Search code examples
ruby-on-railsactiverecordruby-on-rails-5active-record-query

How do I query the number of objects on an associated model?

I have a Profile and that has_many :ratings.

What I want to find are the number of Profile objects that have more than 1 rating record associated with it.

I tried the following to no avail:

> Profile.includes(:ratings).where('ratings.count > 0').count
   (38.2ms)  SELECT COUNT(*) FROM "profiles" WHERE (ratings.count > 0)
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "ratings"
LINE 1: SELECT COUNT(*) FROM "profiles" WHERE (ratings.count > 0)
                                               ^
: SELECT COUNT(*) FROM "profiles" WHERE (ratings.count > 0)

And

> Profile.where('ratings.count > 1').count
   (28.1ms)  SELECT COUNT(*) FROM "profiles" WHERE (ratings.count > 1)
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "ratings"
LINE 1: SELECT COUNT(*) FROM "profiles" WHERE (ratings.count > 1)
                                               ^
: SELECT COUNT(*) FROM "profiles" WHERE (ratings.count > 1)

Note That my ratings model does not include a column called count. What I am trying to do is count the number of ratings objects associated with each profile record and return the number of Profile records that have more than 1 rating associated object.

How do I achieve that with ActiveRecord?

Edit 1

Trying two more queries per user793789's suggestions:

> Profile.includes(:ratings).where('ratings.count > 1').references(:ratings).count
   (55.3ms)  SELECT COUNT(DISTINCT "profiles"."id") FROM "profiles" LEFT OUTER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count > 1)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ...N "ratings"."profile_id" = "profiles"."id" WHERE (ratings.co...
                                                             ^
: SELECT COUNT(DISTINCT "profiles"."id") FROM "profiles" LEFT OUTER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count > 1)

> Profile.joins(:ratings).where('ratings.count > 1').count
   (40.4ms)  SELECT COUNT(*) FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count > 1)
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ...N "ratings"."profile_id" = "profiles"."id" WHERE (ratings.co...
                                                             ^
: SELECT COUNT(*) FROM "profiles" INNER JOIN "ratings" ON "ratings"."profile_id" = "profiles"."id" WHERE (ratings.count > 1)
Solution 
  • Profile.includes(:ratings).where('ratings.count > 0').references(:ratings).count

    OP's Edit 1

    After much backing and forthing, we finally settled on this:

    Profile.joins(:ratings).group('profiles.id').having('count(ratings.id) > 0').length

    But I feel like there must be a more simple way to do this.