sql - 按关联的表属性分组和排序

Question

有两个表，Group sections 和 Groups。

我想按组部分对组进行分组和排序。

组部分:

Id | Name       | Priority
1  | Football   | 2
2  | Basketball | 1
3  | Tennis     | 3

群组:

Id | section_id | Name 
1  | 1          | Barcelona
2  | NULL       | Noname
3  | 1          | Real Madrid
4  | 2          | Cedevita 
5  | 3          | Ljubljana

我现在在 sql 中得到了什么:

  SELECT  group_sections.id, group_sections.priority AS priority, group_sections.name, groups.*
  FROM groups 
  LEFT OUTER JOIN group_sections ON group_sections.group_id = groups.id
  GROUP BY group_sections.id, groups.id
  ORDER BY group_sections.priority ASC

我想得到的:

Football => [<Group id: 1>, <Group id: 3], Basketball => [<Group id: 2>], Tennis => [<Group id: 3]

我怎样才能在 rails active record 中得到这个？

@groups.joins(:group_sections).group('group_sections.id', 'groups.id').order('group_sections.priority ASC')

这个查询对我不起作用。有什么想法吗？

Answer 1

你可以试试string_agg

select string_agg(name || ' => [' || groupnames ,', ')  groupnames
from(
SELECT b.name,  string_agg(a.name,', ') || ']' groupnames
FROM groups1 a
JOIN group_sections b ON b.id = a.sectionid
group by a.sectionid, b.name, b.priority
order by b.priority) x