ThinkPHP3.1 Quick Start (3) Query Language

In the previous article we have mastered the basic data curd method, but in more cases, due to the differences in business logic, curd operations are often not so simple, especially complex business logic below, this is the ActiveRecord mode of deficiencies. thinkphp query Language with coherent operation can be very good to solve the complex business logic requirements, this article we first to in-depth understanding of the framework of the query language.

Introduced

thinkphp built-in a very flexible query method, you can quickly data query operations, query conditions can be used to read, update and delete operations, mainly related to where methods such as a coherent operation can be, regardless of the use of any database, You almost use the same query method (individual database such as MONGO in the expression query will be different), the system to help you solve the difference between the database, so we refer to the framework of this query as a query language. Query language is also the thinkphp framework of ORM highlights, so that the query operation more easily understandable. Here are one by one to explain the meaning of the query language.

Query method

Thinkphp can support the use of strings directly as query criteria, but in most cases it is recommended to use an indexed array or object as a query condition because it is more secure. First, using strings as query criteria This is the most traditional way, but not very safe, for example:

1. $User = M ("User"); Instantiating the User object
2. $User->where (' type=1 and Status=1 ')->select ();

The last SQL statement generated is

1. SELECT * from Think_user WHERE type=1 and Status=1

When using string query, we can use the new version of the string conditions provided by the security preprocessing mechanism, for the time being no longer elaborate. Using arrays as query criteria this is the most common way to query, for example:

1. $User = M ("User"); Instantiating the User object
2. $condition [' name '] = ' thinkphp ';
3. $condition [' status '] = 1;
4. Passing query criteria into the Query method
5. $User->where ($condition)->select ();

The last SQL statement generated is

1. SELECT * from Think_user WHERE ' name ' = ' thinkphp ' and Status=1

If you make a multiple-field query, the default logical relationship between fields is logical and and, but with the following rules you can change the default logical judgment by using _logic to define the query logic:

1. $User = M ("User"); Instantiating the User object
2. $condition [' name '] = ' thinkphp ';
3. $condition [' account '] = ' thinkphp ';
4. $condition [' _logic '] = ' or ';
5. Passing query criteria into the Query method
6. $User->where ($condition)->select ();

The last SQL statement generated is

1. SELECT * from Think_user WHERE ' name ' = ' thinkphp ' OR ' account ' = ' thinkphp '

Third, use the object way to query here to Stdclass built-in objects for example:

1. $User = M ("User"); Instantiating the User object
2. Define query criteria
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 above

1. SELECT * from Think_user WHERE ' name ' = ' thinkphp ' and Status=1

The effect of querying and using an array query in object mode is the same and interchangeable, and in most cases we recommend an array approach to be more efficient.

An expression query

The above query condition is just a simple equality judgment, you can use query expressions to support more SQL query syntax, but also the essence of thinkphp query Language, the use of query expression format: $map [' field name '] = Array (' expression ', ' query condition '); Expressions are case-insensitive, and there are several supported query expressions, each of which means the following:

An expression	meaning
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 query
[NOT] BETWEEN	(not) Interval query
[NOT] In	(not) in query
Exp	Expression query, support for SQL syntax

Examples are as follows: EQ: equals (=)
For example:

1. $map [' id '] = array (' eq ', 100);

And the following query is equivalent

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

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

1. $map [' id '] = array (' NEQ ', 100);

The query condition represented is ID <> 100GT: Greater Than (>)
For example:

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

The query condition represented is ID > 100EGT: Greater Than or equal to (>=)
For example:

1. $map [' id '] = array (' EGT ', 100);

The query condition represented is the ID >= 100LT: Less Than (<)
For example:

1. $map [' id '] = array (' lt ', 100);

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

1. $map [' id '] = array (' ELT ', 100);

The query condition represented is the ID <= 100[not] like: the same as 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 queried. For example, set up:

1. ' Db_like_fields ' => ' titlecontent '

words, use

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

Query conditions will become title like '%thinkphp% '
Supports array methods, such as

1. $map [' A '] =array (' Like ', Array ('%thinkphp% ', '%tp '), ' OR ');
2. $map [' B '] =array (' Notlike ', Array ('%thinkphp% ', '%tp '), ' and ');

The query conditions that are generated are:

1. (A like '%thinkphp% ' OR a Like '%tp ') and (b not like '%thinkphp% ' and B '%tp ')

[NOT] BETWEEN: With SQL [NOT] BETWEEN, the query condition supports strings or arrays, for example:

