ThinkPHP3.1 quick start (3) query language

In the previous article, we learned the basic data CURD method, but in more cases, due to the difference in business logic, CURD operations are often not that simple, especially under complicated business logic, this is also a shortcoming of the ActiveRecord mode. ThinkPHP query language with coherent operation can... "> <LINKhref =" http://www.php100.com//statics

In the previous article, we learned the basic data CURD method, but in more cases, due to the difference in business logic, CURD operations are often not that simple, especially under complicated business logic, this is also a shortcoming of the ActiveRecord mode. The query language of ThinkPHP can be used in combination with coherent operations to meet complicated business logic requirements. in this article, we will first have a deep understanding of the query language of the framework.

Introduction

ThinkPHP has built-in flexible query methods to quickly perform data query operations. query conditions can be used for reading, updating, and deleting operations, mainly involving coherent operations such as the where method, no matter what database you use, you use almost the same query method (for example, Mongo may differ in expression query). The system helps you solve the differences between different databases, therefore, we call this query method of the framework a query language. The query language is also the ORM highlight of the ThinkPHP framework, making query operations easier and easier to understand. The following describes the meaning of the query language.

Query method

ThinkPHP supports using strings directly as query conditions. However, it is recommended that indexes or objects be used as query conditions in most cases, because they are more secure. I. using strings as query conditions is the most traditional method, but the security is not high. for example:

1. $ User = M ("User"); // instantiate the User object
2. $ User-> where ('Type = 1 AND status = 1')-> select ();

The last generated SQL statement is

1. SELECT * FROM think_user WHERE type = 1 AND status = 1

When using string query, we can use the security preprocessing mechanism provided by the new version of the string condition. 2. using arrays as query conditions is the most common query method, for example:

1. $ User = M ("User"); // instantiate the User object
2. $ Condition ['name'] = 'thinkphp ';
3. $ Condition ['status'] = 1;
4. // Pass the query condition into the query method
5. $ User-> where ($ condition)-> select ();

The last generated SQL statement is

1. SELECT * FROM think_user WHERE 'name' = 'thinkphp' AND status = 1

If multi-field query is performed, the default logical relationship between fields is logical AND. However, you can use the following rules to change the default logical judgment by using _ logic to define the query logic:

1. $ User = M ("User"); // instantiate the User object
2. $ Condition ['name'] = 'thinkphp ';
3. $ Condition ['account'] = 'thinkphp ';
4. $ Condition ['_ logic'] = 'OR ';
5. // Pass the query condition into the query method
6. $ User-> where ($ condition)-> select ();

The last generated SQL statement is

1. SELECT * FROM think_user WHERE 'name' = 'thinkphp' OR 'account' = 'thinkphp'

III. query using the object method. here, the stdClass built-in object is used as an example:

1. $ User = M ("User"); // instantiate the User object
2. // Define query conditions
3. $ Condition = new stdClass ();
4. $ Condition-> name = 'thinkphp ';
5. $ Condition-> status = 1;
6. $ User-> where ($ condition)-> select ();

The last generated SQL statement is the same as the preceding one.

1. SELECT * FROM think_user WHERE 'name' = 'thinkphp' AND status = 1

The results of object-based query and array-based query are the same and interchangeable. In most cases, it is recommended that the array-based query be more efficient.

 

Expression query

The preceding query condition is just a simple equality judgment. you can use a query expression to support more SQL query syntaxes. it is also the essence of ThinkPHP query language. the format of the query expression is as follows: $ map ['Field name'] = array ('expression', 'query condition'); the expression is case-insensitive and supports the following types of query expressions:

Expression	Description
EQ	Equal to (=)
NEQ	Not equal to (<>)
GT	Greater than (>)
EGT	Greater than or equal to (> =)
LT	Less than (<)
ELT	Less than or equal to (<=)
LIKE	Fuzzy search
[NOT]	(Not in) interval query
[NOT] IN	(Not IN) IN query
EXP	Expression query, supporting SQL syntax

Example: EQ: equal to (=)
For example:

1. $ Map ['id'] = array ('EQ ', 100 );

