php laravel 连接查询,php – Laravel使用查询构建器嵌套连接查询

这是一个搜索功能,可以返回每个成员注册的最近年份.

我让它使用DB :: raw()调用.但无法让它与查询构建器一起使用.

工作守则：

$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');

我对查询构建器代码的尝试：

$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');

}

);

产生的错误是：

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

我不确定这是不是因为$j2再也无法访问join方法了？

原始MySQL查询：

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