Eliminate n+1 Calls When Using the Mobility Gem To Retrievie All Records Of a Model

The Mobility Gem is a a gem for storing and retrieving translations as attributes on a class. Instead of requiring multiple migrations for every attribute that needed to be translated, Mobility uses only two shared tables to store all translated attributes (This is my understanding of it, if I am wrong, please correct me.)

While Mobility provides a handy method for querying such that only 1 sql call is made to find the corresponding record with the queried translated attribute. A problem exists when I tried to retrieve all records of a class and display each of their translated values - doing so, would cause n+1 calls to the database, each record makes a separate call to retrieve the translated value of the attribute.

I thought of 2 ways to solve this:
  1.  Caching, all the translated attributes are cached after it has been called once
  2.  A complex sql call to eliminate n+1 calls
This post will focus on the second method.

----
I've written a helper method to retrieve either all records or a subset of records defined by an array of the subset ids:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
    # helper method for Mobility gem, to retrieve locale names for a class attribute, e.g. locale_name("Interest", "name")
    # when no 'arr' param is set, retrieve for all
    # when 'arr' is set, retrieves only data included in arr; 'arr' is an array of ids, e.g. [1,2,3]
    # Mobility can be used on both string type data and text type data, by default, this method assumes the data is of string type
    # 'locale' sets the locale of the names retrieved, defaults to locale of the current page
    def locale_name(class_name, attribute, arr = nil, type = "string", locale = I18n.locale)
      table_name = class_name.underscore.pluralize(2)
      where_clause = (arr.nil?) ? "": " WHERE #{table_name}.id IN (#{arr.join(",")})"

      sql = "SELECT DISTINCT #{table_name}.id, #{attribute}_mobility_#{type}_translations.value AS #{attribute} "\
            "FROM #{table_name} "\
            "INNER JOIN mobility_#{type}_translations #{attribute}_mobility_#{type}_translations "\
            "ON #{attribute}_mobility_#{type}_translations.key = '#{attribute}' "\
            "AND #{attribute}_mobility_#{type}_translations.locale = '#{locale}' "\
            "AND #{attribute}_mobility_#{type}_translations.translatable_type = '#{class_name}' "\
            "AND #{attribute}_mobility_#{type}_translations.translatable_id = #{table_name}.id" 
            + where_clause + 
            " ORDER BY #{table_name}.id"
      arr = ActiveRecord::Base.connection.execute(sql)
    end

For example, if I want to retrieve the translated attribute called 'name' on all the records of the model 'Interest', I can now use the helper method like so:
1
 @interests = locale_name('Interest', 'name')

This returns an array, with each element of the array a hash containing the keys: :id & :name, where :name is the translated attribute (of the current locale), arranged in the order of their id

If I want to only retrieve records for a subset, say, for records with ids: [1,2,3]:

1
 @interests = locale_name('Interest', 'name', [1,2,3])

This will return an array of only 3 elements in the order of their id

Of course, the helper method I provided above is not comprehensive. For example, it only allow one to retrieve a single attribute at a time, and I have not provided the option to order the records other than in the order of their ids. Moreover, it does not return the fallback value if the record does not have a translated attribute of the current locale. So, feel free to expand on it if you find it helpful, and provide your solution to me!

Extra:
A good read on the different strategies for storing and retrieving translated attributes:
https://dejimata.com/2017/3/3/translating-with-mobility

Comments

Post a Comment

Popular posts from this blog

I18n Country & City Select Fields - Reconstructing Carmen-Rails from Scratch

Image
One would think that Country/City selection fields are something so common that someone must have created a simple method to implement it. That may true to a point, but it turns out the City-State gem only provides English names, while the Carmen-Rails gem   - despite having I18n support - due to its dependence on the Carmen gem(https://github.com/carmen-ruby/carmen) - is no longer actively maintained. So, there's a few ways to get a working I18n Country/City select field working. The simplest might be to unpack the Carmen-Rails gem, change it to support Rails 5 with this fix , then install it by telling the Gemfile to install from local file. I did that initially,  but that didn't feel exactly right. So, I decided to reinvent the wheel and write the code from scratch. Now there are other gems that return country/subregions information with I18n support, such as the Countries gem and the Cities gem . However, for the purpose of making select fields, I found these gems retu
Read more

(Re: Pagination, You're doing it wrong) - Fixing Duplicate/Missing Records in Infinite Scrolling + Pagination by Hacking the Kaminari Gem

Using pagination with JS to create an infinite scrolling page is common, (Check out this RailsCast episode if you don't know how: http://railscasts.com/episodes/114-endless-page-revised?view=comments )  But it gives rise to the problem of loading duplicate or missing records.  (See: https://coderwall.com/p/lkcaag/pagination-you-re-probably-doing-it-wrong) So, how do you solve this? --- I solved the problem by creating a CustomRelation model that builds on Kaminari's paginate helper. In this example, I am creating an infinite scrolling page of 'posts', akin to Facebook. With the Kaminari gem, its 'paginate' helper accepts ActiveRecord::Relation , which should respond to these 3 methods (this, I refer to the  Kir Shatrov's post, see bottom): current_page - returns current page number total_pages - returns total number of pages all - returns an array of rows for the current page So, instead of using Kaminari's default Relation, I cre
Read more

Sending an Email Confirmation Link with the Right Locale (with Devise)

(This post will be short.) I have followed Railscast to configure I18n for my app, but I soon realized the link included inside the confirmation emails (sent through Devise) would direct the use to the default locale/language (i.e. English) instead of the locale that the user is using. This is my fix: In the mailer template (in devise, it would be devise/mailer/confirmation_instructions.html.erb), just pass in the user locale in the rails url helper: 1 2 3 4 5 <% user_name = ( @user . full_name . empty?) ? @user . email : @user . full_name %> <p> <%= t( '.greeting' , recipient : user_name) %> </p> <p> <%= t( '.instruction' ) %> </p> <p> <%= link_to t( '.action' ), confirmation_url( @resource , confirmation_token: @token , locale : I18n . locale) %> </p> Done.
Read more