It is equivalent to the following query:

1. $ Map ['id'] = 100;

The query condition is id = 100NEQ: not equal to (<>)
For example:

1. $ Map ['id'] = array ('neq', 100 );

The query condition is id <> 100GT: greater than (>)
For example:

1. $ Map ['id'] = array ('GT', 100 );

The query condition is id> 100EGT: greater than or equal to (> =)
For example:

1. $ Map ['id'] = array ('egt', 100 );

The query condition is id> = 100LT: less than (<)
For example:

1. $ Map ['id'] = array ('Lt ', 100 );

The query condition is id <100ELT: less than or equal to (<=)
For example:

1. $ Map ['id'] = array ('elt', 100 );

The query condition is id <= 100 [NOT] LIKE: LIKE of SQL
For example:

1. $ Map ['name'] = array ('like', 'thinkphp % ');

The query condition becomes name like 'thinkphp %'
If the DB_LIKE_FIELDS parameter is configured, some fields are automatically fuzzy queried. For example:

1. 'Db _ LIKE_FIELDS '=> 'Title | content'

Use

1. $ Map ['title'] = 'thinkphp ';

The query condition becomes title like '% thinkphp %'
Supports arrays, such

1. $ Map ['A'] = array ('like', array ('% thinkphp %', '% tp'), 'OR ');
2. $ Map ['B'] = array ('notlike', array ('% thinkphp %', '% tp'), 'AND ');

The generated query conditions are:

1. (A like '% thinkphp %' OR a like '% tp') AND (B not like '% thinkphp %' AND B not like '% tp ')

[NOT] BETWEEN: same as SQL [not] between. query conditions support strings or arrays, for example:

1. $ Map ['id'] = array ('between', '1, 8 ');

It is equivalent to the following:

1. $ Map ['id'] = array ('between', array ('1', '8 '));

The query condition becomes id BETWEEN 1 AND 8 [NOT] IN: same as SQL [not] in. the query condition supports strings or arrays, for example:

1. $ Map ['id'] = array ('not in', '1, 5, 8 ');

It is equivalent to the following:

1. $ Map ['id'] = array ('not in', array ('1', '5', '8 '));

The query condition becomes id not in (, 8) EXP: Expression, supporting more complex queries
For example:

1. $ Map ['id'] = array ('in', '1, 3, 8 ');

You can change it:

1. $ Map ['id'] = array ('Exp ', 'In (1, 3, 8 )');

Exp query conditions are not treated as strings, so the following query conditions can use any syntax supported by SQL, including using functions and field names. A query expression can be used not only for query conditions, but also for data updates. for example:

1. $ User = M ("User"); // instantiate the User object
2. // Assign values to the attributes of the data object to be modified
3. $ Data ['name'] = 'thinkphp ';
4. $ Data ['score '] = array ('Exp', 'Score + 1'); // add 1 to your credit
5. $ User-> where ('Id = 5')-> save ($ data); // save the modified data according to the conditions.

 

Quick Query

Quick query has been added since version 3.0 to further simplify the writing of query conditions. for example: 1. implement the same query conditions for different fields

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['name | title'] = 'thinkphp ';
3. // Pass the query condition into the query method
4. $ User-> where ($ map)-> select ();

The query condition becomes

1. Name = 'thinkphp' OR title = 'thinkphp'

2. implement different query conditions for different fields

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['status & title'] = array ('1', 'thinkphp', '_ multi' => true );
3. // Pass the query condition into the query method
4. $ User-> where ($ map)-> select ();

'_ Multi' => true must be added at the end of the array, indicating that the current multi-condition match is used, so the query condition becomes

1. Status = 1 AND title = 'thinkphp'

To query more fields, for example:

1. $ Map ['status & score & title'] = array ('1', array ('GT ', '0'), 'thinkphp ', '_ multi' => true );

The query condition becomes

1. Status = 1 AND score> 0 AND title = 'thinkphp'

Note: "|" and "&" cannot be used at the same time in the quick query method.

 

Interval query

ThinkPHP supports interval query for a field, for example:

1. $ Map ['id'] = array ('GT ', 1), array ('Lt', 10 ));

