Laravel Nested Queries with Query Builder

Question

This is a search function that returns each member with the last registered year.

I worked with a call to DB :: raw (). But he cannot get it to work with the query builder.

Work code:

$query = DB::table('membership as m'); $query->join( DB::raw( '(SELECT my.* FROM membership_years my INNER JOIN ( SELECT member_id,MAX(membership_year) AS max_my FROM membership_years GROUP BY member_id ) my2 ON my.member_id = my2.member_id AND my.membership_year = my2.max_my ) my' ) ,'m.id','=','my.member_id'); 

My attempt to create a request code:

 $query = DB::table('membership as m'); $query->join('membership_years as my', function($j1){ $j1->join('membership_years as my2', function($j2){ $j2->where('my.membership_year','=','MAX(my2.membership_year)') ->on('my.member_id','=','my2.member_id'); } )->on('m.id','=','my.member_id'); } ); 

Resulting error:

Call the undefined method Illuminate \ Database \ Query \ JoinClause :: join ()

I'm not sure if this is because $j2 no longer has access to the join method?

Raw MySQL query:

 SELECT my.membership_year,m.* FROM membership AS m INNER JOIN ( SELECT my1.* FROM membership_years my1 INNER JOIN ( SELECT member_id,MAX(membership_year) AS max_my FROM membership_years GROUP BY member_id ) my2 ON my1.member_id = my2.member_id AND my1.membership_year = my2.max_my ) my ON m.id = my.member_id ORDER BY m.id ASC 

Answer 1

Method 1. You can write part of the request using the builder:

  $query = DB::table('membership as m') ->select('my.membership_year', 'm.*') ->join(DB::raw('( SELECT my1.* FROM membership_years my1 INNER JOIN ( SELECT member_id, MAX(membership_year) AS max_my FROM membership_years GROUP BY member_id ) my2 ON my1.member_id = my2.member_id AND my1.membership_year = my2.max_my ) my'), 'm.id', '=', 'my.member_id') ->orderBy('m.id'); 

Method 2. You can also write subqueries and use the toSql() method:

 $sub1 = DB::table('membership_years') ->select('member_id', DB::raw('MAX(membership_year) AS max_my')) ->groupBy('member_id'); $sub2 = DB::table('membership_years as my1') ->select('my1.*') ->join(DB::raw('(' . $sub1->toSql() . ') my2'), function ($join) { $join ->on('my1.member_id', '=', 'my2.member_id') ->on('my1.membership_year', '=', 'my2.max_my'); }); $query = DB::table('membership as m') ->select('my.membership_year', 'm.*') ->join(DB::raw('(' . $sub2->toSql() . ') my'), 'm.id', '=', 'my.member_id') ->orderBy('m.id');