Howto use ActiveRecord preload with plain SQL inner joins

Like you know from "How to tell ActiveRecord how to preload associations (either JOINs or separate queries)", you can tell ActiveRecord explicitly if it should use a LEFT OUTER JOIN or a separate query to preload associations.

Here is some special case, where a simple includes is not possible. For the diagram below we want to sort the rides by the translated name of the departure_place and the arrival_place:

Here is the list of features we want to achieve:

• Paginated (using will_paginate Show archive.org snapshot )
• Ordered by translated name of departure_place and arrival_place
• No N+1 queries

This is normally straightforward, but as we are referencing the table we want to order on twice some adjustments need to be made.

Ride
.joins('inner join places as departure_places on ride.departure_place_id = departure_places.id')
.joins('inner join places as arrival_places on ride.arrival_place_id = arrival_places.id')
.joins('inner join place_translations as departure_place_translations on departure_places.id = departure_place_translations.place_id AND departure_place_translations.locale = "en"')
.joins('inner join place_translations as arrival_place_translations on arrival_places.id = arrival_place_translations.place_id AND arrival_place_translations.locale = "en"')
.order('departure_place_translations.name, arrival_place_translations.name')
.where(ride_id: 1)
.paginate(page: 1, per_page: 20)
.preload(
  departure_place: :translations,
  arrival_place: :translations
)
.to_a

What we do here:

• First make some custom SQL with INNER JOINs where our order constraint can be fulfilled (Power of plain SQL: Using eager_load here is not possible or very painful)
• Then preload the result set (Power of ActiveRecord: If you don't use ActiveRecord preload you need to manually take care of pagination and ordering after fetching the records by ids)