Subquery (query builder 12)

First construct the subquery SQL. You can use the following three methods to construct the subquery.

Use the fetchSql method

The fetchSql method means that it does not query but only returns the constructed SQL statement, and it not only supports select, but supports all CURD queries.

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id', '>', 10)
    ->fetchSql(true)
    ->select();

The generated subQuery result is:

SELECT `id`,`name` FROM `think_user` WHERE `id` > 10

Use buildSql to construct a subquery

$subQuery = Db::table('think_user')
    ->field('id,name')
    ->where('id', '>', 10)
    ->buildSql();

The generated subQuery result is:

( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 )

After calling the buildSql method, the actual query operation will not be performed, but 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 will call it directly in subsequent queries.

Then use the subquery to construct a new query:

Db::table($subQuery . ' a')
    ->where('a.name', 'like', 'thinkphp')
    ->order('id', 'desc')
    ->select();

The generated SQL statement is:

SELECT * FROM ( SELECT `id`,`name` FROM `think_user` WHERE `id` > 10 ) a WHERE a.name LIKE 'thinkphp' ORDER BY `id` desc

Use closure to construct the subquery

Queries such as IN/NOT IN and EXISTS/NOT EXISTS can directly use closures as subqueries, for example:

Db::table('think_user')
    ->where('id', 'IN', function ($query) {
        $query->table('think_profile')->where('status', 1)->field('id');
    })
    ->select();

The generated SQL statement is

SELECT * FROM `think_user` WHERE `id` IN ( SELECT `id` FROM `think_profile` WHERE `status` = 1 )

Db::table('think_user')
    ->whereExists(function ($query) {
        $query->table('think_profile')->where('status', 1);
    })->find();

generated The SQL statement is

SELECT * FROM `think_user` WHERE EXISTS ( SELECT * FROM `think_profile` WHERE `status` = 1 )

In addition to the above query conditions, comparison operations also support the use of closure subqueries