Detailed explanation of the usage of framework query expressions

This article describes the usage of tp5.1 framework query expressions. Share it for your reference, as follows:

Query expressions

Query expressions support most SQL query syntax, which is alsoThinkPHPThe essence of query language and the format of query expressions:

where('Field Name','expression','Query Conditions');
whereOr('Field Name','expression','Query Conditions');

5.1New query methods are also supported

whereField('expression','Query Conditions');
whereOrField('expression','Query Conditions');

FieldUse the camel naming method of the field.

Expressions are case-free, and the following types of supported query expressions are:

Examples of usage of expression query are as follows:

Equal to (=)

For example:

Db::name('user')->where('id','=',100)->select();

Equivalent to the following query

Db::name('user')->where('id',100)->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `id` = 100

Not equal to (<>)

For example:

Db::name('user')->where('id','<>',100)->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `id` <> 100

Greater than (>)

For example:

Db::name('user')->where('id','>',100)->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `id` > 100

Greater than or equal to (>=)

For example:

Db::name('user')->where('id','>=',100)->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `id` >= 100

Less than (<)

For example:

Db::name('user')->where('id','<',100)->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `id` < 100

Less than or equal to (<=)

For example:

Db::name('user')->where('id','<=',100)->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `id` <= 100

[NOT] LIKE: LIKE of the same sql

For example:

Db::name('user')->where('name','like','thinkphp%')->select();

The final generated SQL statement is:

SELECT * FROM `think_user` WHERE `name` LIKE 'thinkphp%'

likeQuery supports the use of arrays

Db::name('user')->where('name','like',['%think','php%'],'OR')->select();

The actual generated SQL statement is:

SELECT * FROM `think_user` 
WHERE (`name` LIKE '%think' OR `name` LIKE 'php%')

For more convenience, it should be used directlywhereLikemethod

Db::name('user')->whereLike('name','thinkphp%')->select();
Db::name('user')->whereNotLike('name','thinkphp%')->select();

[NOT] BETWEEN: [not] between sql

Query conditions support strings or arrays, for example:

Db::name('user')->where('id','between','1,8')->select();

Equivalent to the following:

Db::name('user')->where('id','between',[1,8])->select();

The final generated SQL statements are:

SELECT * FROM `think_user` WHERE `id` BETWEEN 1 AND 8

The fastest query method is:

Db::name('user')->whereBetween('id','1,8')->select();
Db::name('user')->whereNotBetween('id','1,8')->select();

[NOT] IN: [not] in the same sql

Query conditions support strings or arrays, for example:

Db::name('user')->where('id','in','1,5,8')->select();

Equivalent to the following:

Db::name('user')->where('id','in',[1,5,8])->select();

The final SQL statement is:

SELECT * FROM `think_user` WHERE `id` IN (1,5,8) 

The fastest query method is:

Db::name('user')->whereIn('id','1,5,8')->select();
Db::name('user')->whereNotIn('id','1,5,8')->select();

[NOT] INQuery support closure usage

[NOT] NULL ：

Query if the field is (not)Null,For example:

Db::name('user')->where('name', null)
->where('email','null')
->where('name','not null')
->select();

The actual generated SQL statement is:

SELECT * FROM `think_user` 
WHERE `name` IS NULL 
AND `email` IS NULL 
AND `name` IS NOT NULL

If you need to query a field's value as a stringnullornot null, should use:

Db::name('user')->where('title','=', 'null')
->where('name','=', 'not null')
->select();

The recommended way is to usewhereNullandwhereNotNullMethod query.

Db::name('user')->whereNull('name')
->whereNull('email')
->whereNotNull('name')
->select();

EXP: Expressions

Supports more complex query situations, such as:

Db::name('user')->where('id','in','1,3,8')->select();

Can be changed to:

Db::name('user')->where('id','exp',' IN (1,3,8) ')->select();

expThe query conditions are not treated as strings, so the following query conditions can be used in any SQL-supported syntax, including the use of function and field names.

Dynamic query

For the above query expression, it can be simplified using dynamic query methods, for example:

Db::name('user')->where('id','>=',100)->select();

It can be simplified to:

Db::name('user')->whereId('>=',100)->select();

For more information about thinkPHP related content, please check out the topic of this site:ThinkPHP Introduction Tutorial》、《Summary of thinkPHP template operation skills》、《Summary of common methods of ThinkPHP》、《Codeigniter Introductory Tutorial》、《Advanced tutorial on CI (CodeIgniter) framework》、《Zend FrameWork Framework Introduction Tutorial"and"PHP template technical summary》。

I hope that the description in this article will be helpful to everyone's PHP programming based on the ThinkPHP framework.