The query conditions are as follows:

1. ('Id'> 1) AND ('id' <10)

1. $ Map ['id'] = array ('GT ', 3), array ('Lt', 10), 'OR ');

The query conditions are as follows: ('id'> 3) OR ('id' <10)

1. $ Map ['id'] = array ('neq', 6), array ('GT ', 3), 'and ');

The query condition is: ('id '! = 6) AND ('id'> 3)
The last one can be the AND, OR, or xor operator. If no value is specified, it is the AND operation by default.
The condition of the interval query can support all expressions of a common query, that is, expressions LIKE, GT, and EXP are supported. In addition, range query supports more conditions, as long as the conditions for a field can be written together, for example:

1. $ Map ['name'] = array ('like', '% a %'), array ('like', '% B % '), array ('like', '% c %'), 'thinkphp', 'or ');

The final query condition is:

1. ('Name' LIKE '% a %') OR ('name' LIKE '% B %') OR ('name' LIKE '% c % ') OR ('name' = 'thinkphp ')

 

Combined query

The subject of the combined query is still queried in array mode, but some special query support is added, including string mode query (_ string) and composite query (_ complex), request string query (_ query). only one special query can be defined for each query in a hybrid query. because of the array index method, special queries with the same index will be overwritten. 1. query in string mode (using _ string as the query condition)
Array conditions can also be used together with string conditions, for example:

1. $ User = M ("User"); // instantiate the User object
2. $ Map ['id'] = array ('neq', 1 );
3. $ Map ['name'] = 'OK ';
4. $ Map ['_ string'] = 'status = 1 AND score> 10 ';
5. $ User-> where ($ map)-> select ();

The final query condition is:

1. ('Id '! = 1) AND ('name' = 'OK') AND (status = 1 AND score> 10)

2. request string query: a request string query is similar to passing parameters through a URL. it supports simple equality criteria.

1. $ Map ['id'] = array ('GT ', '123 ');
2. $ Map ['_ query'] = 'status = 1 & score = 100 & _ logic = or ';

The query conditions are as follows:

1. 'Id'> 100 AND ('status' = '1' OR 'score '= '123 ')

3. Composite query composite query encapsulates a new query condition and is then incorporated into the original query condition. Therefore, complex query condition assembly can be completed.
For example:

1. $ Where ['name'] = array ('like', '% thinkphp % ');
2. $ Where ['title'] = array ('like', '% thinkphp % ');
3. $ Where ['_ logic'] = 'or ';
4. $ Map ['_ complex'] = $ where;
5. $ Map ['id'] = array ('GT ', 1 );

The query condition is

1. (Id> 1) AND (name like '% thinkphp %') OR (title like '% thinkphp % '))

The composite query uses _ complex as the subquery condition. in combination with the previous query method, you can flexibly define more complex query conditions.
Many query methods can be converted to each other. for example, the preceding query conditions can be changed:

1. $ Where ['id'] = array ('GT ', 1 );
2. $ Where ['_ string'] =' (name like "% thinkphp %") OR (title like "% thinkphp ")';

The final SQL statements are consistent.

 

Statistics Query

We often use statistical data in applications, such as the number of current users (or the number of users meeting certain conditions), the maximum credits of all users, and the average scores of users, thinkPHP provides a series of built-in methods for these statistical operations, including:

Method	Description
Count	Quantity. the parameter is the field name to be counted (optional)
Max	Obtain the maximum value. The parameter is the field name to be counted (required)
Min	Obtain the minimum value. The parameter is the field name to be counted (required)
Avg	Obtain the average value. The parameter is the field name to be counted (required)
Sum	Obtain the total score. the parameter is the field name to be counted (required)

Usage example:

1. $ User = M ("User"); // instantiate the User object

Number of Retrieved users:

1. $ UserCount = $ User-> count ();

You can also collect statistics by field:

1. $ UserCount = $ User-> count ("id ");

Obtain the maximum credits of a user:

1. $ MaxScore = $ User-> max ('score ');

Obtain the minimum credits of a user whose credits are greater than 0:

1. $ MinScore = $ User-> where ('score> 0')-> min ('score ');

Obtain the average credits of a user:

1. $ AvgScore = $ User-> avg ('score ');

Count the user's total score:

1. $ SumScore = $ User-> sum ('score ');

All statistical queries support consistent operations.

 

SQL query

ThinkPHP's built-in ORM and ActiveRecord modes enable convenient data access operations, and the new version adds a coherent operation function to make this data operation clearer, however, ThinkPHP still supports native SQL queries and execution operations. to meet the needs of complex queries and some special data operations, the return value of the SQL query is because the query results of the Db class are directly returned, and no processing is performed. It mainly includes the following two methods:

1. query method

Query SQL query
Usage	Query ($ SQL, $ parse = false)
Parameters	SQL (required): the SQL statement to be queried Parse (optional): whether to parse SQL statements
Return value	If the data is invalid or the query is incorrect, false is returned. Otherwise, the query result dataset is returned (same as the select method)

Example:

1. $ Model = new Model () // instantiate a model object without any data tables
2. $ Model-> query ("select * from think_user where status = 1 ");

If you currently use a distributed database and set read/write splitting, the query method is always executed on the read server. Therefore, the query method corresponds to read operations, no matter what your SQL statement is. 2. execute method

Execute is used for SQL operations to update and write data.
Usage	Execute ($ SQL, $ parse = false)
Parameters	SQL (required): the SQL statement to be executed Parse (optional): whether to parse SQL statements
Return value	If the data is invalid or the query is incorrect, false is returned. Otherwise, the number of affected records is returned.

Example:

1. $ Model = new Model () // instantiate a model object without any data tables
2. $ Model-> execute ("update think_user set name = 'thinkphp' where status = 1 ");

If you currently use a distributed database and set read/write splitting, the execute method is always executed on the write server. Therefore, the execute method corresponds to write operations, no matter what your SQL statement is.

Dynamic query

With the features of PHP5, ThinkPHP implements dynamic query. The core model's dynamic query methods include the following:

Method name	Description	Example
GetBy	Query data based on field values	For example, getByName and getByEmail
GetFieldBy	Query and return the value of a field based on the field	For example, getFieldByName

1. getBy dynamic query this query method queries fields in a data table. For example, if a User object has attributes such as id, name, email, and address, we can use the following query method to query records that meet the conditions based on a specific attribute.

1. $ User = $ User-> getByName ('liu21st ');
2. $ User = $ User-> getByEmail ('liu21st @ gmail.com ');
3. $ User = $ User-> getByAddress ('Shenzhen, China ');

Currently, dynamic query of multiple data fields is not supported. use the find and select methods to query multiple data fields. 2. getFieldBy dynamically queries a field and returns the value of a field, for example

1. $ UserId = $ User-> getFieldByName ('liu21st', 'id ');

Obtains the user id value based on the user name.

 

Subquery

Subquery support has been added since version 3.0. There are two ways to use this feature: 1. when the select method parameter is set to false, the system only returns the constructed SQL statement if no query is performed, for example:

1. // First construct the subquery SQL
2. $ SubQuery = $ model-> field ('Id, name')-> table ('tablename')-> group ('field')-> where ($ where) -> order ('status')-> select (false );

When the select method passes in the false parameter, it means that the current query is not executed, but the query SQL is generated. 2. use buildSql

1. $ SubQuery = $ model-> field ('Id, name')-> table ('tablename')-> group ('field')-> where ($ where) -> order ('status')-> buildSql ();

After the buildSql method is called, the actual query operation is not performed, but the SQL statement for this query is generated (to avoid confusion, parentheses are added on both sides of the SQL ), then we can directly call it in subsequent queries.

1. // Query using subqueries
2. $ Model-> table ($ subQuery. 'a')-> where ()-> order ()-> select ()

The constructed subquery SQL can be used for consistent operation methods of ThinkPHP, such as table where.

 

Summary

This document describes how to query data, including simple queries, expression queries, quick queries, interval queries, and statistical queries, and how to perform subqueries. Later we will learn in detail how to use consistent operations for more complex CURD operations.