sql - 按模型关联计数排序

Question

我有一个模型事件，它依赖于执行，而执行又依赖于具有 ProductTranslation 的产品。同样的事件模型有很多订单。我想在表格中显示事件并能够按计数(订单)对它们进行排序，按名称搜索...我发出了sql请求

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

但我不能用 ActiveReccord 转录它，特别是 order by count(orders)

顺便说一下，我使用 gem globalize 进行翻译。

Answer 1

一种将 SQL 查询转换为 Rails 的方法:

初始请求

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

第一步确定主表；在这里，它是 Event。所以这是来自您的 Event 模型的调用。

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")

第二步 将inner join转化为关系。您必须声明 has_many/belongs_to 到 Execution 和 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")

子查询出来 目前左外连接不能用 Rails 4 完成，但是你可以提取你的子查询。

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")

其他一些更正 SQL 命令是大写的，条件是散列，更喜欢对表模型使用 table_name。

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")

瞧!