Using eloquent how to get all records of a many to many relationship that the max value in their pivot table

Question

I want to select a specific record from table a and all their latest records from table a_b that has relation with table b before a specific date. For example

Table (a)

id	name
1	aa

Table (b)

id	name
1	b1
2	b2
3	b3
4	b4

Table (a_b)

id	a_id	b_id	date
1	1	1	2022-09-06
2	1	2	2022-09-06
3	1	1	2022-09-07
4	1	2	2022-09-07
5	1	1	2022-09-10
6	1	2	2022-09-10

If I want the latest records before 2022-09-09 the Output should be

id	a_name	b_name	date
1	aa	b1	2022-09-07
2	aa	b2	2022-09-07

This is can be done using pure SQL:

select a.id,a.name,b.name,a_b.date
from a
LEFT JOIN a_b on a_b.a_id = a.id
INNER JOIN b on b.id = a_b.b_id

where a.id = 1
AND a_b.date = (
select max(a_b.date) from a
LEFT JOIN a_b on a_b.a_id = a.id
INNER JOIN b on b.id = a_b.b_id
WHERE a.id = 1 AND a_b.date < = '2022-09-09')

How can I achieve the same output using Laravel eloquent using (with)?

A::with([
'B'=> function ($query) use ($date) {
/// what should I write here
}])->get()

Answer 1

here is an exact conversion from your sql to laravel

A::select('a.id', 'a.name', 'b.name', 'a_b.date')
    ->leftJoin('a_b', 'a_b.a_id', '=', 'a.id')
    ->join('b', 'b.id', '=', 'a_b.b_id')
    ->where('a.id', 1)
    ->where('a_b.date', function($query) {
        $query->selectRaw('max(`a_b`.`date`)')
            ->from('a')
            ->leftJoin('a_b', 'a_b.a_id', '=', 'a.id')
            ->join('b', 'b.id', '=', 'a_b.b_id')
            ->where('a.id', 1)
            ->where('a_b.date', '<=', '2022-09-09');
    })
->get()

I can't figure out how to use mysql max function with pivot tables. in laravel you can use ->withPivot() and ->wherePivot() on relationships, but im not sure how to limit to 1 (latest)

It seems it's only possible with an inner join using a sub query https://stackoverflow.com/a/12526264/10917416. using this method actually does a double join and is a bit messy (im sure it can probably be cleaned up)

A::with([
    'b' => function ($query) {
        $query->join(
            DB::raw('('.
                DB::table('a_b')
                    ->selectRaw('`b_id`, max(`a_b`.`date`) as max_date')
                    ->groupBy('b_id')
                    ->where('a_b.date', '<=', DB::raw(':max_date'))
                    ->toSql()
            .') AB_2'), // end DB::raw
        'b.id',
        '=', 
        'AB_2.b_id')
        ->select('max_date as date', 'name', 'b.id')
        ->setBindings(['max_date'=>'2022-09-09']);
    }
])->get();

That being said, i'm not sure whats wrong with your implementation using joins. I guess you'll need to test for performance