Rails/ActiveRecord: Why does count result in a syntax error when called on my ActiveRecord:Relation?

Question

I have been trying to optimize a query. I have a model named Issue and a model named Labels they have a many to many relationship.

To get a list of issues that has labels with names that match every item in a given array, I'm using:

    Issue.select('issues.id, count(labels.id) as matching_label_count')
      .joins(:labels)
      .where(labels: { name: [*labels] })
      .having("matching_label_count = #{labels.size}")
      .group('issues.id')

Using pry, I see that the query returns an Issue::ActiveRecord_Relation as expected and that it responds to ActiveRecord::Calculations methods. However, when I call count on the result I get a syntax error:

    pry(main)> Issue.select('issues.id, count(labels.id) as
    matching_label_count').includes(:labels).where(labels: { name: labels
    }).having("matching_label_count = #{labels.size}").group('issues.id').count
    (0.9ms)  SELECT COUNT(DISTINCT issues.id, count(labels.id) as
    matching_label_count) AS
    count_issues_id_count_labels_id_as_matching_label_count, issues.id,
    count(labels.id) as matching_label_count, issues.id AS issues_id FROM "issues"
    LEFT OUTER JOIN "tags" ON "tags"."issue_id" = "issues"."id" LEFT OUTER JOIN
    "labels" ON "labels"."id" = "tags"."label_id" WHERE "labels"."name" IN (?, ?)
    GROUP BY issues.id HAVING (matching_label_count = 2) ORDER BY
    "issues"."created_at" DESC  [["name", "bug"], ["name", "enhancement"]]

    ActiveRecord::StatementInvalid: SQLite3::SQLException: near "as": syntax error:
    SELECT COUNT(DISTINCT issues.id, count(labels.id) as matching_label_count) AS
    count_issues_id_count_labels_id_as_matching_label_count, issues.id,
    count(labels.id) as matching_label_count, issues.id AS issues_id FROM "issues"
    LEFT OUTER JOIN "tags" ON "tags"."issue_id" = "issues"."id" LEFT OUTER JOIN
    "labels" ON "labels"."id" = "tags"."label_id" WHERE "labels"."name" IN (?, ?)
    GROUP BY issues.id HAVING (matching_label_count = 2) ORDER BY
    "issues"."created_at" DESC from /Users/Arnould/.rvm/gems/ruby-2.6.0/gems/sqlite3-1
    .3.13/lib/sqlite3/database.rb:91:in `initialize' Caused by
    SQLite3::SQLException: near "as": syntax error from /Users/Arnould/.rvm/gems/ruby-
    2.6.0/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:91:in `initialize'

It does however work fine with length. size returns a hash with a count (label_name_count) as the value and the issue id as the key.

I already have half a dozen tests calling count on the result I want to know why it doesn't work before I consider changing them.

What is causing #count to fail and how can I fix it my query to ensure it works?

Answer 1

You have hard wired count(labels.id) as matching_label_count within the select projection of your query. When you use the count method, ActiveRecord will wrap all of your select fields inside a COUNT of its own leading to:

SELECT COUNT(DISTINCT issues.id, count(labels.id) as matching_label_count) AS
    count_issues_id_count_labels_id_as_matching_label_count

That alias/as within the count is invalid SQL and that is why sqlite3 reports the error.

If you rely on length or size, the query is unaltered, i.e. selects not wrapped inside a count and executed first. Only after the records are retrieved and the models are initialised are the instances counted. That will work as such but is highly inefficient if you are only interested in the count. If you need the instances elsewhere as well, that approach is fine however.

In order to get the query to work with count, you will have to remove the projection first. Either by

• removing the 'count as' within the select projection if you do not rely on them elsewhere
• or by providing the select values dynamically by wrapping the scope within a method
• or by removing the select projection first e.g. via except.