Rails-5中的按数组列值分组

Question

我有一个有趣的问题

我希望按表的数组列中的单个值进行计数

例如，用于

对于用户模型

id |  community_ids
1  |  {2, 4}
2  |  {2, 5} 
3  |  {2, 4}

我想说

Community| User count
  2      | 3
  4      | 2
  5      | 1

我有一个解决方案

User.
  pluck(Arel.sql('unnest(community_ids) as community_id')).
  group_by(&:itself).
  transform_values(&:count)

但问题是我遍历了所有的用户

谢谢

Answer 1

SQL版本可能类似于：

SELECT unnest(community_ids) AS community_id, count(*) AS count
FROM users 
GROUP BY community_id;

将它映射到rails AR也很简单，可以用不同的方式来完成，我将使用最简单的转储方式：

sql = "SELECT unnest(community_ids) AS community_id, count(*) AS count
FROM users 
GROUP BY community_id"
results = ActiveRecord::Base.connection.execute(sql).to_a
# results should look something like: [{"community_id"=>2,"count"=>3}...]

PS:这种方法取自。