I need help with a scope in Rails.

I have the following postgresql query:

select
  name,
  coalesce(esp.description, eng.description) Description
from games g
left join 
  (select game_id, description from details where locale = 'en-GB') eng 
  on g.id = eng.game_id
left join
  (select game_id, description from details where locale = 'es-ES')
  esp on g.id = esp.game_id

What it does basically is getting the names (from table 'games') and the description (from another table called 'details') of a game, if the description is not available in spanish, it takes it in english.

I leave here both tables:

You can test the query I mentioned before here with two premade games (one has spanish translation and the other doesn't): https://dbfiddle.uk/?rdbms=postgres_13&fiddle=381eb932e859dcfa9105d5d79a2c9c63

I would like to get the same result as a scope when retrieving the games in rails.

When I try:

scope :filter_by_description,
      -> {
        order("description, coalesce(currnt.locale, fallbck.locale) Locale FROM custom_apps c
        LEFT JOIN (SELECT * FROM custom_app_details WHERE locale = 'de-DE') currnt on c.id = currnt.custom_app_id
        LEFT JOIN (SELECT * FROM custom_app_details WHERE locale = 'en-GB') fallbck on c.id = fallbck.custom_app_id")
      }

I just get the following error:

ActiveRecord::UnknownAttributeReference at /games
Query method called with non-attribute argument(s): "order("description, coalesce(currnt.locale, fallbck.locale) Locale FROM custom_apps c LEFT JOIN (SELECT * FROM custom_app_details WHERE locale = 'de-DE') currnt on c.id = currnt.custom_app_id LEFT JOIN (SELECT * FROM custom_app_details WHERE locale = 'en-GB') fallbck on c.id = fallbck.custom_app_id")"

CodePudding user response：

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.