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

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?

CodePudding user response：

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.