How to simulate ActiveRecord Model.count.to_sql

Question

I want to display the SQL used in a count. However, Model.count.to_sql will not work because count returns a FixNum that doesn't have a to_sql method. I think the simplest solution is to do this:

Model.where(nil).to_sql.sub(/SELECT.*FROM/, "SELECT COUNT(*) FROM")

This creates the same SQL as is used in Model.count, but is it going to cause a problem further down the line? For example, if I add a complicated where clause and some joins.

Is there a better way of doing this?

Answer 1

You can try

Model.select("count(*) as model_count").to_sql

Answer 2

You may want to dip into Arel:

Model.select(Arel.star.count).to_sql

ASIDE: I find I often want to find sub counts, so I embed the count(*) into another query:

child_counts = ChildModel.select(Arel.star.count)
                         .where(Model.arel_attribute(:id).eq(
                                  ChildModel.arel_attribute(:model_id)))
Model.select(Arel.star).select(child_counts.as("child_count"))
     .order(:id).limit(10).to_sql

which then gives you all the child counts for each of the models:

SELECT  *,
        (
          SELECT COUNT(*)
          FROM "child_models"
          WHERE "models"."id" = "child_models"."model_id"
        ) child_count
FROM "models"
ORDER BY "models"."id" ASC
LIMIT 10

Best of luck