自定义选择的连接表 [英] Custom select on join table
问题描述
我用钢轨3.2,并试图使用ActiveRecord来查询我的数据库。 我有2 ActiveRecord模型,管理与秩序:
I'm using rails 3.2, and trying to use ActiveRecord to query my database. I have 2 activerecord models, Admin and Order:
class Admin < ActiveRecord::Base
attr_accessible :email, :name
has_many :orders
class Order < ActiveRecord::Base
attr_accessible :operation
belongs_to :admin
在我的情况下,order.operation是一个字符串,再presenting订单类型。我试图建立一个查询给我三列:
In my case, order.operation is a string, representing order type. I'm trying to build a query giving me three columns:
admin.name, admin.orders.where(:operation => 'bonus').count, admin.orders.where(:operation => 'gift').count
有没有办法在一个查询中,以适应呢?
Is there a way to fit it in a single query?
编辑: 这里的原始SQL得到我所需要的:
EDITED: here's raw sql to get what I need:
SELECT t_a.admin_id_com, t_a.name, gb_f_f_gb_f_fi.bonus_count, gb_f_f_gb_f_fi.gifts_count
FROM (
SELECT f_fil.admin_id_gifts, f_fil.gifts_count, f_filt.admin_id_bonus, f_filt.bonus_count
FROM (
SELECT admin_id as admin_id_gifts, count(distinct id) as gifts_count FROM orders WHERE operation = 'new gifts!' GROUP BY admin_id_gifts)
f_fil LEFT OUTER JOIN (
SELECT admin_id as admin_id_bonus, count(distinct id) as bonus_count FROM orders WHERE operation = 'new bonuses!' GROUP BY admin_id_bonus)
f_filt ON (f_fil.admin_id_gifts = f_filt.admin_id_bonus))
gb_f_f_gb_f_fi LEFT OUTER JOIN (
SELECT id AS admin_id_com, t_ad.name FROM admins t_ad) t_a ON (gb_f_f_gb_f_fi.admin_id_gifts = t_a.admin_id_com)
是否有可能像打造专业化的查询,使用ActiveRecord的?
Is it possible to buid a query like that using ActiveRecord?
推荐答案
试试这个:
@admins = Admin.joins(:orders).
select("admins.id, admins.name, orders.id,
SUM((orders.operation = 'bonus')::integer) AS bonus_count,
SUM((orders.operation = 'gift')::integer) AS gift_count ").
group("admins.id ")
# access each columns as
admin.name, admin.bonus_count, admin.gift_count
另一种选择是使用预先加载,它会使用两个查询,但可能会更快
Other option is to use eager loading, it will use two queries but might be faster
@admins = Admin.includes(:orders)
# in admin.rb
def orders_count(type)
# Don't use where here as it would make a separate query instead of using eager loading records
orders.select{|x| x.operation == type}.count
end
# access each columns as
admin.name, admin.orders_count("bonus"), admin.orders_count("gift")
这篇关于自定义选择的连接表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!