Order by count of model association

Question

I have a model Event, which depends on Execution, which depends on Product who have a ProductTanslation. This same Event model have many orders. I would like to display the events in a table and be able to sort them by count(orders), search by name ... I made the sql request

select e.id, pt.name, nbc.nb
from `events` as e
inner join `executions` as ex on e.`execution_id` = ex.`id`
inner join `products` as p on ex.`product_id` = p.`id`
left outer join (select product_id, `name` from `product_translations` where `locale` ='en' group by `product_id`  ) as pt on pt.`product_id` = p.id
left outer join (select event_id, COUNT(*) as nb from orders group by event_id) as nbc on nbc.event_id = e.id
where pt.name like '%plane%'
order by 3 desc

But I can't transcribe it with ActiveReccord specially the order by count(orders)

By the way I use the gem globalize for my translations.

Answer 1

A method to translate a SQL query to Rails :

Initial Request

select e.id, pt.name, nbc.nb
from `events` as e
inner join `executions` as ex on e.`execution_id` = ex.`id`
inner join `products` as p on ex.`product_id` = p.`id`
left outer join (select product_id, `name` from `product_translations` where `locale` ='en' group by `product_id`  ) as pt on pt.`product_id` = p.id
left outer join (select event_id, COUNT(*) as nb from orders group by event_id) as nbc on nbc.event_id = e.id
where pt.name like '%plane%'
order by 3 desc

First step Identify the main table; here, it's Event. So it's a call from your Event model.

Event
  .select("events.id, pt.name, nbc.nb")
  .joins("inner join `executions` as ex on events.`execution_id` = ex.`id`
          inner join `products` as p on ex.`product_id` = p.`id`
          left outer join (select product_id, `name` from `product_translations` where `locale` ='en' group by `product_id`  ) as pt on pt.`product_id` = p.id
          left outer join (select event_id, COUNT(*) as nb from orders group by event_id) as nbc on nbc.event_id = events.id")
  .where("pt.name like '%plane%'")
  .order("3 desc")

Second step Transform inner join into relations. You have to declare an has_many / belongs_to to Execution and Product.

Model Event < AR::Base
  has_many :executions
  has_many :products, :through => :executions

Event
  .select("events.id, pt.name, nbc.nb")
  .joins(:products)
  .joins("left outer join (select product_id, `name` from `product_translations` where `locale` ='en' group by `product_id`) as pt on pt.`product_id` = products.id
          left outer join (select event_id, COUNT(*) as nb from orders group by event_id) as nbc on nbc.event_id = events.id")
  .where("pt.name like '%plane%'")
  .order("3 desc")

Sub queries out The left outer join can not be done with Rails 4 at the moment, but you can extract your sub queries.

sub_query1 = ProductTranslation
               .select("product_id, `name`")
               .where("`locale` ='en'")
               .group("`product_id`")

sub_query2 = Order
               .select("event_id, COUNT(*) as nb")
               .group("event_id")

Event
  .select("events.id, pt.name, nbc.nb")
  .joins(:products)
  .joins("left outer join (#{sub_query1.to_sql}) as pt on pt.`product_id` = products.id
          left outer join (#{sub_query2.to_sql}) as nbc on nbc.event_id = events.id")
  .where("pt.name like '%plane%'")
  .order("3 desc")

Some other corrects SQL commands are upcase, conditions are a hash, prefer use table_name for table models.

sub_query1 = ProductTranslation
               .select("`product_id`, `name`")
               .where({ :locale => 'en' })
               .group(:product_id)

sub_query2 = Order
               .select("event_id, COUNT(*) as nb")
               .group("event_id")

Event
  .select("#{Event.table_name}.id, pt.name, nbc.nb")
  .joins(:products)
  .joins("LEFT OUTER JOIN (#{sub_query1.to_sql}) AS pt
            ON pt.`product_id` = #{Product.table_name}.id
          LEFT OUTER JOIN (#{sub_query2.to_sql}) AS nbc
            ON nbc.event_id = #{Event.table_name}.id")
  .where("pt.name LIKE ?", '%plane%')
  .order("3 DESC")

Voilà !