Analysis of sub query operation example in tp5.1 framework database

Time:2020-9-24

This paper describes the sub query operation of tp5.1 framework database. For your reference, the details are as follows:

First, construct the subquery SQL. You can use the following three ways to build a subquery.

Using the fetchsql method

The fetchsql method represents that it does not query, but only returns the built SQL statement, and not only supports itselectAll curd queries are supported.


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

The subquery results are as follows:


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

Construct subquery using buildsql


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

The subquery results are:


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

After calling the buildsql method, the actual query operation will not be carried out, but only the SQL statement of the query will be generated (in order to avoid confusion, brackets will be added around the SQL), and then we will directly call it in the subsequent query.

Then, the subquery is used 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

Constructing subqueries using closures

IN/NOT INandEXISTS/NOT EXISTSFor 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();

The generated SQL statement is


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

More readers interested in ThinkPHP can see the topic of this site: introduction to ThinkPHP, summary of ThinkPHP template operation skills, summary of common methods of ThinkPHP, introduction to CodeIgniter, advanced tutorial of CI framework, introduction to Zend framework framework and PHP template technology summary.

I hope this article will be helpful to the PHP program design based on ThinkPHP framework.