ThinkPHP3.1 Quick Start (3) Query Language.

In the previous article, we mastered the basic data CURD method, but in more cases, due to differences in business logic, CURD operations are often not that simple, especially with complex business logic. This is also the shortcoming of the ActiveRecord mode. . ThinkPHP's query language combined with coherent operations can well solve complex business logic requirements. In this article, we will first have an in-depth understanding of the framework's query language.

Introduction

ThinkPHP has built-in very flexible query methods, which can quickly perform data query operations. The query conditions can be used for operations such as reading, updating, and deleting. It mainly involves coherent operations such as the where method. Whether it is using Which database, you almost use the same query method (some databases such as Mongo will have different expression queries), the system helps you solve the differences between different databases, so 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 simpler and easier to understand. Let’s explain the connotation of query language one by one.

Query method

ThinkPHP can support the direct use of strings as query conditions, but in most cases it is recommended to use index arrays or objects as query conditions, because it is safer. 1. Use strings as query conditions. This is the most traditional way, but it is not very safe. For example:

$User = M("User"); // Instantiate the User object

$User->where( 'type=1 AND status=1')->select();

The final generated SQL statement is

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

When using string query, we can The safety preprocessing mechanism used in conjunction with the string conditions provided by the new version will not be described in detail for the time being. 2. Using arrays as query conditions is the most commonly used query method, for example:

$User = M("User"); // Instantiate the User object

$condition['name'] = 'thinkphp' ;

$condition['status'] = 1;

// Pass the query conditions into the query method

$User->where($condition)->select();

The final generated SQL statement Yes

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

If you perform a multi-field query, the default logical relationship between fields is logical AND, but the default logic can be changed using the following rules Judgment, define query logic by using _logic:

$User = M("User"); // Instantiate the User object

$condition['name'] = 'thinkphp';

$condition['account' ] = 'thinkphp';

$condition['_logic'] = 'OR';

// Pass the query conditions into the query method

$User->where($condition)->select();

The final generated SQL statement is

SELECT * FROM think_user WHERE `name`='thinkphp' OR `account`='thinkphp'

3. Use the object method to query Here, take the stdClass built-in object as an example:

$ User = M("User"); // Instantiate User object

// Define query conditions

$condition = new stdClass();

$condition->name = 'thinkphp';

$condition- >status= 1;

$User->where($condition)->select();

The final generated SQL statement is the same as above

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

The effect of querying using object mode and querying using array is the same and are interchangeable. In most cases, we recommend using array mode to be more efficient.

Expression query

The above query condition is just a simple equality judgment. Query expressions can be used to support more SQL query syntax, which is also the essence of ThinkPHP query language. The query expression format is: $map ['Field name'] = array('expression','query condition'); Expressions are not case-sensitive. The following query expressions are supported, and their respective meanings are:

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, supports SQL syntax

Examples are as follows :EQ: equal to (=)
For example:

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

is equivalent to the following query

$map['id'] = 100;

The query condition represented by

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

$map['id'] = array('neq',100); The query condition represented by

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

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

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

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

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

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

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

$map['id'] = array('elt', 100); The query condition represented by

is id <= 100[NOT] LIKE: Same as sql LIKE
For example:

$map['name'] = array('like','thinkphp%');

The query condition becomes name like 'thinkphp%'
If the DB_LIKE_FIELDS parameter is configured, some fields will also automatically perform fuzzy query. For example, if you set:

'DB_LIKE_FIELDS'=>'title|content'

, use

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

The query condition will become title like '%thinkphp% '
Supports array mode, for example

$map['a'] =array('like',array('%thinkphp%','%tp'),'OR');

$map['b' ] =array('notlike',array('%thinkphp%','%tp'),'AND');

The query condition generated is:

(a like '%thinkphp%' OR a like '% tp') AND (b not like '%thinkphp%' AND b not like '%tp')

[NOT] BETWEEN: Same as sql's [not] between, query conditions support strings or arrays, for example:

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

is equivalent to the following:

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

The query condition becomes id BETWEEN 1 AND 8[NOT] IN: Same as SQL's [not] in, the query condition supports string or array, for example:

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

