Counts in activerecord query without using sql string

Question

Looking for a proper way to include counts while trying to convert a pure SQL query to Activerecord query. I am not really experienced with Activerecord, so maybe I am missing something obvious here.

The problem is how to include counts in end results without having to use sql query.

Here is what I have so far and it works well. I just want to get rid of the remaining SQL part.

    abc has_many :pqr
    pqr has_many :xyz
    ids = [n]

    abc
     .select(
       :identifier,
       :name,
       "count(DISTINCT pqr.key) as pqr_count",
       "count(xyz.id) as xyz_count",
     )
     .joins(pqr: [:xyz])
     .where(
       active: true,
       id: ids,
       xyz: {
         status: 'open',
       }
     )
     .where.not(identifier: 'sandbox')
     .group(:identifier)

In short replace "count(DISTINCT pqr.key) as pqr_count" and "count(xyz.id) as xyz_count" with something better

Any point in right direction would be really great.

Answer 1

You can use substitute Arel nodes for the part of the select query where there are strings:

  abc
     .select(
       :identifier,
       :name,
       PqrModel.arel_table[:id].count(true).as('pqr_count'),
       XyzModel.arel_table[:id].count.as('xyz_count')
     )
     .joins(pqr: [:xyz])
     .where(
       active: true,
       id: ids,
       xyz: {
         status: 'open',
       }
     )
     .where.not(identifier: 'sandbox')
     .group(:identifier)