代码之家 › 专栏 › 技术社区 › user3574603

Rails/ActiveRecord:count在调用my ActiveRecord:Relation时为什么会导致语法错误?

activerecord ruby-on-rails

user3574603 · 技术社区 · 6 年前

我一直在尝试优化一个查询。我有一个叫 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 如果失败了,我如何修复我的查询以确保它工作?

1 回复 | 直到 6 年前

ulferts 6 年前

你有硬接线 count(labels.id) as matching_label_count 在查询的select投影中。当你使用 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,必须先删除投影。或者

如果在其他地方不依赖“计数为”,则删除“选择投影”中的“计数为”
或者通过在方法中包装作用域来动态提供select值
或者先移除选择投影,例如通过 except .