Rails calculating a virtual column using 2 other virtual columns

Question

I need help converting the following MySQL query into an ActiveRecord Query that will produce a relation with IP records as object. To explain this in a better way, How to convert the following query in How to sum two virtual column into third column in sql in ActiveRecord query ?

select a_id
u_c,
i_c,
(u_c + 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 a
    left outer join UP b on b.IP = a.id       
) sub 

Let me explain a litte more. I have 2 tables where I am querying into the 1st table and calculating 3 virtual columns. The 3rd column will be the sum of the other 2 virtual columns. Thanks for the help.

Answer 1

Writing complicated queries in SQL and executing them via rails is a simple, okay, and often the shorter solution.

But if you actually want to use Rails tools for reusability and/or security purposes, then this query CAN be achieved via Arel, which is the tool that ActiveRecord queries are built on internally.

This is considered private api and is subject to change. Use caution when upgrading rails.

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

It will produce something like this

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;

I added several aliases to make it similar to the initial query, but since Arel is taking care of referencing them, most aliases can just be removed.