How to link Arel queries with ActiveRecord methods?

Question

I am discovering Arel, and a bit confused about how to come back to ActiveRecord objects as used by default in my RoR 5.2 application.

Initially, my Business Rules index data set was defined as:

@business_rules = BusinessRule.pgnd(current_playground_scope). 
search(params[:criteria]).order("hierarchy ASC").
paginate(page: params[:page], :per_page => paginate_lines)

Now, Name and Description columns are provided by a Translations table, which make queries a bit more complex. That's where Arel comes in:

names = Translation.arel_table.alias('tr_names')
descriptions = Translation.arel_table.alias('tr_descriptions')
rules = BusinessRule.arel_table
translated_rules = rules.
join(names, Arel::Nodes::OuterJoin).on(rules[:id].eq(names[:document_id]).and(names[:language].eq(user_language).and(names[:field_name].eq('name')))).
join(descriptions, Arel::Nodes::OuterJoin).on(rules[:id].eq(descriptions[:document_id]).and(descriptions[:language].eq(user_language).and(descriptions[:field_name].eq('description'))))

rules_extract = translated_rules.project(Arel.star)
sql = rules_extract.to_sql
@rules_index = ActiveRecord::Base.connection.execute(sql)

#suggestions for better organising Arel's tree are welcome

The to_sql method provides a satisfying SQL query, but the result of the execute method returns a PG::Result class where I am expecting an ActiveRecord_Relation.

Reading around I found a lot about Arel's features, but I still miss the link to put it back at work in my index view.

Thanks for helping!

Answer 1

Actually, Arel provides chunks of query to standard ActiveRecord query interface. This allows me to rewrite the original Rails index query and add the aliased linked table twice:

def index
  names = Translation.arel_table.alias('tr_names')
  descriptions = Translation.arel_table.alias('tr_descriptions')
  rules = BusinessRule.arel_table
  translated_rules = rules.
    join(names, Arel::Nodes::OuterJoin).on(rules[:id].eq(names[:document_id]).and(names[:language].eq(user_language).and(names[:field_name].eq('name')))).
    join(descriptions, Arel::Nodes::OuterJoin).on(rules[:id].eq(descriptions[:document_id]).and(descriptions[:language].eq(user_language).and(descriptions[:field_name].eq('description')))).
    join_sources

  @business_rules = BusinessRule.joins(translated_rules).order("hierarchy ASC, major_version, minor_version").paginate(page: params[:page], :per_page => paginate_lines)

  respond_to do |format|
    format.html # index.html.erb
    format.json { render json: @business_rules }
  end
end

Now I am ready for smart refactoring of my queries, and to retrieve the translated fields from Translations table!