This question is based on an issue posted to the Mobility GitHub project.
I'm working with an articles table that supports multiple article types (e.g., blog post, case study, knowledge base article). This table includes a column to track the number of times an article is viewed—an integer column that increments every time a show action is called for an article.
In implementing translations for these articles, I want to track the number of views for each translation individually, not for the main article object. In an effort to achieve this, I included the views property as one of the translated properties on my object:
class Article < ApplicationRecord
include Taggable
include PgSearch
translates :title, type: :string
translates :subtitle, type: :text
translates :body, type: :text
translates :views, type: :integer
multisearchable :against => [:title, :subtitle, :body]
has_and_belongs_to_many :products
attachment :hero_image, content_type: %w(image/jpeg image/png image/gif)
validates :title, :body, :posted_on, presence: true
scope :current, -> { where 'posted_on < ?', Date.tomorrow }
scope :news_articles, -> { where type: ['BlogPost', 'CaseStudy'] }
def log_view(by = 1)
self.views ||= 0
self.views += by
self.save(touch: false)
end
def to_param
"#{id} #{title}".parameterize
end
def published?
posted_on < Date.tomorrow
end
end
In my controller, I want to list the top ten most viewed articles, which I get with this query:
@top_articles = Article.current.news_articles.order(views: :desc, posted_on: :desc).limit(10)
I expect to receive an array of articles, as I did before implementing Mobility.
What I get instead is #<Article::ActiveRecord_Relation:0x233686c>. If I then try to convert that to an array with @top_articles.to_a, I get this error:
Article Load (0.7ms) SELECT "articles".* FROM "articles" WHERE (posted_on < '2018-02-11') AND "articles"."type" IN ('BlogPost', 'CaseStudy') ORDER BY "articles"."views" DESC, "articles"."posted_on" DESC LIMIT $1 [["LIMIT", 10]]
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column articles.views does not exist
LINE 1: ...les"."type" IN ('BlogPost', 'CaseStudy') ORDER BY "articles"...
^
: SELECT "articles".* FROM "articles" WHERE (posted_on < '2018-02-11') AND "articles"."type" IN ('BlogPost', 'CaseStudy') ORDER BY "articles"."views" DESC, "articles"."posted_on" DESC LIMIT $1
from /usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'
Changing the query to include i18n:
@top_articles = Article.i18n.current.news_articles.order(views: :desc, posted_on: :desc).limit(10)
… returns #<#<Mobility::Backends::ActiveRecord::Table::QueryMethods:0x00000000050a86d8>:0x286c3e0>, and when I try to convert that to an array, I get the same thing:
Article Load (0.7ms) SELECT "articles".* FROM "articles" WHERE (posted_on < '2018-02-11') AND "articles"."type" IN ('BlogPost', 'CaseStudy') ORDER BY "articles"."views" DESC, "articles"."posted_on" DESC LIMIT $1 [["LIMIT", 10]]
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column articles.views does not exist
LINE 1: ...les"."type" IN ('BlogPost', 'CaseStudy') ORDER BY "articles"...
^
: SELECT "articles".* FROM "articles" WHERE (posted_on < '2018-02-11') AND "articles"."type" IN ('BlogPost', 'CaseStudy') ORDER BY "articles"."views" DESC, "articles"."posted_on" DESC LIMIT $1
from /usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'
It turns out that while Mobility supports the use of translated fields in the where clause, it does not currently support them in the order clause of an Active Record query.
order clauseBased on feedback from the gem author, I tried the query:
Article.i18n.current.news_articles.order('article_translations.views desc', 'articles.posted_on desc')
… which returns a #<#<Mobility::Backends::ActiveRecord::Table::QueryMethods>> object, and to_a returns this error:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "article_translations"
LINE 1: ...les"."type" IN ('BlogPost', 'CaseStudy') ORDER BY article_tr...
^
: SELECT "articles".* FROM "articles" WHERE (posted_on < '2018-02-12') AND "articles"."type" IN ('BlogPost', 'CaseStudy') ORDER BY article_translations.views desc, articles.posted_on desc LIMIT $1
from /usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'
joins or includes clause for the translation tableArticle.i18n.joins(:article_translations).order('article_translations.views desc', 'articles.posted_on desc').limit(10)
This query, again, returns a #<#<Mobility::Backends::ActiveRecord::Table::QueryMethods>> object, and to_a results in:
ActiveRecord::ConfigurationError: Can't join 'Article' to association named 'article_translations'; perhaps you misspelled it?
from /usr/local/bundle/gems/activerecord-5.0.6/lib/active_record/associations/join_dependency.rb:231:in `find_reflection'
has_many :article_translations to modelAdding a relation to the model throws back this error:
uninitialized constant Article::ArticleTranslation
What should I try next?
UPDATE
Ordering by translated attributes is now supported as of version 0.8.0/
Just do this:
Article.i18n.current.news_articles.
order(:views => :desc, :'articles.posted_on' => :desc)
and Mobility will handle everything (you don't need to join the translation table, etc.)
ORIGINAL ANSWER
You were right that you need to join the translations table, but the association is named translations, not article_translations.
In any case, there is a method join_translations that joins the translation table, so this should work:
Article.i18n.
current.
news_articles.
join_translations.
order('article_translations.views desc', 'articles.posted_on desc')