ThinkPHP3.1 Query Language Detailed _php example

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.

1. Query Language Introduction

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.

2. 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, use string as query criteria

This is the most traditional way, but the security is not high, for example:

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

The last SQL statement generated is

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.

Ii. using arrays as query criteria

This is the most common way to query, for example:

$User = M ("User"); Instantiate the User object
$condition [' name '] = ' thinkphp ';
$condition [' status '] = 1;
 The query condition is passed into the query method
$User->where ($condition)->select (); 

The last SQL statement generated is

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:

$User = M ("User"); Instantiate the User object
$condition [' name '] = ' thinkphp ';
$condition [' account '] = ' thinkphp ';
$condition [' _logic '] = ' or ';
 The query condition is passed into the query method
$User->where ($condition)->select (); 

The last SQL statement generated is

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

Third, the use of object methods to query

Here is an example of a Stdclass built-in object:

$User = M ("User"); Instantiate the User object
 //define the query condition
$condition = new StdClass (); 
$condition->name = ' thinkphp '; 
$condition->status= 1; 
$User->where ($condition)->select (); 

The last generated SQL statement is the same as above

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.

3. Expression Query

The above query condition is just a simple equality judgment, you can use query expression to support more SQL query syntax, but also the essence of thinkphp Query Language, query expression use 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
BX7	Expression query, support for SQL syntax

Examples are as follows:

EQ : Equals (=)

For example:

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

And the following query is equivalent

$map [' id '] = 100;

The query condition represented is ID = 100

NEQ: Not Equal to (<>)

For example:

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

The query condition indicated is ID <> 100

GT: Greater Than (>)

For example:

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

The query condition indicated is ID > 100

EGT: greater than or equal to (>=)

For example:

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

The query condition represented is ID >= 100

LT: Less Than (<)

For example:

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

The query condition indicated is ID < 100

ELT: Less Than or equal (<=)

For example:

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

The query condition represented is ID <= 100

[NOT]like: Same as SQL

For example:

$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:

' Db_like_fields ' => ' title|content '

words, use

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

Query conditions will become title like '%thinkphp% '

Supports array methods, such as

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

The query conditions that are generated are:

(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:

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

and the following equivalent:

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

The query condition becomes the ID BETWEEN 1 and 8

[NOT] In: With SQL (not) in, query conditions support strings or arrays, for example:

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

and the following equivalent:

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

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

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

Can be changed to:

$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:

$User = M ("User"); Instantiate the User object
 //Data object property assignment to modify
$data [' name '] = ' thinkphp ';
$data [' score '] = Array (' exp ', ' score+1 ');//user's integral plus 1
$User->where (' id=5 ')->save ($data);//Save modified data according to condition

4. Quick Query

Starting with the 3.0 version, the shortcut query method is added to further simplify the wording of the query terms, such as:

One, to achieve the same query conditions of different fields

$User = M ("User"); Instantiate the User object
$map [' name|title '] = ' thinkphp ';
 The query condition is passed into the query method
$User->where ($map)->select (); 

The query condition becomes

Name= ' thinkphp ' OR title = ' thinkphp '

Second, the realization of different fields of the query conditions

$User = M ("User"); Instantiate the User object
$map [' Status&title '] =array (' 1 ', ' thinkphp ', ' _multi ' =>true);
 The query condition is passed into the query method
$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

status= 1 and title = ' thinkphp '

Query fields support More, for example:

$map [' Status&score&title '] =array (' 1 ', Array (' GT ', ' 0 '), ' thinkphp ', ' _multi ' =>true);

The query condition becomes

Status= 1 and score >0 and title = ' thinkphp '

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

5. Interval query

Thinkphp supports interval queries for a field, for example:

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

The resulting query criteria are:

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

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

The resulting query criteria are:

(' ID ' > 3) OR (' ID ' < 10)

$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:

$map [' name '] = Array (' Like ', '%a% '), Array ("Like", '%b% '), array (' Like ', '%c% '), ' thinkphp ', ' or '); 

The final query criteria are:

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

6. Combination 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:

$User = M ("User"); Instantiate the User object
$map [' id '] = array (' NEQ ', 1);
$map [' name '] = ' OK ';
$map [' _string '] = ' status=1 and score>10 ';
$User->where ($map)->select (); 

The final query conditions are as follows:

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

Second, the request string Query method

A request-string query is a way that is similar to a URL argument and can support simple conditional equality judgments.

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

The resulting query criteria are:

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

Third, compound query

A compound query is equivalent to encapsulating a new query condition and merging it into the original query condition, so it can be assembled with more complicated query conditions.
For example:

$where [' name '] = Array (' Like ', '%thinkphp% ');
$where [' title '] = Array (' Like ', '%thinkphp% ');
$where [' _logic '] = ' or ';
$map [' _complex '] = $where;
$map [' id '] = array (' GT ', 1);

The query condition is

(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:

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

The resulting SQL statement is consistent.

7. Statistical Enquiry

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:

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

Get the number of users:

$userCount = $User->count ();

or by field statistics:

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

Get maximum points for a user:

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

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

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

Get the average integral of a user:

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

To count the total score of users:

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

And all statistical queries support the use of consecutive operations.

8.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:

One, 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:

$Model = new Model ()//Instantiate a model object that does not correspond to any data table
$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.
two, 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:

$Model = new Model ()//Instantiate a model object does not correspond to any data table
$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.

9. 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

One, getby dynamic query

This query is queried for the fields of the datasheet. 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.

$user = $User->getbyname (' liu21st ');
$user = $User->getbyemail (' liu21st@gmail.com ');
$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.

Second, getfieldby dynamic query

query for a field and return the value of a field, for example

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

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

10. Sub-query

There are two ways to use subquery support starting with version 3.0:

1. Use Select method

When the Select method's argument is false, it means that no query simply returns the build SQL, for example:

First constructs the subquery sql 
$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

$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.

Query using subqueries 
$model->table ($subQuery. ' A ')->where ()->order ()->select () 

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