CODEWITHSUNDEEP
ruby-on-railsactiverecord
Fran
Fran

Reputation: 1073

How to order a query by a translated field using globalize

I'm trying to order a query using a field which is translated with globalize2. The problem is that since stored in database and in an association I'm having lot of problems.

I've something like that

class Category < ActiveRecord::Base
  validates_presence_of :name
  validates_uniqueness_of :name
  has_many :products, :dependent => :destroy

  translates :name
end

The question is, how do I order by the translated name?

Upvotes: 16

Views: 4495

Answers (3)

rebagliatte
rebagliatte

Reputation: 2146

The with_translations method seems to be way to go:

Category.with_translations(I18n.locale).order('category_translations.name')

Also, if you are using PostgreSQL you might want to add case insensitive order to that:

Category.with_translations(I18n.locale).order("LOWER(category_translations.name) ASC")

More about this here: https://github.com/globalize/globalize#scoping-objects-by-those-with-translations

Upvotes: 13

edgerunner
edgerunner

Reputation: 14973

I'm assuming that any model called Category would have at most hundreds of records if not less. Maybe you can consider sorting the results in memory, after fetching them.

@categories = Category.all # or whatever else to retrieve what you want
@categories.sort! { |a,b| a.name <=> b.name }

Beware though. This would become a bad idea if the categories table contains more than thousands of records.

Upvotes: 1

aceofspades
aceofspades

Reputation: 7586

I tested this using sqlite3, and it works.

class Category < ActiveRecord::Base
  ...
  named_scope :ordered, lambda {|locale|
    {
      #:select => "categories.*, categories.name sort_name",
      # For MySQL
      #:select => "categories.*, IF(category_translations.name IS NULL, categories.name, category_translations.name) sort_name",
      # For sqlite3
      :select => "categories.*, (CASE WHEN category_translations.name IS NULL THEN categories.name ELSE category_translations.name END) sort_name",
      :joins => ActiveRecord::Base.sanitize_sql_array([
        "LEFT JOIN category_translations on category_translations.category_id = categories.id AND category_translations.locale = ?", locale]),
      :order => "sort_name"
    }
  }
  ...
end

Category.ordered(some_locale).all # Returns all records, sorted by translated name

Upvotes: 1

Related Questions