To realize the need to use sub-query in JOIN, so according to the documents and source code to find methods

Laravel Leftjoin on orOn source code analysis

Native SQL that you want to implement

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` ) 
Copy the code

Methods given by official documents (subquery join)

You can use joinSub, leftJoinSub, and rightJoinSub methods to join subqueries of a query. Each of these methods takes three parameters: a subquery, its table alias, and a Closure that defines the related column:

$latestPosts = DB::table('posts')
                   ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
                   ->where('is_published'.true)
                   ->groupBy('user_id');

$users = DB::table('users')
        ->joinSub($latestPosts.'latest_posts'.function ($join) {
            $join->on('users.id'.'='.'latest_posts.user_id');
        })->get();
Copy the code

Implementation method 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();Copy the code

Implementation method 2: Join +getBindings+addBinding

SQL > select addBinding from ‘where’; ‘

$query = DB::table('table_name2')->where('type'.'='.2);
 
$info = DB::table('table_name')
            ->join(DB::raw('('.$query->toSql().') as table_name2'), function ($join) use ($query) {
                $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'],
                    ])->addBinding($query->getBindings());
            })->get();
Copy the code

Source code analysis joinSub method

File address: / vendor/laravel/framework/SRC/Illuminate the 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)
 {
 	 // Result: Query is the native SQL, bindings is the binding parameter of getBindings
 	 // See below for the createSub method
     [$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;
  }
Copy the code

The above code implements native SQL in a Model fashion

Laravel join subquery joinSub source code analysis to this, I hope the article can help solve your problem, looking forward to your attention 👍