Strange difference between .size and .count when grouping SQL query

August 11, 2022

I have scope in Usage model:

# == Schema Information
# id                :uuid
# service           :string
# organization_id   :uuid
# document_id       :uuid
# created_at        :datetime

class Usage < ApplicationRecord
  belongs_to :document
  
  scope :grouped, -> {
    joins(document: :user)
      .select("COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at)")
      .group('service', 'documents.subject', 'document_id', "CONCAT(users.first_name, ' ', users.last_name)",  'usages.created_at::date')
      .order("MAX(usages.created_at) DESC")
  }
  
  ...

When in the rails console I do result = Usage.all.grouped, result:

Usage Load (2.5ms)  SELECT COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at) FROM "usages" INNER JOIN "documents" ON "documents"."id" = "usages"."document_id" INNER JOIN "users" ON "users"."id" = "documents"."user_id" GROUP BY "usages"."service", documents.subject, "usages"."document_id", CONCAT(users.first_name, ' ', users.last_name), usages.created_at::date ORDER BY MAX(usages.created_at) DESC LIMIT $1  [["LIMIT", 11]]
 
=> #<ActiveRecord::Relation [#<Usage id: nil, service: "document", document_id: "f3455d09-0681-46d0-bf2d-54bb3286089b">, #<Usage id: nil, service: "document", document_id: "db3303de-f2ac-4611-8603-da70847e7eeb">, #<Usage id: nil, service: "document", document_id: "642b9fa8-e72e-4bb8-9551-d30f92bf7e67">]>

Then, to expand this query into a hash with the parameters I need, I call the .size method on this scope.

result.size

(1.7ms)  SELECT COUNT(*) AS count_all, "usages"."service" AS usages_service, documents.subject AS documents_subject, "usages"."document_id" AS usages_document_id, CONCAT(users.first_name, ' ', users.last_name) AS concat_users_first_name_users_last_name, usages.created_at::date AS usages_created_at_date FROM "usages" INNER JOIN "documents" ON "documents"."id" = "usages"."document_id" INNER JOIN "users" ON "users"."id" = "documents"."user_id" GROUP BY "usages"."service", documents.subject, "usages"."document_id", CONCAT(users.first_name, ' ', users.last_name), usages.created_at::date ORDER BY MAX(usages.created_at) DESC

=> {
  ["document", "Alena Cartwright II welcome a Gorgon", "f3455d09-0681-46d0-bf2d-54bb3286089b", "Test Test", Tue, 09 Aug 2022]=>1, 
  ["document", "Thanh Harber Sr. view a Ankylosaurus", "db3303de-f2ac-4611-8603-da70847e7eeb", "Test2 Test", Mon, 08 Aug 2022]=>1,
  ["document", "Rolando Langosh DO bust a Clay Golem", "642b9fa8-e72e-4bb8-9551-d30f92bf7e67", "Test3 Test", Sun, 07 Aug 2022]=>1
}

As we can see, when the .size method is called, another SELECT query comes in.

According to the documentation – .size returns the size of the collection by executing a SELECT COUNT(*) query if the collection hasn’t been loaded, and calling collection.length if it has.

But at the same time, I get an error if I try to call the .count method on this scope.

result.count

(1.4ms)  SELECT COUNT(COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at)) AS count_count_usages_document_id_usages_service, "usages"."service" AS usages_service, documents.subject AS documents_subject, "usages"."document_id" AS usages_document_id, CONCAT(users.first_name, ' ', users.last_name) AS concat_users_first_name_users_last_name, usages.created_at::date AS usages_created_at_date FROM "usages" INNER JOIN "documents" ON "documents"."id" = "usages"."document_id" INNER JOIN "users" ON "users"."id" = "documents"."user_id" GROUP BY "usages"."service", documents.subject, "usages"."document_id", CONCAT(users.first_name, ' ', users.last_name), usages.created_at::date ORDER BY MAX(usages.created_at) DESC

Traceback (most recent call last):
        1: from (irb):5

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  function count(bigint, character varying, uuid, character varying, timestamp without time zone) does not exist)
LINE 1: SELECT COUNT(COUNT(usages.document_id), usages...

What is the difference between using .size and .count in this example?

Why does the .count method wrap the existing SQL COUNT method with another COUNT?

>Solution :

From the docs;

If count is used with Relation#select, it will count the selected
columns:

# Person.select(:age).count
SELECT COUNT("people"."age") FROM "people"

So, if your query happens to have a SELECT, then it’ll wrap whatever it contains within COUNT (see select_values)

By definition when using size it’ll default to count(:all) unless the object is loaded. So you anyway end up using count.