laravel join 子查询 joinSub

想要实现的原生sql

SELECT
*
FROM
	`a`
	LEFT JOIN ( SELECT * FROM `b` WHERE `type` = 2 ) AS b 
	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` )

官方文档给的方法(子查询联接)

您可以使用 joinSub，leftJoinSub 和 rightJoinSub方法加入查询的子查询。这些方法中的每一个都接收三个参数：子查询，其表别名和定义相关列的Closure：

$reminds = DB::table('hr')
            ->select('repeat_type_id',DB::raw('create_date as created_at'))
            ->where('valid',1)
            ->groupBy('repeat_type_id');

$repeat_types = DB::table('hrt')
            ->joinSub($reminds,'reminds_tables',function($join){
                $join->on('hrt.id','=','reminds_tables.repeat_type_id');
            })
            ->get();

print_r($repeat_types);

select * from `hr` 
inner join (
	select `repeat_type_id`, create_date as created_at from `hrt` 
	where `valid` = 1 group by `repeat_type_id`) as `reminds_tables` 
on 
`hrt`.`id` = `reminds_tables`.`repeat_type_id`

实现方法1：joinSub

$query = DB::table('table_name2')->where('type','=',2);
$info = DB::table('table_name')
            ->joinSub($query,'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();

joinSub 源码

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

/**
  * AddHoneycombLog a subquery join clause to the query.
  *
  * @param  \Closure|\Illuminate\Database\Query\Builder|string  $query
  * @param  string  $as
  * @param  \Closure|string  $first
  * @param  string|null  $operator
  * @param  string|null  $second
  * @param  string  $type
  * @param  bool  $where
  * @return \Illuminate\Database\Query\Builder|static
  *
  * @throws \InvalidArgumentException
  */
 public function joinSub($query, $as, $first, $operator = null, $second = null, $type = 'inner', $where = false)
 {
 	 // 结果：query为原生sql，bindings为getBindings的绑定参数
 	 // createSub方法见下方
     [$query, $bindings] = $this->createSub($query);

     $expression = '('.$query.') as '.$this->grammar->wrapTable($as);

     $this->addBinding($bindings, 'join');

     return $this->join(new Expression($expression), $first, $operator, $second, $type, $where);
 }

 /**
  * Creates a subquery and parse it.
  *
  * @param  \Closure|\Illuminate\Database\Query\Builder|string  $query
  * @return array
  */
 protected function createSub($query)
 {
     // If the given query is a Closure, we will execute it while passing in a new
     // query instance to the Closure. This will give the developer a chance to
     // format and work with the query before we cast it to a raw SQL string.
     if ($query instanceof Closure) {
         $callback = $query;

         $callback($query = $this->forSubQuery());
     }

     return $this->parseSub($query);
 }
 /**
   * Parse the subquery into SQL and bindings.
   *
   * @param  mixed  $query
   * @return array
   *
   * @throws \InvalidArgumentException
   */
  protected function parseSub($query)
  {
      if ($query instanceof self || $query instanceof EloquentBuilder) {
          return [$query->toSql(), $query->getBindings()];
      } elseif (is_string($query)) {
          return [$query, []];
      } else {
          throw new InvalidArgumentException(
              'A subquery must be a query builder instance, a Closure, or a string.'
          );
      }
  }
/**
  * AddHoneycombLog a binding to the query.
  *
  * @param  mixed  $value
  * @param  string  $type
  * @return $this
  *
  * @throws \InvalidArgumentException
  */
  public function addBinding($value, $type = 'where')
  {
      if (! array_key_exists($type, $this->bindings)) {
          throw new InvalidArgumentException("Invalid binding type: {$type}.");
      }

      if (is_array($value)) {
          $this->bindings[$type] = array_values(array_merge($this->bindings[$type], $value));
      } else {
          $this->bindings[$type][] = $value;
      }

      return $this;
  }

Laravel left join 多个条件用法

$query = EmailRecord::select($email_title_select);
$query->where($email_record_table.'.valid', 1);
$query->orderBy($email_record_table.'.id', 'desc');
$query->leftJoin($email_report_send_table." as send",function($join)use($email_record_table){
    $join->on('send.email_record_id',"=", $email_record_table.'.id')->where('send.valid',1);
});

if($contacts){
   $query->rightJoin($email_contact_link_table,function($join)use($contacts,$email_contact_link_table,$email_record_table){
       $join->on($email_record_table.'.id','=',$email_contact_link_table.'.email_record_id');
       $join->where($email_contact_link_table.'.valid',1);
       foreach ($contacts as $cont){
           $join->orWhere($email_contact_link_table.'.contact_name', 'like','%'.$cont.'%');
       }
   });
}else{
   $query->leftJoin($email_contact_link_table,function($join)use($contacts,$email_contact_link_table,$email_record_table){
       $join->on($email_record_table.'.id','=',$email_contact_link_table.'.email_record_id')->where($email_contact_link_table.'.valid',1);
   });
}