1. $map [' id '] = array (' Between ', ' 1,8 ');

and the following equivalent:

1. $map [' id '] = array (' Between ', Array (' 1 ', ' 8 '));

The query condition becomes the ID BETWEEN 1 and 8[not] in: With SQL [NOT] in, the query condition supports strings or arrays, for example:

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

and the following equivalent:

1. $map [' id '] = array (' Not in ', Array (' 1 ', ' 5 ', ' 8 '));

The query condition becomes ID not in (1,5, 8) EXP: An expression that supports more complex query situations
For example:

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

Can be changed to:

1. $map [' id '] = Array (' exp ', ' in (1,3,8) ');

The condition of the EXP query is not treated as a string, so subsequent query conditions can use any SQL-supported syntax, including the use of functions and field names. Query expressions can be used not only for query criteria, but also for data updates, such as:

1. $User = M ("User"); Instantiating the User object
2. The data object property assignment to modify
3. $data [' name '] = ' thinkphp ';
4. $data [' score '] = Array (' exp ', ' score+1 ');//user's integral plus 1
5. $User->where (' id=5 ')->save ($data); Save modified data based on criteria

Quick Query

Starting with the 3.0 version, the added shortcut query method, can further simplify the query conditions, such as: first, to achieve the same query conditions of different fields

1. $User = M ("User"); Instantiating the User object
2. $map [' nametitle '] = ' thinkphp ';
3. Passing query criteria into the Query method
4. $User->where ($map)->select ();

The query condition becomes

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

Second, the realization of different fields of the query conditions

1. $User = M ("User"); Instantiating the User object
2. $map [' Status&title '] =array (' 1 ', ' thinkphp ', ' _multi ' =>true);
3. Passing query criteria into the Query method
4. $User->where ($map)->select ();

The ' _multi ' =>true must be added at the end of the array to indicate that the current is a multiple-condition match, so that the query condition becomes

1. status= 1 and title = ' thinkphp '

, the query fields support more, 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 shortcut query mode.

Interval query

Thinkphp supports interval queries for a field, for example:

1. $map [' id '] = Array (array (' GT ', 1), Array (' LT ', 10));

The resulting query criteria are:

1. (' id ' > 1) and (' ID ' < 10)

