mysql - 当同时连接到同一个表时，如何按 Rails 中的聚合计数进行排序？

Question

有一个这样的范围:

  scope :sorted_by_dongle_count,
    > { includes(:dongles)
          .order('count(dongles.id) ASC')
          .group('organisations.id')
          .references(:dongles) }

这对于 SQLite 非常有效，但对于 MySQL 却失败了。

ActiveRecord::StatementInvalid: Mysql2::Error: Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'portal_development.dongles.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: [query here]

查看查询，很容易看出原因:

SELECT `organisations`.`id` AS t0_r0,
       `organisations`.`name` AS t0_r1,
       `dongles`.`id` AS t1_r0,           👈 This should be an aggregate!
       `dongles`.`name` AS t1_r1,
       `dongles`.`organisation_id` AS t1_r4
FROM `organisations`
  LEFT OUTER JOIN `dongles`
    ON `dongles`.`organisation_id` = `organisations`.`id`
GROUP BY organisations.id
ORDER BY count(dongles.id) DESC

是否有一种正确的方法可以在不诉诸 select() 并对整个列列表进行硬编码的情况下执行此操作？

(我已经知道的另一种解决方法是计数缓存。我已经在考虑这一点，因为随时可用的计数会加快排序速度。)

Answer 1

我发现如果我更改为:

  scope :sorted_by_dongle_count,
   -> { joins(:dongles)       👈
          .order('count(dongles.id) ASC')
          .group('organisations.id')
          .references(:dongles) }

我不再收到错误，结果是一样的。所以我想只有在使用计数或其他聚合时，才避免使用 includes ？但对于所有其他查询，请使用 includes...