在SQL中编写复杂的查询并通过Rails执行它们是一个简单的，好的，而且通常是更短的解决方案。
但是如果你真的想使用Rails工具来实现可重用性和/或安全性，那么这个查询可以通过Arel来实现，Arel是ActiveRecord查询内部构建的工具。
这被认为是私有API，可能会发生变化。升级滑轨时要小心。

class IP < ActiveRecord::Base
    has_many :ups, foreign_key: :IP, class_name: 'UP'
end
class UP < ActiveRecord::Base
end

a = IP.arel_table.alias('a')
b = UP.arel_table.alias('b')

first_subquery = Arel::Nodes::Grouping.new(
    UP.from(b).where(
      b[:i_p_id].eq(a[:id]).and(b[:role].eq('L'))
    ).select(b[:id].count).arel
)
second_subquery = Arel::Nodes::Grouping.new(
    UP.from(b).where(
      b[:institution_package_id].eq(a[:id]).and(b[:role].eq('I'))
    ).select(b[:id].count).arel
)
sub = IP.left_joins(:ups).distinct.select(
  a[:id].as('a_id'),
  first_subquery.as('u_c')
  second_subquery.as('i_c')
).arel.as('sub')

Arel::SelectManager.new.from(sub).project(
  sub[:a_id],
  sub[:u_c],
  sub[:i_c],
  (sub[:u_c] + sub[:i_c]).as('t_c')
).to_sql

它会产生这样的东西

SELECT sub."a_id",
    sub."u_c",
    sub."i_c",
    (sub."u_c" + sub."i_c") AS t_c
FROM (
        SELECT DISTINCT "a"."id" AS a_id,
            (
                (
                    SELECT COUNT("b"."id")
                    FROM "UP" "b"
                    WHERE "b"."i_p_id" = "a"."id"
                        AND "b"."role" = 'L'
                )
            ) AS u_c,
            (
                (
                    SELECT COUNT("b"."id")
                    FROM "UP" "b"
                    WHERE "b"."institution_package_id" = "a"."id"
                        AND "b"."role" = 'I'
                )
            ) AS i_c
        FROM "IP"
            LEFT OUTER JOIN "UP" ON "UP"."IP" = "IP"."id"
    ) sub;

我添加了几个别名，使其与初始查询类似，但由于Arel负责引用它们，因此大多数别名都可以删除。