如何将含虚拟列求和的MySQL查询转为Rails ActiveRecord查询

转换后的查询代码 #

假设你的Rails模型为Ip（对应ip表）和Up（对应up表），可以按以下方式构建ActiveRecord查询：

# 构造子查询，对应原SQL中的内层查询
sub_query = Ip.select(
  'distinct ip.id as a_id',
  '(SELECT count(up.id) FROM up WHERE up.i_p_id = ip.id AND up.role = "L") as u_c',
  '(SELECT count(up.id) FROM up WHERE up.institution_package_id = ip.id AND up.role = "I") as i_c'
).left_joins(:ups) # 这里依赖Ip模型已定义has_many :ups, foreign_key: 'i_p_id'关联

# 基于子查询构建外层查询，计算总和t_c
ip_records = Ip.from(sub_query, :sub)
               .select(
                 'sub.a_id',
                 'sub.u_c',
                 'sub.i_c',
                 '(sub.u_c + sub.i_c) as t_c'
               )

如果不想依赖模型关联，也可以直接指定join条件：

sub_query = Ip.select(
  'distinct ip.id as a_id',
  '(SELECT count(up.id) FROM up WHERE up.i_p_id = ip.id AND up.role = "L") as u_c',
  '(SELECT count(up.id) FROM up WHERE up.institution_package_id = ip.id AND up.role = "I") as i_c'
).joins('left outer join up on up.i_p_id = ip.id')

ip_records = Ip.from(sub_query, :sub)
               .select(
                 'sub.a_id',
                 'sub.u_c',
                 'sub.i_c',
                 '(sub.u_c + sub.i_c) as t_c'
               )

补充说明 #

• 子查询部分通过select嵌入原SQL的计数逻辑，distinct保证a_id不重复，对应原SQL的select distinct a.id as a_id。
• 外层查询使用from(sub_query, :sub)将子查询作为临时表sub，并在select中计算t_c为前两列的和。
• 执行ip_records会返回包含虚拟列属性的ActiveRecord Relation，可直接链式调用where、order等查询方法。
• 若要通过模型实例直接访问虚拟列，可在Ip模型中添加属性访问器：

class Ip < ApplicationRecord
  attr_accessor :a_id, :u_c, :i_c, :t_c
end

内容的提问来源于stack exchange，提问作者sunny1304