LeftJoin on MAX date - get specific column for another join( in subquery )

Hello,

Basically i have 3 tables - shipments , main_status_shipment and main_statuses There are 10 main_statuses.

I have huge amounts of shipments and for every shipment you can have up to 10 rows in the pivot table.

What i need to do is basically start from the shipments table -> leftJoin the pivot table on the MAX(created_at) and left join the main_statuses table.

I tried doing it in this manner

 $latestMainStatus = Capsule::table('main_status_shipment ')
            ->select('main_status_id', 'shipment_id', Capsule::raw('MAX(created_at) as last_main_status_created_at'))
            ->groupBy('shipment_id');

$mainQuery = Capsule::table('shipments')  ->leftJoinSub($latestMainStatus, 'latest_main_statuses', function($join) {
                $join->on('shipments.id', 'latest_main_statuses.shipment_id');
            })
            ->leftJoin('main_statuses', 'latest_main_statuses.main_status_id', 'main_statuses.id')

But it turns out that the subQuery is not returning the row that corresponds to the max date, meaning i get the first "main_status_id" for the given shipment and not the one i need. This leads to me believe that i need to do a nested query where in the deepest level i select the max created_at and then somehow join the other tables as well, but I can't figure it out.

Could someone point me to the right direction

bugsysha

6 years ago

Level 61

Why not join first and then just group by and select the data that you need?

Do you have relationships in your models?

DPlachkov

6 years ago

Level 25

SELECT shipments.id, main_status_shipment .*, main_statuses.* FROM `shipments` left join main_status_shipment on shipments.id = main_status_shipment .shipment_id and main_status_shipment .created_at = (SELECT MAX(created_at) from main_status_shipment where main_status_shipment .shipment_id = shipments.id ) left join main_statuses on main_statuses.id = main_status_shipment .main_status_id

This is how i figured out the query should be. The only thing I'm not sure about is if i should put the "on main_status_shipment .created_at = ...()" in a where clause or should it stay in the ON. And also how to execute the subquery in the "on" statement using laravel query builder

DPlachkov

6 years ago

Best Answer

Level 25

This is what i ended up doing. Now I don't know if there is a better way to write the subquery in the second part of the ON clause.

$mainQuery = Capsule::table('shipments')
    ->leftJoin('main_status_shipment ', function($join) {
        $join->on('shipments.id', 'main_status_shipment .shipment_id')
            ->on('main_status_shipment .created_at', Capsule::raw('(SELECT MAX(mss.created_at) from main_status_shipment as mss where mss.shipment_id = shipments.id)'));
    })
    ->leftJoin('main_statuses', 'main_statuses.id', 'main_status_shipment .main_status_id');

bugsysha

6 years ago

Level 61

Something like this should work!

Shipment::with('statuses')->get();

Then in your Shipment model you can have a relationship

public function statuses(): HasMany
{
    return $this->hasMany(ShipmentStatus::class);
}

Use LazyCollections to avoid memory issues.

Please or to participate in this conversation.