1. $map [' id '] = Array (array (' GT ', 3), array (' LT ', ", ' or ');

The resulting query criteria are: (' ID ' > 3) OR (' ID ' < 10)

1. $map [' id '] = Array (Array (' NEQ ', 6), Array (' GT ', 3), ' and ');

The resulting query criteria are: (' ID '!= 6) and (' ID ' > 3)
The last one can be an and, or, or an XOR operator, and if it is not written, the default is an and operation.
The conditions of an interval query can support all expressions of a normal query, meaning expressions like, GT, and exp can be supported. In addition, the interval query can also support 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 criteria are:

1. (' name ' like '%a% ') or (' name ' like '%b% ') or (' name ' like '%c% ') or (' name ' = ' thinkphp ')

Group Query

The main body of the combined query is still an array query, only adding some special query support, including string pattern query (_string), compound query (_complex), request string Query (_query), and special query in mixed query can only define one for each query. Because an array is indexed, special queries with the same index are overwritten. A, string-mode query (using _string as a query condition)
Array conditions can also be mixed with string conditions, such as:

1. $User = M ("User"); Instantiating 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 conditions are as follows:

1. (' id '!= 1) and (' name ' = ' OK ') and (Status=1 and SCORE>10)

Second, request string Query method request string query is a kind of method similar to URL parameter, can support simple conditional equality judgment.

1. $map [' id '] = array (' GT ', ' 100 ');
2. $map [' _query '] = ' status=1&score=100&_logic=or ';

The resulting query criteria are:

1. ' ID ' >100 and (' status ' = ' 1 ' OR ' score ' = ' 100 ')

Composite query compound query is equivalent to encapsulate a new query conditions, and then merged into the original query conditions, so you can complete the more complex query conditions of assembly.
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% '))

Composite query uses _complex as a subquery to define, with the previous query method, can be very flexible to develop more complex query conditions.
Many query methods can be converted to each other, such as the above query conditions can be changed to:

1. $where [' id '] = array (' GT ', 1);
2. $where [' _string '] = ' (Name like "%thinkphp%") OR (title like "%thinkphp") ';

The resulting SQL statement is consistent.

Statistics Query

In the application we often use some statistical data, such as the current (or meet certain conditions) of the number of users, all users of the maximum points, the user's average score, and so on, thinkphp for these statistical operations provides a series of built-in methods, including:

Method	Description
Count	Statistics quantity, parameter is the name of the field to be counted (optional)
Max	Gets the maximum value, and the parameter is the name of the field to be counted (required)
Min	Gets the minimum value, and the parameter is the name of the field to be counted (required)
Avg	Gets the average, the parameter is the name of the field to be counted (must)
Sum	Gets the total score, the parameter is the field name to be counted (must)

Usage examples:

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

Get the number of users:

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

or by field statistics:

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

Get maximum points for a user:

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

Get the minimum integral of a user with an integral greater than 0:

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

Get the average integral of a user:

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

To count the total score of users:

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

And all statistical queries support the use of consecutive operations.

SQL query

Thinkphp's built-in ORM and ActiveRecord modes enable easy data access, and the new version of the added consistency operation makes this data operation clearer, but thinkphp still retains native SQL queries and execution support. In order to meet the needs of complex queries and some special data operations, the return value of the SQL query is not processed because it is the query result of the DB class that is returned directly. The following two methods are mainly included:

1. Query method

Query performs SQL query operations
Usage	Query ($sql, $parse =false)
Parameters	SQL (required): SQL statement to query Parse (optional): Do you need to parse SQL
return value	Returns False if the data is illegal or if the query is wrong Otherwise return the query result dataset (same Select method)

Use examples:

1. $Model = new Model ()//Instantiate a model object that does not correspond to any data table
2. $Model->query ("select * from Think_user where Status=1");

If you are currently using a distributed database and you have set up a read-write separation, the query method is always performed at the read server, so the query method corresponds to a read operation, regardless of what your SQL statement is. 2. Execute method

Execute SQL operation for updating and writing data
Usage	Execute ($sql, $parse =false)
Parameters	SQL (required): SQL statement to execute Parse (optional): Do you need to parse SQL
return value	Returns False if the data is illegal or if the query is wrong Otherwise, the number of records affected is returned

Use examples:

1. $Model = new Model ()//Instantiate a model object that does not correspond to any data table
2. $Model->execute ("Update think_user set name= ' thinkphp ' where Status=1");

If you are currently using a distributed database and you have set up a read-write separation, the Execute method is always executed at the write server, so the Execute method corresponds to the write operation, regardless of what your SQL statement is.

Dynamic Query

With the help of the characteristics of PHP5 language, dynamic query is implemented thinkphp, and the dynamic query method of core model includes the following kinds:

Method Name	Description	examples
Getby	Querying data based on the value of a field	For example, Getbyname,getbyemail
Getfieldby	Query and return the value of a field based on a field	For example, Getfieldbyname

Getby Dynamic Query This query method for the field of the data table query. For example, the user object has attributes such as id,name,email,address, so we can use the following query method to query the records that match the criteria directly based on a property.

1. $user = $User->getbyname (' liu21st ');
2. $user = $User->getbyemail (' liu21st@gmail.com ');
3. $user = $User->getbyaddress (' Shenzhen, China ');

To temporarily not support dynamic query methods for multiple data fields, use the Find method and the Select method to query. Ii. getfieldby Dynamic Queries query for a field and return the value of a field, for example

1. $userId = $User->getfieldbyname (' liu21st ', ' id ');

Indicates that the user's ID value is obtained based on the user's name.

Child query

New subquery support has been added since version 3.0, and there are two ways to use it: 1. When the Select method's argument is false, use the Select method to indicate that no query simply returns build SQL, for example:

1. To construct a subquery SQL first
2. $subQuery = $model->field (' Id,name ')->table (' tablename ')->group (' field ')->where ($where)->order (' Status ')->select (false);

When the Select method passes in the false argument, the current query is not executed, but only the query SQL is generated. 2. Using BuildSQL method

1. $subQuery = $model->field (' Id,name ')->table (' tablename ')->group (' field ')->where ($where)->order (' Status ')->buildsql ();

The BuildSQL method is invoked without the actual query operation, but only the SQL statement that generated the query (in order to avoid confusion, with parentheses on both sides of the SQL), and then we call directly in subsequent queries.

1. Querying with subqueries
2. $model->table ($subQuery. ' A ')->where ()->order ()->select ()

The constructed subquery SQL can be used for thinkphp consistent operations, such as table where.

Summarize

This article mainly helps us to understand how to conduct data query, including simple query, expression query, quick query, interval query, statistical query, and how to do subquery operation. We will also learn more about how to use coherent operations for more complex curd operations later.