thinkphp study notes multi-table query, thinkphp study notes_PHP tutorial

thinkphp study notes for multi-table query, thinkphp study notes for

During the operation, there were no problems with the two-table query, but problems began to occur with the three-table query

There are the following three tables, the sub-tables are pl table (uid, content), user table (id, username), lyb table (uid, title)

There are several methods for multi-table query operations:

㈠View Model (recommended)

To define the view model, you only need to inherit ThinkModelViewModel and then set the viewFields attribute

public $viewFields = array(
    'pl'    =>array('uid','rid','content'),
    'user'    =>array('id','username','_on'=>'pl.uid=user.id'),
    'lyb'    =>array('uid'=>'lid','content'=>'lyb_content','title','_on'=>'pl.uid=lyb.uid'),　//如果表中有字段重名,可以通过=>设置别名，'uid'=>'lid'
    );   

View query:

View queries are the same as queries for different models, there is no difference.

$Model = D("pl") ->field('uid,title,username,lyb_content')->select();  //pl为数据库名

If you find that there is duplicate data in the query results, you can also use the group method to process it.

㈡join

The JOIN method is also one of the coherent operation methods, used to query data from two or more tables based on the relationship between columns in these tables.

Joins usually have the following types. Different types of join operations will affect the returned data results.

INNER JOIN : Return rows if there is at least one match in the table, equivalent to JOIN
LEFT JOIN: Return all rows from the left table even if there is no match in the right table
RIGHT JOIN: Return all rows from the right table even if there is no match in the left table
FULL JOIN : Return rows
as long as there is a match in one of the tables The join method can support the above four types:

The same operation is performed on the above three tables

$Model = D("pl")
　　　　　　　　　　->join('lyb on pl.uid = lyb.uid')
　　　　　　　　　　->join('user on pl.uid = user.id') 
　　　　　　　　　　->field('user.username,lyb.title,pl.content')
　　　　　　　　　　->select();

㈢table

The table method is also one of the coherent operation methods of the model class, and is mainly used to specify the data table for operation.

Usage

Generally, the system can automatically identify the current corresponding data table when operating the model, so the table method is usually used for:

Data table for switching operations;
Perform operations on multiple tables;                                                                                                          

$Model = D("pl")
　　->field('pl.content,user.username,lyb.title')
　　->table('pl,lyb,user')
　　->limit(10)
　　->select();

Multiple table query:

$list=M()->table(array('think_select'=>'this0','think_student'=>' this1','think_class'=>'this2'))
->where('this0.stu_id=this1.id and this0.class_id=this2.id')
->field('this0 .id this0_id,this1.id this1_id,this2.id this2_id')->select();
Generate sql:
select this0.id this0_id,this1.id this1_id,this2.id this2_id

from think_select this0, think_student this1, think_class this2
where this0.stu_id=this1.id and this0.class_id=this2.id

Link query:

$list=M( )->table("think_select this0")->join('think_student this1 on this0.stu_id=this1.id')
->join('think_class this2 on this0.stu_id=this2.id')
->field('this0.id this0_id,this1.id this1_id,this2.id this2_id')->select();
Generate sql:
select this0.id this0_id,this1.id this1_id,this2.id this2_id

from think_select this0 left join think_student this1 on this0.stu_id=this1.id
left join think_class this2 on this0.stu_id=this2.id