Multi table query of thinkphp study notes

During the operation, there is no problem with the two table queries, but the three table queries begin to have problems

There are the following three tables, which are divided into pl (uid, content), user (id, username) and lyb (uid, title)

There are several methods for multi-table query operation:

(i) View Model (Recommended)

To define the view model, simply inherit Think\ Model\ ViewModel and set the viewFields property

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'), 　 // If there are fields in the table with duplicate names , It can be passed through => Set the alias, 'uid'=>'lid'
    );   

View query:

View queries are similar to queries of different models, and there is no difference.

$Model = D("pl") ->field('uid,title,username,lyb_content')->select();  //pl Is the database name 

If duplicate data is found in the results of the query, the group method can also be used to deal with it.

(ii) join

The JOIN method is also one of the coherent operation methods for querying data from two or more tables based on the relationships between their columns.

join usually has the following types, and different types of join operations will affect the returned data results.

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

The above three tables are also operated

$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();

(iii) table

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

Usage

1 Under normal circumstances, the system can automatically identify the current corresponding data table when operating the model. Therefore, the table method is usually used to:

Switch the data table of the operation;
Operate on multiple tables;

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

Note: The table method queries the values of all fields by default