Rails - Complicated sql query with query variables as scope

One way to do this is by two lateral subqueries:

class Game < ApplicationRecord
  has_many :details

  # Gets games with a localized description
  # @param [String] preferred - the preferred locale for the description
  # @param [String] fallback  - the fallback locale for the description
  # @return [ActiveRecord::Relation]
  def self.with_localized_description(preferred: 'es-ES', fallback: 'en-GB')
    # This is the subquery we want to run for each row on the games table
    subquery = Detail.select(:description)
                     .where(Detail.arel_table[:game_id].eq(arel_table[:id]))
                     .limit(1)
    select(
      arel_table[Arel.star],
      # Feel free to use whatever alias you want
      'COALESCE(preferred_locale.description, fallback_locale.description) AS description'
    )
      .outer_lateral_joins(subquery.where(locale: preferred), name: 'preferred_locale')
      .outer_lateral_joins(subquery.where(locale: fallback), name: 'fallback_locale')
  end

  # @param [Arel::SelectCore]  - subquery  
  # @param [String] name       - the alias used for this table
  # @return [ActiveRecord::Relation]
  def self.outer_lateral_joins(subquery, name: '')
     # Rails doesn't have a decent way to write LEFT OUTER JOIN LATERAL - this is kind of hacky but works
    joins("LEFT OUTER JOIN LATERAL (#{subquery.to_sql}) #{name} ON true")
  end
end

The easiest way to describe this is that its like a SQL foreach loop. Each lateral join is telling Postgres to get a single row from the details table for each row on the games table.

Since this is a lateral join you can use preferred_locale and fallback_locale in the group, order or whatever clause you want just like if you were dealing with a column on the games table. Postgres also lets you use selected columns in the order clause.

For example:

Game.with_localized_description.order('fallback_locale.description')
Game.with_localized_description.order(:description)

ActiveRecord also makes any aliased columns in the select available as attributes on the records:

irb(main):047:0> mario = Game.with_localized_description.find_by(name: 'Mario Cart')
  Game Load (0.5ms)  SELECT "games".*, COALESCE(preferred_locale.description, fallback_locale.description) AS description FROM "games" LEFT OUTER JOIN LATERAL (SELECT * FROM "details" WHERE "details"."game_id" = "games"."id" AND "details"."locale" = 'es-ES' LIMIT 1) preferred_locale ON true LEFT OUTER JOIN LATERAL (SELECT * FROM "details" WHERE "details"."game_id" = "games"."id" AND "details"."locale" = 'en-GB' LIMIT 1) fallback_locale ON true WHERE "games"."name" = $1 LIMIT $2  [["name", "Mario Cart"], ["LIMIT", 1]]
=>
#<Game:0x00007fec80133700
...
irb(main):048:0> mario.description
=> "Un buen juego de carreras." 
irb(main):049:0> Game.with_localized_description.order(:description)
  Game Load (0.9ms)  SELECT "games".*, COALESCE(preferred_locale.description, fallback_locale.description) AS description FROM "games" LEFT OUTER JOIN LATERAL (SELECT * FROM "details" WHERE "details"."game_id" = "games"."id" AND "details"."locale" = 'es-ES' LIMIT 1) preferred_locale ON true LEFT OUTER JOIN LATERAL (SELECT * FROM "details" WHERE "details"."game_id" = "games"."id" AND "details"."locale" = 'en-GB' LIMIT 1) fallback_locale ON true ORDER BY "description" ASC
=>
[#<Game:0x000056162a59a3d0
  id: 1,
  name: "Mario Cart",
  created_at: Wed, 23 Mar 2022 12:11:41.658592000 UTC +00:00,
  updated_at: Wed, 23 Mar 2022 12:11:41.658592000 UTC +00:00>]

This method isn't written as a "scope" since the scope method is just syntactic sugar for writing class methods. Jamming this code into a lambda would just ruin the readibility of the code.