Flatten results of multiple annotations

Using Django Using the ORM
1

I have a model for concert setlists, and I’m attempting to write a query that gets the songs in a given “position” (Show Opener, Show Closer, Encore Opener, etc.) for each distinct show in the filtered group.

So ideally, I’d have a resulting queryset with just the event, and an annotated field for each distinct position, and the song in that position.

So for example, I have this:

context["songs"] = (
  models.Setlists.objects.order_by("event__id", "song_num")
  .filter(event__tour=32)
  .exclude(
    position__isnull=True,
  )
  .values("event__id")
  .annotate(
    show_opener=Case(
      When(
        Q(position="Show Opener"),
        then=Subquery(
          models.Songs.objects.filter(
            id=OuterRef("song"),
          ).values(json=JSONObject(id="id", name="name")),
        ),
      ),
    ),
    show_closer=Case(
      When(
        Q(position="Show Closer"),
        then=Subquery(
          models.Songs.objects.filter(
            id=OuterRef("song"),
          ).values(json=JSONObject(id="id", name="name")),
        ),
      ),
    ),
  ).exclude(show_opener__isnull=True, show_closer__isnull=True)
)

This sort of works, but it includes duplicates where one annotated value is none while the other has a song and vice versa.

{'event__id': '19801003-01', 'show_opener': {'id': 164, 'name': 'Born to Run'}, 'show_closer': None}, {'event__id': '19801003-01', 'show_opener': None, 'show_closer': {'id': 1443, 'name': 'Thunder Road'}}

Even trying to do a “group by” with .values(‘event__id’, ‘position’) results in a similar result set, but one item for each position.

My goal is to have a “flattened” result set, something like:

{'event__id': '19801003-01', 'show_opener': {'id': 164, 'name': 'Born to Run'}, 'show_closer': 'show_closer': {'id': 1443, 'name': 'Thunder Road'}}

I’m aware I can just do a “regroup” in the template, but was wondering if something like the above is possible in the ORM.

Thanks in advance

2

Can you show the model schemes for that?

To me it seems at a first glance that you approach it via the wrong model - since your “authoritative” id is the event, maybe start filtering from the event model to extract the opener|closer entities. Just a first wild guess based on what you want to extract in the end.

3

Here is the model scheme for setlists:


class Setlists(models.Model):
    id = models.AutoField(primary_key=True)

    event = models.ForeignKey(
        to=Events,
        on_delete=models.DO_NOTHING,
        db_column="event_id",
        blank=True,
        default=None,
    )

    set_name = models.CharField(blank=True, default="Show")

    song_num = models.IntegerField(
        default=1,
    )

    song = models.ForeignKey(
        to=Songs,
        on_delete=models.DO_NOTHING,
        db_column="song_id",
        blank=True,
        default=None,
        to_field="id",
    )

    note = models.TextField(blank=True, default=None, db_column="song_note")
    segue = models.BooleanField(default=False)
    premiere = models.BooleanField(default=False)
    debut = models.BooleanField(default=False)
    instrumental = models.BooleanField(default=False)
    position = models.TextField(blank=True, default=None)
    last = models.IntegerField(blank=True, default=None)
    next = models.IntegerField(blank=True, default=None)
    tour_num = models.IntegerField(blank=True, default=0)
    tour_total = models.IntegerField(blank=True, default=0)
    sign_request = models.BooleanField(default=False)
    updated_at = models.DateTimeField()
    created_at = models.DateTimeField()

And here is the Events model, not sure if you wanted that too or not

class Events(models.Model):
    num = models.IntegerField(db_column="event_num")
    id = models.TextField(primary_key=True, db_column="event_id")
    date = models.DateField(blank=True, default=None, db_column="event_date")

    public = models.BooleanField(blank=True, default=False)

    artist = models.ForeignKey(
        to=Bands,
        on_delete=models.DO_NOTHING,
        db_column="artist",
    )

    venue = models.ForeignKey(
        to=Venues,
        on_delete=models.DO_NOTHING,
        related_name="event_venue",
        db_column="venue_id",
        blank=True,
        default=None,
    )

    tour = models.ForeignKey(
        to="Tours",
        on_delete=models.DO_NOTHING,
        blank=True,
        default=None,
        db_column="tour_id",
    )

    note = models.TextField(blank=True, default=None)

    updated_at = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True, blank=True, null=True)

Bit confused on how I would start from the Event model instead. I already have the query “grouping” by the event_id (which is a foreign key to Event model). Unless you mean something else.

4

What database are you using? I was thinking that maybe you could try doing a distinct on the event__id, but Sqlite for example doesn’t support DISTINCT ON .

Another alternative I can think of is a CTE, but that requires bringing in another package like django-cte or writing plain SQL.

5

using postgresql as the database.

After some messing around, I’ve found that the issue is with the annotate. Doing a Subquery or a JSONObject gives me the duplicated results like above.

The only way to avoid that is annotating the song ID and the song name separately. Then I get non-duplicated results. Only issue is then having to do two annotations for every single position, which gets a bit repetitive with 6 possible “positions”. Not sure why “grouping” the values in a JSONObject and/or Subquery would result in duplicated results, but doing them separate works.

  models.Setlists.objects.filter(
    event__tour__id=context["tour"].id,
    set_name__in=VALID_SET_NAMES,
    position__isnull=False,
  )
  .exclude(position=None)
  .values("event__id")
  .annotate(
    show_opener_id=Min(
      Case(
        When(
          position="Show Opener",
          then=F("song"),
        ),
      ),
    ),
    show_opener_name=Min(
      Case(
        When(
          position="Show Opener",
          then=F("song__name"),
        ),
      ),
    ),
    set_1_closer_id=Min(
      Case(
        When(
          position="Set 1 Closer",
          then=F("song"),
        ),
      ),
    ),
    set_1_closer_name=Min(
      Case(
        When(
          position="Set 1 Closer",
          then=F("song__name"),
        ),
      ),
    ),
    set_2_opener_id=Min(
      Case(
        When(
          position="Set 2 Opener",
          then=F("song"),
        ),
      ),
    ),
    set_2_opener_name=Min(
      Case(
        When(
          position="Set 2 Opener",
          then=F("song__name"),
        ),
      ),
    ),
    main_set_closer_id=Min(
      Case(
        When(
          position="Main Set Closer",
          then=F("song"),
        ),
      ),
    ),
    main_set_closer_name=Min(
      Case(
        When(
          position="Main Set Closer",
          then=F("song__name"),
        ),
      ),
    ),
    encore_opener_id=Min(
      Case(
        When(
          position="Encore Opener",
          then=F("song"),
        ),
      ),
    ),
    encore_opener_name=Min(
      Case(
        When(
          position="Encore Opener",
          then=F("song__name"),
        ),
      ),
    ),
    show_closer_id=Min(
      Case(
        When(
          position="Show Closer",
          then=F("song"),
        ),
      ),
    ),
    show_closer_name=Min(
      Case(
        When(
          position="Show Closer",
          then=F("song__name"),
        ),
      ),
    ),
  )
  .order_by("event")