Laravel nested join queries with query builder

Question

This is a search function that returns each member with their most recent year registered.

I got it working with a DB::raw() call. But can't get it working with the query builder.

Working 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 on query builder 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');
  }
);

The resulting error is:

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

I'm not sure if this is because the $j2 doesn't have access to the join method anymore?

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

Decembrist

Answer 1

Way 1. You can write part of the query with 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');

Way 2. Also you can write subqueries and use 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');

Jennefer

Great examples showing off complex joins, still works perfect in laravel 5.7 – Lonesome