is equivalent to the following:

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

The query condition becomes id NOT IN (1,5, 8)EXP: expression, supporting 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 conditions of exp query will not be treated as strings, so subsequent query conditions can use any syntax supported by SQL, including using functions and field names. Query expressions can not only be used for query conditions, but also for data updates, for example:

$User = M("User"); // Instantiate the User object

// Assign the data object attributes to be modified

$ data['name'] = 'ThinkPHP';

$data['score'] = array('exp','score+1');//The user's points are increased by 1

$User->where( 'id=5')->save($data); // Save modified data according to conditions

Quick query

Starting from version 3.0, a quick query method has been added, which can further simplify the writing of query conditions. For example: 1. Implement the same query conditions for different fields

$User = M("User"); // Instantiate the User object

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

// The query conditions are passed into the query method

$User->where($map)->select();

The query conditions become

name= 'thinkphp' OR title = 'thinkphp'

2. Implementation Different query conditions for different fields

$User = M("User"); // Instantiate the User object

$map['status&title'] =array('1','thinkphp','_multi'=> true);

// Pass the query conditions into the query method

$User->where($map)->select();

'_multi'=>true must be added at the end of the array, indicating that it is currently a multi-condition match, so the query condition becomes

status= 1 AND title = 'thinkphp'

, the query field supports 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: "|" and "&" cannot be used at the same time in the shortcut query method.

Interval query

ThinkPHP supports interval query for a certain field, for example:

$map['id'] = array(array('gt',1),array('lt',10)) ;

The query condition obtained is:

(`id` > 1) AND (`id` < 10)

$map['id'] = array(array('gt',3),array ('lt',10), 'or') ;

The query condition obtained is: (`id` > 3) OR (`id` < 10)

$map['id'] = array( array('neq',6),array('gt',3),'and');

The query condition obtained is: (`id` != 6) AND (`id` > 3)
Finally One can be AND, OR or XOR operator. If not written, the default is AND operation.
The conditions of interval query can support all expressions of ordinary queries, which means that expressions like LIKE, GT and EXP can be supported. In addition, interval query can also support more conditions. As long as the conditions for one field can be written together, for example:

$map['name'] = array(array('like','%a%') , array('like','%b%'), array('like','%c%'), 'ThinkPHP','or');

The final query condition is:

(`name` LIKE '%a%') OR (`name` LIKE '%b%') OR (`name` LIKE '%c%') OR (`name` = 'ThinkPHP')

combination query

combination The main body of the query is still queried in array mode, but some special query support is added, including string mode query (_string), compound query (_complex), request string query (_query), and special queries in mixed queries. Each query Only one can be defined. Due to the array indexing method, special queries with the same index will be overwritten. 1. String mode query (using _string as query condition)
Array conditions can also be mixed with string conditions, for example:

$User = M("User"); // Instantiate User object

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

$map['name'] = 'ok';

$map['_string'] = 'status=1 AND score>10';

$User->where($map)->select();

The final query condition becomes:

( `id` != 1 ) AND ( `name` = 'ok' ) AND (status=1 AND score>10)

2. Request string query method Request string query is a method similar to URL parameter passing, which can support simple condition equality judgment.

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

$map['_query'] = 'status=1&score=100&_logic=or';

The query condition obtained is:

`id`>100 AND (`status` = '1' OR `score` = '100')

3. Compound query Compound query is equivalent to encapsulating a new query condition and then incorporating it into the original query condition among them, so more complex query condition assembly can be completed.
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%') )

The compound query uses _complex as a sub-query condition to define, in conjunction with the previous query method , you can formulate more complex query conditions very flexibly.
Many query methods can be converted to each other. For example, the above query conditions can be changed to:

$where['id'] = array('gt',1);

$where['_string'] = ' (name like "%thinkphp%") OR (title like "%thinkphp") ';

The final generated SQL statement is consistent.

Statistical query

In applications, we often use some statistical data, such as the current number of users (or those who meet certain conditions), the maximum points of all users, the average score of users, etc. ThinkPHP provides a series of statistical operations for these Built-in methods, including:

