CODEWITHSUNDEEP
laravellaravel-query-builder
Amine
Amine

Reputation: 100

Join first row of the latest rows from child table

I have 2 tables bookings and port_movements

bookings hasMany port_movements

bookings columns: id,closed
port_movements columns: id,booking_id,date

How to return bookings list where closed = false and join only 1 latest row from port movements on bookings.id = port_movements.booking_id to avoid duplications

Here is my code

$query
->where('closed', false)
->join('port_movements as pm', 'bookings.id', '=', 'pm.booking_id')
->where(function ($q) {
       $q->where('pm.type', 'berthing')
         ->orWhere('pm.type', 'shifting');
})
->join('vessels', 'bookings.vessel_id', '=', 'vessels.id')
->join('berths', 'lm.berth_id', '=', 'berths.id')
->whereIn('berths.id', $berths);

Upvotes: 1

Views: 689

Answers (2)

Ersoy
Ersoy

Reputation: 9586

The query will be like this;

SELECT *
FROM bookings
         join port_movements as pm on pm.booking_id = bookings.id and
                                          pm.date = (SELECT MAX(date)
                                                         FROM port_movements
                                                         WHERE port_movements.booking_id = bookings.id)
where closed = false;

Query builder;

return DB::table('bookings')
            ->join('port_movements as pm', function ($join) {
                $join->on('pm.booking_id', '=', 'bookings.id');
                $join->on('pm.date', '=', DB::raw('(SELECT MAX(date) FROM port_movements WHERE port_movements.booking_id = bookings.id)'));
            })
            ->where('closed', false)
            ->get();

Sub-query part is not my best, probably there is a better way to do it.

Upvotes: 2

marcothesane
marcothesane

Reputation: 6721

I noted you have the SQL tag, so I'll answer with an SQL solution.

If you don't want that, then specify laravel and associates only ...

The query would be:

WITH newest_port_movements AS (
  SELECT
    booking_id
  , MAX("date") AS max_dt -- it is a reserved word, put in quotes
  FROM port_movements
  WHERE NOT closed
  GROUP BY booking_id
)
SELECT
  b.*
, pm.*
FROM bookings b
JOIN newest_port_movements npm
  ON b.id = npm.booking_id
 AND NOT b.closed
JOIN port_movements pm
  ON npm.booking_id = pm.booking_id
 AND npm.max_dt     = pm."date"
 AND NOT pm.closed
;

Upvotes: 1

Related Questions