0

I have the below tables

vehicles - id, name

vehicle_settings - id, settings, vehicle_id, company_id, updated_at, updated_by

vehicle_licenses - id, license_number, vehicle_id, company_id, updated_at, updated_by

users - id, name, email

One vehicle can have multiple entries in vehicle_settings and multiple entries in vehicle_licenses It is possible that the vehicle has no vehicle_settings or vehicle licenses.

Now, I need the list of all vehicles with the user who has last updated the vehicle setting or license. I should also be able to filter the list with vehicle id and be able to the count of all vehicles that match the filtered condition.

So, I tried the below query

SELECT DISTINCT ON (v.id) v.id,
(
SELECT
    JSON_BUILD_OBJECT(
      'id', u.id, 
      'name', u.name,
      'email', u.email
    )
FROM users u WHERE u.id=updates.updated_by  AND updates.updated_at = MAX(updates.updated_at) 
) AS updated_by,
MAX(updates.updated_at) AS updated_at,
COUNT(*) over () 
FROM vehicles v 
LEFT JOIN  LATERAL(
select foo.updated_by,foo.updated_at FROM (
select vs.updated_by,vs.updated_at from vehicle_settings vs WHERE vs.vehicle_id = v.id 
 UNION         
select vl.updated_by,vl.updated_at from vehicle_licenses vl WHERE vl.vehicle_id = v.id
) AS foo order by updated_at desc  LIMIT 1    
) AS updates ON TRUE
WHERE v.id 'c4ced8df-c93f-45e8-806f-f7d5f20d9d75'
GROUP BY v.id, updates.updated_by,updates.updated_at ;

This query works fairly well, however, the count is misleading. The number of count I get is more than the number of vehicles. Maybe it takes entries from LEFT LATERAL JOIN also I guess.

Surya
  • 2,429
  • 1
  • 21
  • 42

1 Answers1

1
SELECT v.*, u.*,
       COUNT(*) FILTER (WHERE v.id = 'c4ced8df-c93f-45e8-806f-f7d5f20d9d75') OVER ()
FROM vehicles AS v
   LEFT JOIN (SELECT DISTINCT ON (vehicle_id)
                vehicle_id, updated_by
         FROM (SELECT vehicle_id, updated_by, updated_at
               FROM vehicle_settings
               UNION ALL
               SELECT vehicle_id, updated_by, updated_at
               FROM vehicle_licenses) AS x
         ORDER BY vehicle_id, updated_at DESC) AS vu
      ON v.id = vu.vehicle_id
   LEFT JOIN users AS u
      ON vu.updated_by = u.id;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263