laravel join 子查询 joinSub

向右看齐 2023-10-06 19:59 22阅读 0赞

#### 阅读目录 ####

*  想要实现的原生sql
 *  官方文档给的方法(子查询联接)
 *   *  实现方法1：joinSub
 *  joinSub 源码
 *  Laravel left join 多个条件用法

## 想要实现的原生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);
       });
    }