What is the usage of join in thinkphp5

In thinkphp5, join is used to query data from two or more tables based on the relationship between columns in these tables. It is one of the coherent operation methods. The returned result is a model object. Syntax is "join (table name to be associated and alias [association condition [association type]])".

The operating environment of this article: Windows 10 system, ThinkPHP version 5, Dell G3 computer.

What is the usage of join in thinkphp5

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

Description

object join ( mixed join [, mixed $condition = null [, string $type = 'INNER']] )

Parameters

join: The (complete) table name and alias to be associated, supports three writing methods:

• Writing method 1: ['Complete table name or subquery'=>'alias']

• Writing method 2:'Complete table name alias'

• Writing 3: 'Table name without data table prefix'

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

type: Association type. Can be: INNER, LEFT, RIGHT, FULL, case-insensitive, default is INNER.

Return value: model object

Join usually has the following types. Different types of join operations will affect the returned data results.

• INNER JOIN: Equivalent to JOIN (default JOIN type), if there is at least one match in the table, the row is returned

• 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: As long as there is a match in one of the tables, rows are returned

Examples are as follows:

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

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

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

The above three The writing method has the same effect. __WORK__ and __CARD__ will be converted into think_work and think_card during final analysis. Note: The table name in the middle of the '_table name_' method needs to be in uppercase letters

If you don't want to use an alias, the full name of the table must be used in the subsequent conditions. You can use the following method

Db::table('think_user')->join('__WORK__','__ARTIST__.id = __WORK__.artist_id')->select();

The INNER JOIN method is used by default. If you need to use other JOIN methods, you can change it to

Db::table('think_user')->alias('a')->join('word w','a.id = w.artist_id','RIGHT')->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();

Because the statement returned by buildSql contains () , so there is no need to add () at both ends.

Recommended learning: "PHP Video Tutorial"

The above is the detailed content of What is the usage of join in thinkphp5. For more information, please follow other related articles on the PHP Chinese website!