Example analysis of tp5.1 framework join method usage

Time:2020-9-23

This paper describes the usage of join method in tp5.1 framework. For your reference, the details are as follows:

JOINMethod is used to query data from two or more tables based on the relationship between the columns in those tables. Join usually has the following types. Different types of join operations will affect the returned data results.

  • INNER JOIN: equivalent to join (the default join type), and returns a row if there is at least one match in the table
  • 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: return row as long as there is a match in one of the tables

explain


join ( mixed join [, mixed $condition = null [, string $type = 'INNER']] )
leftJoin ( mixed join [, mixed $condition = null ] )
rightJoin ( mixed join [, mixed $condition = null ] )
fullJoin ( mixed join [, mixed $condition = null ] )

parameter

join

The (full) table name and alias to associate

Supported writing method:

  • Writing method 1: [‘full table name or subquery ‘= >’alias’]
  • Writing method 2: ‘table name without data table prefix’ (automatically as alias)
  • Write 2: ‘table name alias without data table prefix’

condition

Association conditions. It can be a string or an array. When it is an array, each element is an association condition.

type

Association type. It can be:`INNER`、`LEFT`、`RIGHT`、`FULL`, case insensitive, default is`INNER`。

Return value

Model object

give an example


Db::table('think_artist')
->alias('a')
->join('work w','a.id = w.artist_id')
->join('card c','a.card_id = c.id')
->select();


Db::table('think_user')
->alias('a')
->join(['think_work'=>'w'],'a.id=w.artist_id')
->join(['think_card'=>'c'],'a.card_id=c.id')
->select();

Inner join mode is adopted by default. If you need to use other join methods, you can change it to


Db::table('think_user')
->alias('a')
->leftJoin('word w','a.id = w.artist_id')
->select();

The table name can also be a subquery


$subsql = Db::table('think_work')
->where('status',1)
->field('artist_id,count(id) count')
->group('artist_id')
->buildSql();
 
Db::table('think_user')
->alias('a')
->join([$subsql=> 'w'], 'a.artist_id = w.artist_id')
->select();

More about ThinkPHP related content, interested readers can view this site topic: “ThinkPHP introduction tutorial”, “ThinkPHP template operation skills summary”, “ThinkPHP common methods summary”, “CodeIgniter introductory tutorial”, “Ci (CodeIgniter) framework advanced tutorial”, “Zend framework framework introduction tutorial” and “PHP template technology summary”.

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