Advanced Queries (Query Builder 9)

Quick query

The shortcut query method is a simplified way of writing the same query conditions in multiple fields, which can further simplify the way of writing query conditions. Multiple fields are separated by | to represent OR query, and separated by & to represent AND query. The following query can be implemented, for example:

Db::table('think_user')
    ->where('name|title','like','thinkphp%')
    ->where('create_time&update_time','>',0)
    ->find();

The generated query SQL is:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' OR `title` LIKE 'thinkphp%' ) AND ( `create_time` > 0 AND `update_time` > 0 ) LIMIT 1

Quick query support All query expressions.

Batch (field) query

You can define batch conditional queries with multiple conditions, for example:

Db::table('think_user')
    ->where([
        ['name', 'like', 'thinkphp%'],
        ['title', 'like', '%thinkphp'],
        ['id', '>', 0],
        ['status', '=', 1],
    ])
    ->select();

The generated SQL statement is:

SELECT * FROM `think_user` WHERE `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' AND `id` > 0 AND `status` = '1'

If you use exp query in array mode, you must use the raw method.

Db::table('think_user')
    ->where([
        ['name', 'like', 'thinkphp%'],
        ['title', 'like', '%thinkphp'],
        ['id', 'exp', Db::raw('>score')],
        ['status', '=', 1],
    ])
    ->select();

Array query method, ensure that your query array cannot be controlled by user-submitted data. The form data submitted by the user should be passed in as an element of the query array, as follows:

Db::table('think_user')
    ->where([
        ['name', 'like', $name . '%'],
        ['title', 'like', '%' . $title],
        ['id', '>', $id],
        ['status', '=', $status],
    ])
    ->select();

Note, Multiple query conditions for the same field may be merged. If you want the conditions in a certain where method to be processed separately, you can use the following method to avoid being affected by other conditions.

$map = [
        ['name', 'like', 'thinkphp%'],
        ['title', 'like', '%thinkphp'],
        ['id', '>', 0],
    ];
Db::table('think_user')
    ->where([ $map ])
    ->where('status',1)
    ->select();

The generated SQL statement is:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' AND `id` > 0 ) AND `status` = '1'

Make good use of multi-dimensional array queries, you can easily assemble various complex SQL statements

If you use the following multiple The SQL statement generated by condition combination

$map1 = [
        ['name', 'like', 'thinkphp%'],
        ['title', 'like', '%thinkphp'],
    ];
    
$map2 = [
        ['name', 'like', 'kancloud%'],
        ['title', 'like', '%kancloud'],
    ];    
    
Db::table('think_user')
    ->whereOr([ $map1, $map2 ])
    ->select();

is:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `title` LIKE '%thinkphp' ) OR ( `name` LIKE 'kancloud%' AND `title` LIKE '%kancloud' )

Closure query

$name = 'thinkphp';
$id = 10;
Db::table('think_user')->where(function ($query) use($name, $id) {
    $query->where('name', $name)
        ->whereOr('id', '>', $id);
})->select();

The SQL statement generated is:

SELECT * FROM `think_user` WHERE ( `name` = 'thinkphp' OR `id` > 10 )

It can be seen that brackets will be automatically added on both sides of each closure condition.

Mixed query

You can combine all the methods mentioned above to perform mixed query, for example:

Db::table('think_user')
    ->where('name', ['like', 'thinkphp%'], ['like', '%thinkphp'])
    ->where(function ($query) {
        $query->where('id', ['<', 10], ['>', 100], 'or');
    })
    ->select();

The generated SQL statement is:

SELECT * FROM `think_user` WHERE ( `name` LIKE 'thinkphp%' AND `name` LIKE '%thinkphp' ) AND ( `id` < 10 or `id` > 100 )

String condition query

For some really complex queries, you can also directly use native SQL statements to query, for example:

Db::table('think_user')
    ->where('id > 0 AND name LIKE "thinkphp%"')
    ->select();

For safety reasons, we can use parameter binding for string query conditions, for example:

Db::table('think_user')
    ->where('id > :id AND name LIKE :name ', ['id' => 0, 'name' => 'thinkphp%'])
    ->select();

It is recommended to use the whereRaw method.

Db::table('think_user')
    ->whereRaw('id > :id AND name LIKE :name ', ['id' => 0, 'name' => 'thinkphp%'])
    ->select();

Using query

You can query by calling the where method once and passing in the Query object.

$query = new \think\db\Query;
$query->where('id','>',0)
	->where('name','like','%thinkphp');

Db::table('think_user')
    ->where($query)
    ->select();

The where method of the Query object can only be called once, and can only be called in the where method. If a non-query condition chain method is used in the query object, the current query will not be passed in.

$query = new \think\db\Query;
$query->where('id','>',0)
	->where('name','like','%thinkphp')
    ->order('id','desc') // 不会传入后面的查询
    ->field('name,id'); // 不会传入后面的查询

Db::table('think_user')
    ->where($query)
    ->where('title','like','thinkphp%') // 有效
    ->select();

Shortcut methods

The system encapsulates a series of shortcut methods to simplify queries, including:

Field OR queryField XOR queryQuery whether the field is NullQuery whether the field is not NullField IN queryField NOT IN query ##whereBetween##whereNotBetweenwhereLike whereNotLikewhereExistswhereNotExistswhereExpwhereColumnThe following are examples of the next two fields Comparative query conditions usage of whereColumn method.

Method	Function
##whereOr
whereXor
whereNull
whereNotNull
whereIn
whereNotIn
Field BETWEEN query
`Field NOT BETWEEN query`
`Field LIKE query`
`Field NOT LIKE query`
`EXISTS condition query`
`NOT EXISTS condition query`
`Expression query`
`Compare two fields`

Query user data whose update_time is greater than create_time

Db::table('think_user')
    ->whereColumn('update_time','>','create_time')
    ->select();

The generated SQL statement is:

SELECT * FROM `think_user` WHERE ( `update_time` > `create_time` )

Query user data whose name and nickname are the same

Db::table('think_user')
    ->whereColumn('name','=','nickname')
    ->select();

The generated SQL statement is:

SELECT * FROM `think_user` WHERE ( `name` = `nickname` )

The same field conditions can also be simplified to

Db::table('think_user')
    ->whereColumn('name','nickname')
    ->select();

Supports array comparison of multiple fields

Db::name('user')->whereColumn([
	['title', '=', 'name'],
    ['update_time', '>=', 'create_time'],
])->select();

The generated SQL statement is:

SELECT * FROM `think_user` WHERE ( `name` = `nickname` AND `update_time` > `create_time` )

Dynamic query

The query constructor also provides a dynamic query mechanism to simplify query conditions, including:

Dynamic queryDescriptionwhereFieldNamewhereOrFieldName##getByFieldNameGet a value based on a certain field

// 根据邮箱（email）查询用户信息
$user = Db::table('user')
	->whereEmail('thinkphp@qq.com')
    ->find();

// 根据昵称（nick_name）查询用户
$email = Db::table('user')
    ->whereNickName('like', '%流年%')
    ->select();
    
// 根据邮箱查询用户信息
$user = Db::table('user')
    ->getByEmail('thinkphp@qq.com');
    
// 根据昵称（nick_name）查询用户信息
$user = Db::table('user')
    ->field('id,name,nick_name,email')
    ->getByNickName('流年');
    
// 根据邮箱查询用户的昵称
$nickname = Db::table('user')
    ->getFieldByEmail('thinkphp@qq.com', 'nick_name');
    
// 根据昵称（nick_name）查询用户邮箱
$email = Db::table('user')
    ->getFieldByNickName('流年', 'email');

getBy and getFieldBy methods will only query one record and can be used in conjunction with other chain methods


`Query a certain field Value`
`Query the value of a certain field`
Query based on a certain field	##getFieldByFieldName
	FieldName represents the camel case representation of the actual field name of the data table. Assuming that there are email and nick_name fields in the data table user, we can query like this.

Conditional query

The query builder supports conditional queries, such as:

Db::name('user')->when($condition, function ($query) {
    // 满足条件后执行
    $query->where('score', '>', 80)->limit(10);
})->select();

and supports branch queries that do not meet the conditions

Db::name('user')->when($condition, function ($query) {
    // 满足条件后执行
    $query->where('score', '>', 80)->limit(10);
}, function ($query) {
    // 不满足条件执行
    $query->where('score', '>', 60);
});

Advanced Queries (Query Builder 9)

php.cn