0

I'm using Django with postgresql. I have 3 tables with m2m relations: movies, genres and persons. Each person can have it's own role as actor, writer, or director.

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  |

What i'm trying to reach is grouped by genres and persons name and concatenated name fields for each movie:

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           |

Using raw sql it's quite easy to reach just subsequently group and string_agg subqueries.

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
;

But i can't come up with same result in django orm. Closest approach

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

gives me concatenated genres for every movie, but persons are still ungrouped:

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|

What is the best way to do this?

Max Rukhlov
  • 331
  • 3
  • 12
  • you need one more string_agg for the name in the annotate – manishg Mar 18 '21 at 03:25
  • @manishg yes, but where i should put it? if i put it after the first annotate clause, second feild is not grouped – Max Rukhlov Mar 18 '21 at 05:11
  • .annotate(genress=StringAgg('genres__name', delimiter=', ', ordering='genres__name'), name=StringAgg('persons__name', delimiter=', ')) – manishg Mar 18 '21 at 11:53
  • What happens when you try the above? – manishg Mar 18 '21 at 11:54
  • @manishg `SELECT "movies_filmwork"."title", "movies_person"."role", "movies_person"."name", STRING_AGG("movies_genre"."name", ', ' ORDER BY "movies_genre"."name") AS "genress", STRING_AGG("movies_person"."name", ', ') AS "name"` two STRING_AGG states on one level, not what i need – Max Rukhlov Mar 18 '21 at 12:58

0 Answers0