ThinkPHP database operation view query, subquery, native query

The following tutorial column of thinkphp will introduce you to the view query, subquery and native query of ThinkPHP database operations. I hope it will be helpful to friends in need!

View query

View query can implement multi-table query that does not rely on database views and does not require the database to support views, for example:

Db::view('User','id,name')
    ->view('Profile','truename,phone,email','Profile.user_id=User.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

The generated SQL statement is similar to:

SELECT User.id,User.name,Profile.truename,Profile.phone,Profile.email,Score.score 
FROM think_user User 
INNER JOIN think_profile Profile ON Profile.user_id=User.id 
INNER JOIN think_socre Score ON Score.user_id=Profile.id 
WHERE Score.score > 80

　Note that the view query does not need to call the table and join methods, and only the field name needs to be used when calling the where and order methods. Add table name.

INNER join query is used by default. If you need to change it, you can use:

Db::view('User','id,name')
    ->view('Profile','truename,phone,email','Profile.user_id=User.id','LEFT')
    ->view('Score','score','Score.user_id=Profile.id','RIGHT')
    ->where('score','>',80)
    ->select();

The generated SQL statement is similar to:

SELECT User.id,User.name,Profile.truename,Profile.phone,Profile.email,Score.score 
FROM think_user User 
LEFT JOIN think_profile Profile ON Profile.user_id=User.id 
RIGHT JOIN think_socre Score ON Score.user_id=Profile.id 
WHERE Score.score > 80

You can use an alias:

Db::view('User',['id'=>'uid','name'=>'account'])
    ->view('Profile','truename,phone,email','Profile.user_id=User.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

The generated SQL statement becomes:

SELECT User.id AS uid,User.name AS account,Profile.truename,Profile.phone,Profile.email,Score.score 
FROM think_user User 
INNER JOIN think_profile Profile ON Profile.user_id=User.id 
INNER JOIN think_socre Score ON Score.user_id=Profile.id 
WHERE Score.score > 80

You can use arrays to define table names and aliases, for example:

Db::view(['think_user'=>'member'],['id'=>'uid','name'=>'account'])
    ->view('Profile','truename,phone,email','Profile.user_id=member.id')
    ->view('Score','score','Score.user_id=Profile.id')
    ->where('score','>',80)
    ->select();

The generated SQL statement becomes:

SELECT member.id AS uid,member.name AS account,Profile.truename,Profile.phone,Profile.email,Score.score 
FROM think_user member 
INNER JOIN think_profile Profile ON Profile.user_id=member.id 
INNER JOIN think_socre Score ON Score.user_id=Profile.id 
WHERE Score.score > 80

Subquery

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

1. Use the select method

When the parameter of the select method is false, it means that no query is performed. Return the constructed SQL, for example:

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

The generated subQuery result is:

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

2. Use the fetchSql method

The fetchSql method means that it does not perform a query but only returns the constructed SQL statement, and it not only supports select, but also 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

3. Use buildSql to construct the 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 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), Then we call it directly in subsequent queries.

　It should be noted that you need to add ‘brackets’ yourself when using the first two methods.

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

4. Use closures to construct subqueries

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')    
->where(function($query){        
$query->table('think_profile')->where('status',1);
    },'exists')    
    ->find();

The generated SQL statement is

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

Native query

The Db class supports native SQL query operations, mainly including the following two methods:

query method

query method is used to perform SQL query operations. If the data is illegal or the query is wrong, it returns false, otherwise it returns the query result data set (same as the select method).

Usage example:

Db::query("select * from think_user where status=1");

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

execute method

execute is used to update and write data sql operations, if the data is illegal Or if there is a query error, false is returned, otherwise the number of records affected is returned.

Usage example:

Db::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 is always executed on the write server, so the execute method corresponds to They are all write operations, regardless of what your SQL statement is.

Parameter binding

#Supports parameter binding during native query, including question marks placeholder or named placeholder, for example:

Db::query("select * from think_user where id=? AND status=?",[8,1]);
// 命名绑定
Db::execute("update think_user set name=:name where status=:status",['name'=>'thinkphp','status'=>1]);

The above is the detailed content of ThinkPHP database operation view query, subquery, native query. For more information, please follow other related articles on the PHP Chinese website!