thinkphp Multi-Table query

In the process of learning thinkphp, you need to operate on multiple tables, but in the actual process, always encountered a variety of problems, so write this blog post, as their own learning process

There is no problem with both table queries during the operation, but the three-table query begins to have problems

With the following three tables, the table is PL table (uid,content), User table (id,username), Lyb table (Uid,title)

There are several ways to do a multi-table query:

㈠ view model (recommended)

To define the view model, simply inherit the Think\model\viewmodel and set the ViewFields property to

 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 is a field name in the table, you can set the alias by = =, ' uid ' = ' lid '        );     

View query:

There is no difference between a view query and a query of a different model.

$Model = D ("pl")->field (' Uid,title,username,lyb_content ')->select ();   PL is the database name

You can also use the group method if you find that the result of the query has duplicate data.

㈡join

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

Joins typically have the following types, and different types of join operations affect the returned data results.

• INNER Join: Returns a row if there is at least one match in the table, equivalent to join
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

The Join method can support four of the above types:

The same is true for the above three sheets

$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 consistent operations of the model class and is used primarily to specify the data table for the operation.

Usage

In general, the system can automatically recognize the current corresponding data table when manipulating the model, so the table method is usually used to:

2. Data table for switching operations;
3. operate on multiple tables;

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

   Note: The table method defaults to querying the values of all fields