Rails Globalize sort objects by translated column with fallbacks and will_paginate

Question

I have some models that I want to display in a paginated list (with will_paginate) sorted by a column translated with Globalize.

The requirements say that it has to be possible to create objects in any language and if no translation for the current locale are available, to fall back to any other in a predefined order.

The problem I'm running into is that if I join the table on the translation table, and the translations are mixed, will_paginate fails because even with a distinct call, the #count is calculated wrong and #limit on the AR Relation doesn't work as expected.

For example:

I have an Exhibitor model with a corresponding Exhibitor::Translation from Globalize and the Exhibitor objects can have translations in any or all configured locales.

Exhibitor.with_translations.order(:sort_string).limit(2) 

returns just one object, because the first Exhibitor object has 2 translations and even a #distinct call doesn't change this, which means will_paginate is getting confused and

I think what I want is something like this:

SELECT exhibitors.id,translations.sort_string
FROM exhibitors
INNER JOIN
exhibitor_translations translations ON translations.exhibitor_id = 
exhibitors.id
WHERE translations.locale = 'en' OR translations.locale = 'de' OR 
translations.locale = 'fr'
ORDER BY translations.sort_string;

The WHERE part is where I'm struggling, because I just want the first translation which exists, but here I'm getting back all available for each object.

I hope this is a somewhat understandable explanation, still trying to exactly formulate it in my head, so if any clarification is needed, please just ask.

I tried a couple variations of this solution here Globalize3 order records by translated attributes and taking fallbacks into consideration

but will_paginate still shows the wrong count and doesn't display the navigation links.

Answer 1

If anyone runs into the same problem, I finally solved it with a subquery like this:

SELECT  DISTINCT
    COALESCE(b.exhibitor_id, c.exhibitor_id, a.exhibitor_id) exhibitor_id,
    COALESCE(b.sort_string, c.sort_string, a.sort_string) sort_string

FROM exhibitor_translations a
   LEFT JOIN
    (
        SELECT ID, exhibitor_id, sort_string, locale
        FROM exhibitor_translations
        WHERE locale = 'fr'
     ) b ON a.exhibitor_id = b.exhibitor_id
   LEFT JOIN
    (
        SELECT ID, exhibitor_id, sort_string, locale
        FROM exhibitor_translations
        WHERE locale = 'en'
     ) c ON a.exhibitor_id = c.exhibitor_id; 

The Rails code looks like this (not the final production code but close enough):

    entity            = Exhibitor
    query             = entity.all
    translation_table = entity.translations_table_name
    foreign_key       = "#{entity.name.demodulize.underscore}_id"
    attribute_name    = "sort_string"

    subquery = entity.translation_class.select("
      DISTINCT
      COALESCE(b.id, a.id) id,
      COALESCE(b.#{foreign_key}, a.#{foreign_key}) #{foreign_key},
      COALESCE(b.#{attribute_name}, a.#{attribute_name}) #{attribute_name}
    ")
    subquery.from("#{translation_table} AS a")
    subquery = subquery.joins("
      LEFT JOIN
      (
        SELECT id, #{foreign_key}, #{attribute_name}
        FROM #{translation_table}
        WHERE locale = '#{I18n.locale}'
      ) b ON a.id <> b.id AND a.#{foreign_key} = b.#{foreign_key}
    ")

    query = query.joins("INNER JOIN (#{subquery.to_sql}) t ON #{entity.table_name}.id = t.#{foreign_key}")

    query.order("t.#{attribute_name} ASC")

If anyone wants to use this, be mindful of SQL Injection possibilties and use ActiveRecord#quote for external values (I don't have any user inputs for this query at the moment)

For > 2 locales use multiple join clauses like in the raw sql query above.