Rails left outer join with row including NULL

Question

I need to do a complex outer join. I wanna have a row in the result for the service interval if there is no service_note that's connected to it. As you see from the query service_interval belongs to one service_job which is connected to service_notes through the joint table service_note_jobs. At the moment it returns an empty relation if there is no service_note connected to that service_interval.

def self.distance_and_time_until_next_service(service_interval_ids)
  ServiceInterval
    .left_joins(vehicle: { service_notes: :service_note_jobs })
    .where(
      'service_note_jobs.service_job_id = service_intervals.service_job_id AND '\
      'service_intervals.vehicle_id = service_notes.vehicle_id'
    )
    .where(service_intervals: { id: service_interval_ids })
    .group('service_intervals.id')
    .select(
      'service_intervals.id',
      "MAX(GREATEST(service_notes.odometer, service_intervals.created_odometer) - vehicles.odometer + service_intervals.distance_interval) AS distance_left_until_next_service",
      "MAX(ROUND(EXTRACT(epoch FROM(GREATEST(service_notes.date, service_intervals.created_at) - NOW())) / 86400  + service_intervals.time_interval)) AS time_left_until_next_service"
    )
end

Possible duplicate of [LEFT OUTER JOIN in Rails 4](https://stackoverflow.com/questions/24358805/left-outer-join-in-rails-4) — Sebastián Palma, Oct 11 '19 at 05:19

score 2 · Answer 1 · answered Oct 11 '19 at 02:11

2

Try to add this after your left_joins:

.where(vehicle: { service_notes: { id: nil } })

Or more general sample

User.left_joins(:posts).where(posts: { id: nil })

This will give you users with no posts.

answered Oct 11 '19 at 02:11

Serhii Nadolynskyi

5,473
3
21
20

Wouldn't this filter out the ones with service notes? – Sean Magyar Oct 17 '19 at 19:07
Yes, you are right. It will filter out records with service_notes. You will get only ones without them. – Serhii Nadolynskyi Oct 18 '19 at 20:05
Yep, that's the problem, it should also keep the ones without service notes. I just realized that COALESCE might work. – Sean Magyar Oct 18 '19 at 21:09

Rails left outer join with row including NULL

1 Answers1