How to convert a sql which has a join within a left join to query builder?

Question

I need to write a query builder from sql which has a join within a left join.

This is the SQL which has a join within another left join.

select v_dts.* from v_dts 
left join(select in_d2.ref_book from in_h
inner join in_d2 on(in_h.doc_code=in_d2.doc_code and in_h.book=in_d2.book) 
where in_h.doc_code='IN' group by in_d2.ref_book) as i
on(v_dts.book=i.ref_book)

(Problem)This is the Query Builder that I try to convert from the SQL above.

$order_progress = DB::table('v_dts')
->select('v_dts.*')
->leftJoin('in_h',function($join_in_h){
   $join_in_h->select('in_d2.ref_book');
      $join_in_h->join('in_d2',function($join_in_d2){
      $join_in_d2->on('in_h.doc_code','=','in_d2.doc_code');
      $join_in_d2->on('in_h.book','=','in_d2.book');
      $join_in_d2->where('in_h.doc_code','=','IN');
      $join_in_d2->groupBy('in_d2.ref_book');
   });
})
->get();

However, my query builder is wrong. It show an error message

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

and generated SQL

SQL: select v_dts.* from v_dts left join (in_h inner join in_d2 on in_h.doc_code = in_d2.doc_code and in_h.book = in_d2.book and in_h.doc_code = IN)

Anyone can help me to figure out my problem? Thanks.

Answer 1

You need a subquery left join. ->leftJoin('in_h',function($join_in_h){...} does not do a subquery join; that's a regular left join with a fancy join clause.

Instead use ->leftJoinSub.

You may use the joinSub, leftJoinSub, and rightJoinSub methods to join a query to a sub-query. Each of these methods receive three arguments: the sub-query, its table alias, and a Closure that defines the related columns.

Write the subquery.

$ref_books = DB::table('in_h')
    ->select('in_d2.ref_book')
    ->join('in_d2', function($join){
        $join->on('in_h.doc_code','=','in_d2.doc_code')
             ->where('in_h.book','=','in_d2.book');
    })
    ->where('in_h.doc_code','=','IN')
    ->groupBy('in_d2.ref_book');

And use it in another query.

$order_progress = DB::table('v_dts')
    ->select('v_dts.*')
    ->leftJoinSub($ref_books, 'i',function($join){
        $join->on('v_dts.book', '=', 'i.ref_book');
    })
    ->get();

You can use ->dd() and ->dump() to dump the generated SQL for debugging.

See "Sub-Query Joins" in the Laravel docs.

Note: I don't have Laravel so I can't check this is 100% correct, but it should get you on the right path.