django - Django 后续 StringAgg 与分组

Question

我将 Django 与 postgresql 一起使用。我有 3 个具有 m2m 关系的表movies：genres和persons. 每个人都可以有自己的演员、作家或导演角色。

title    |genre_name|role  |name          |
---------|----------|------|--------------|
Dark Star|Sci-Fi    |Actor |Brian Narelle |
Dark Star|Sci-Fi    |Writer|John Carpenter|
Dark Star|Sci-Fi    |Writer|Dan O'Bannon  |
Dark Star|Sci-Fi    |Actor |Cal Kuniholm  |
Dark Star|Comedy    |Actor |Brian Narelle |
Dark Star|Comedy    |Writer|Dan O'Bannon  |
Dark Star|Comedy    |Actor |Cal Kuniholm  |

我想要达到的内容按类型和人名以及每部电影的串联名称字段分组：

title    |genres        |role  |names                                  |
---------|--------------|------|---------------------------------------|
Dark Star|Comedy, Sci-Fi|Actor |Brian Narelle, Cal Kuniholm, Dre Pahich|
Dark Star|Comedy, Sci-Fi|Writer|Dan O'Bannon, John Carpenter           |

使用原始 sql 很容易达到随后的组和string_agg子查询。

select distinct tt.title, tt.genres, tt.role, string_agg(tt.name, ', ') as names
from (
    select t.title, string_agg(t.genre_name, ', ' ORDER BY t.genre_name) as genres, t.role, t.name
    from (
        SELECT "movies_filmwork"."title",
               "movies_genre"."name" as "genre_name",
               "movies_person"."role",
               "movies_person"."name"
          FROM "movies_filmwork"
          LEFT OUTER JOIN "movies_filmwork_genres"
            ON ("movies_filmwork"."id" = "movies_filmwork_genres"."filmwork_id")
          LEFT OUTER JOIN "movies_genre"
            ON ("movies_filmwork_genres"."genre_id" = "movies_genre"."id")
          LEFT OUTER JOIN "movies_filmwork_persons"
            ON ("movies_filmwork"."id" = "movies_filmwork_persons"."filmwork_id")
          LEFT OUTER JOIN "movies_person"
            ON ("movies_filmwork_persons"."person_id" = "movies_person"."id")
        where "movies_filmwork"."title" = 'Dark Star'
     ) t
     group by t.title, t.role, t.name
 ) tt
 group by tt.title, tt.role, tt.genres
 order by tt.title, genres
;

但我无法在 django orm 中得出相同的结果。最接近的方法

Filmwork.objects.filter(title='Dark Star')
.values('title', 'persons__role', 'persons__name')
.annotate(genress=StringAgg('genres__name', delimiter=', ', ordering='genres__name'))

给我每部电影的连接类型，但人仍然没有分组：

title    |genres        |role  |name          |
---------|--------------|------|--------------|
Dark Star|Comedy, Sci-Fi|Actor |Brian Narelle |
Dark Star|Comedy, Sci-Fi|Actor |Cal Kuniholm  |
Dark Star|Comedy, Sci-Fi|Actor |Dre Pahich    |
Dark Star|Comedy, Sci-Fi|Writer|Dan O'Bannon  |
Dark Star|Comedy, Sci-Fi|Writer|John Carpenter|

做这个的最好方式是什么？

标签： djangopostgresqldjango-ormdjango-annotate