laravel leftjoin on orOn的源码剖析

发布日期: 2020-09-21

文章字数: 822

leftjoin连接时需要对不止一个条件进行进行匹配，这时候就需要使用闭包方式

leftjoin('db', function ($join) {···});

原生join orOn例子

原生sql例子如下：

SELECT
    *
FROM
    `a`
    LEFT JOIN `b` ON `b`.`deleted_at` IS NULL 
    AND ( `a`.`a` = `b`.`a` AND `a`.`b` = `b`.`b` AND `a`.`c` = `b`.`c` ) 
    OR  ( `a`.`a` = `b`.`a` AND `a`.`b` = `b`.`b` AND `a`.`d` = `b`.`d` )

想用model的方法实现join 的 orOn，百度谷歌过后没有找到合适的资料

官方文档的orOn资料如下：
https://laravel.com/docs/6.x/queries#joins

Cross Join Clause

To perform a “cross join” use the crossJoin method with the name of the table you wish to cross join to. Cross joins generate a cartesian product between the first table and the joined table:

$users = DB::table('sizes')
            ->crossJoin('colors')
            ->get();

Advanced Join Clauses

You may also specify more advanced join clauses. To get started, pass a Closure as the second argument into the join method. The Closure will receive a JoinClause object which allows you to specify constraints on the join clause:

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

源码剖析

这时候只能寻找源码了

先从`$join->on`开始

文件地址：/vendor/laravel/framework/src/Illuminate/Database/Query/JoinClause.php

/**
  * AddHoneycombLog an "on" clause to the join.
  *
  * On clauses can be chained, e.g.
  *
  *  $join->on('contacts.user_id', '=', 'users.id')
  *       ->on('contacts.info_id', '=', 'info.id')
  *
  * will produce the following SQL:
  *
  * on `contacts`.`user_id` = `users`.`id` and `contacts`.`info_id` = `info`.`id`
  *
  * @param  \Closure|string  $first
  * @param  string|null  $operator
  * @param  string|null  $second
  * @param  string  $boolean
  * @return $this
  *
  * @throws \InvalidArgumentException
  */
 public function on($first, $operator = null, $second = null, $boolean = 'and')
 {
     if ($first instanceof Closure) {
         return $this->whereNested($first, $boolean);
     }

     return $this->whereColumn($first, $operator, $second, $boolean);
 }

 /**
  * AddHoneycombLog an "or on" clause to the join.
  *
  * @param  \Closure|string  $first
  * @param  string|null  $operator
  * @param  string|null  $second
  * @return \Illuminate\Database\Query\JoinClause
  */
 public function orOn($first, $operator = null, $second = null)
 {
     return $this->on($first, $operator, $second, 'or');
 }

由上可见，join中的on 可以传递 $boolean = 'or'的
使用on 传递boolean参数 === orOn方法

单where条件

可以通过以下代码实现：

$info = DB::table('table_name')
    ->leftJoin('table_name2', function ($join) {
        $join->on('table_name.a', '=', 'table_name2.a')
             ->orOn('table_name.b', '=', 'table_name2.b');
    })->get();

查看`whereColumn`方法

文件地址：/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php

/**
 * AddHoneycombLog a "where" clause comparing two columns to the query.
 *
 * @param  string|array  $first
 * @param  string|null  $operator
 * @param  string|null  $second
 * @param  string|null  $boolean
 * @return \Illuminate\Database\Query\Builder|static
 */
public function whereColumn($first, $operator = null, $second = null, $boolean = 'and')
{
    // If the column is an array, we will assume it is an array of key-value pairs
    // and can add them each as a where clause. We will maintain the boolean we
    // received when the method was called and pass it into the nested where.
    if (is_array($first)) {
        return $this->addArrayOfWheres($first, $boolean, 'whereColumn');
    }

    // If the given operator is not found in the list of valid operators we will
    // assume that the developer is just short-cutting the '=' operators and
    // we will set the operators to '=' and set the values appropriately.
    if ($this->invalidOperator($operator)) {
        [$second, $operator] = [$operator, '='];
    }

    // Finally, we will add this where clause into this array of clauses that we
    // are building for the query. All of them will be compiled via a grammar
    // once the query is about to be executed and run against the database.
    $type = 'Column';

    $this->wheres[] = compact(
        'type', 'first', 'operator', 'second', 'boolean'
    );

    return $this;
}

由is_array可见，是可以传递数组的

多where条件

可以通过以下代码实现：

$info = DB::table('table_name')
    ->leftJoin('table_name2', function ($join) {
        $join->on([
                   ['table_name.a', '=', 'table_name2.a'],
                   ['table_name.b', '=', 'table_name2.b'],
                   ['table_name.c', '=', 'table_name2.c'],
             ])
           ->orOn([
                   ['table_name.a', '=', 'table_name2.a'],
                   ['table_name.b', '=', 'table_name2.b'],
                   ['table_name.d', '=', 'table_name2.d'],
           ]);
    })->get();