Reading in the product language table

While loading the list of products on Jzool.com I naturally wanted to load the associated translation which is stored in a different table.

The best way to do this to use eager loading which is a cool thingie you do when you want to read associated tables in one go.

So, in the Product model I do this:

 has_many :product_languages, :dependent => :destroy
 has_one :t, :class_name => 'ProductLanguage',
   :conditions => ['locale = ?', I18n.locale],
   :select => 'id, product_id, name, short'

The "t" stands for "translated". Just wanted to keep it short. I could very well do has_many :ts by the way.

Now, in my paginated search method I use the include.

 def self.search(search, page)
   paginate :include => :t, :per_page => 20, :page => page,
            :conditions => ['name like ?', "%#{search}%"], :order => 'created_at desc'
 end

And then I can just call the translated name by doing:

<%= @product.t.name %>

However, this actually ends up generating two sql statements. Here's an example using two models - Category and CategoryLanguage where the latter holds the localized name of the category.

def localized_children
Category.find :all, :conditions => ['categories.parent_id = ? and categories.display = ?', self.id, true],
   :order => 'categories.sort_id asc',
   :select => 'categories.id, categories.name, categories.parent_id', :include => :t
end

This results in the following two sql statements

[4;35;1mCategory Load (1.0ms) [0m   
[0mSELECT categories.id, categories.name, categories.parent_id FROM `categories` WHERE (categories.parent_id = 307 and categories.display = 1) ORDER BY categories.sort_id asc [0m
[4;36;1mCategoryLanguage Load (1.0ms) [0m    [0;1mSELECT id, category_id, name FROM `category_languages` WHERE (`category_languages`.category_id IN (308,313,480,310,309,311,504,312) AND (locale = 'en'))

So it seems like the best way to go is simple sql.

def localized_children
   Category.find_by_sql(["
     SELECT DISTINCT c.id, c.parent_id, cl.name
     FROM categories AS c JOIN category_languages AS cl ON c.id = cl.category_id
     WHERE c.display = ? AND c.parent_id = ? AND cl.locale = ?
     ORDER BY c.sort_id ASC",
     true, self.id, I18n.locale])
end

Which results in one clean sql

SELECT DISTINCT c.id, c.parent_id, cl.name
FROM categories AS c JOIN category_languages AS cl ON c.id = cl.category_id
WHERE c.display = 1 AND c.parent_id = 307 AND cl.locale = 'en'
ORDER BY c.sort_id ASC

But in the view Use <%= @product.name %> instead of <%= @product.t.name %>