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

Question

我一直在尝试优化查询。我有一个名为 Issue 的模型和一个名为 Labels 的模型他们有一个多对多的关系。

获取 issues 的列表有 labels使用与给定数组中的每个项目匹配的名称，我正在使用:

    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')

使用 pry，我看到查询返回 Issue::ActiveRecord_Relation正如预期的那样，它响应 ActiveRecord::Calculations方法。但是，当我调用 count结果我得到一个语法错误:

    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'

然而，它确实适用于 length . size返回一个以计数 ( label_name_count) 作为值，以问题 ID 作为键的散列。

我已经有六个测试调用 count关于结果，我想知道为什么在考虑更改它们之前它不起作用。

是什么导致 #count失败，我该如何修复我的查询以确保它正常工作？

Answer 1

你有硬接线count(labels.id) as matching_label_count在查询的选择投影中。当您使用 count方法，ActiveRecord 会将所有选择字段包装在 COUNT 中自身导致:

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

那个别名/ as计数内的 SQL 无效，这就是 sqlite3 报告错误的原因。

如果您依赖 length或 size ，查询是不变的，即选择不包含在 count 中并首先执行。只有在检索到记录并初始化模型之后，才会计算实例。如果您只对计数感兴趣，这将起作用，但效率非常低。如果您也需要其他地方的实例，那么这种方法很好。

为了使查询与计数一起使用，您必须先删除投影。或者通过

如果您在其他地方不依赖它们，则删除选择投影中的“计数为”

或者通过将范围包装在方法

中动态地提供选择值

或首先删除选择投影，例如通过
except .