Method

Description

Count Count Count, the parameter is the field name to be counted (optional)

Max Get the maximum value, the parameter is the field name to be counted (required)

Min Get the minimum value, the parameter is the field name to be counted (required)

Avg Get the average value, the parameter is the field name to be counted (required)

Sum Get the total score, the parameter is the field name to be counted (required)

Usage example:

$User = M("User"); // Instantiate the User object

Get the number of users:

$userCount = $User->count();

Or based on field statistics:

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

Get the user's maximum points:

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

Get the users whose points are greater than 0 The minimum score:

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

Get the user's average score:

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

Statistics of the user's total score:

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

And all statistical queries support the use of coherent operations.

SQL query

ThinkPHP’s built-in ORM and ActiveRecord mode realize convenient data access operations, and the coherent operation function added in the new version makes this data operation clearer, but ThinkPHP still retains the native SQL query and Execution operation support, in order to meet the needs of complex queries and some special data operations, the return value of the SQL query is the query result of the Db class returned directly without any processing. It mainly includes the following two methods:

1. Query method

query Perform SQL query operation

Usage query($sql,$parse=false)

Parameters sql (required): SQL statement to be queried
parse (optional): Whether it is necessary to parse SQL

Return value

If the data is illegal or the query is wrong, return false

Otherwise, return the query result data set (same as the select method)

Usage example:

$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 if read-write separation is set, the query method is always executed on the read server, so the query method corresponds to read operations, regardless of what your SQL statement is. 2. execute method

execute is used to update and write sql operations of data

Usage execute($sql,$parse=false)

Parameters sql (required): SQL statement to be executed
parse ( Optional): Whether you need to parse SQL

Return value If the data is illegal or the query is wrong, return false
Otherwise, return the number of records affected

Usage example:

$Model = new Model() // Instantiate a model object None Corresponds to any data table

$Model->execute("update think_user set name='thinkPHP' where status=1");

If you currently use a distributed database and set up read-write separation, the execute method It is always executed by writing to the server, so the execute method corresponds to writing operations, regardless of what your SQL statement is.

Dynamic query

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

Method name

Description

Example

getBy based on the value of the field Query data For example, getByName, getByEmail

getFieldBy Query based on fields and return the value of a certain field For example, getFieldByName

1. getBy dynamic query This query method queries the fields of the data table. For example, the User object has attributes such as id, name, email, address, etc., then we can use the following query method to directly query records that meet the conditions based on a certain attribute.

$user = $User->getByName('liu21st');

$user = $User->getByEmail('liu21st@gmail.com');

$user = $User->getByAddress( 'Shenzhen, China');

The dynamic query method of multiple data fields is temporarily not supported. Please use the find method and select method to query. 2. getFieldBy dynamic query queries a certain field and returns the value of a certain field, for example

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

means to get the user's id value based on the user's name.

Subquery

Starting from version 3.0, subquery support has been added. There are two ways to use it: 1. Use the select method. When the parameter of the select method is false, it means that no query is performed and only the constructed SQL is returned, for example ：

// First construct 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 parameter, it means that the current query is not executed, but only the query SQL is generated. 2. Use the buildSql method

$subQuery = $model->field('id,name')->table('tablename')->group('field')->where($where)- >order('status')->buildSql();

After calling the buildSql method, the actual query operation will not be performed, but only the SQL statement of the query will be generated (in order to avoid confusion, brackets will be added on both sides of the SQL ), and then we call it directly in subsequent queries.

// Query using subqueries

$model->table($subQuery.' a')->where()->order()->select()

Constructed subquery SQL Continuous operation methods available for ThinkPHP, such as table where etc.

Summary

This article mainly helps us understand how to query data, including simple queries, expression queries, quick queries, interval queries, statistical queries, and how to perform subquery operations. Later we will learn more about how to use coherent operations to perform more complex CURD operations.

The above is the content of ThinkPHP3.1 Quick Start (3) Query Language. For more related content, please pay attention to the PHP Chinese website (www.php.cn)