django list model entry with multiple references

Question

I have the following models which represent songs and the plays of each song:

from django.db import models


class Play(models.Model):
     play_day = models.PositiveIntegerField()
     source = models.CharField(
         'source',
         choices=(('radio', 'Radio'),('streaming', 'Streaming'), )
     )
     song = models.ForeignKey(Song, verbose_name='song')    


class Song(models.Model):

     name = models.CharField('Name')

Image I have the following entries:

Songs:

|ID | name                |
|---|---------------------|
| 1 | Stairway to Heaven  |
| 2 | Riders on the Storm |

Plays:

|ID | play_day | source    | song_id |
|---|----------|-----------|---------|
| 1 | 2081030  | radio     | 1       |
| 1 | 2081030  | streaming | 1       |
| 2 | 2081030  | streaming | 2       |

I would like to list all the tracks as follows:

| Name                | Day        | Sources          |
|---------------------|------------|------------------|
| Stairway to Heaven  | 2018-10-30 | Radio, Streaming |
| Riders on the Storm | 2018-10-30 | Streaming        |

I am using Django==1.9.2, django_tables2==1.1.6 and django-filter==0.13.0 with PostgreSQL.

Problem: I'm using Song as the model of the table and the filter, so the queryset starts with a select FROM song. However, when joining the Play table, I get two entries in the case of "Stairway to Heaven" (I know, even one is too much: https://www.youtube.com/watch?v=RD1KqbDdmuE).

What I tried:

• I tried putting a distinct to the Song, though this yields the problem that I cannot sort for other columns than the Song.id (supposing I do distinct on that column)

• Aggregate: this yields a final state, actually, a dictionary and which cannot be used with django_tables.

• I found this solution for PostgreSQL Selecting rows ordered by some column and distinct on another though I don't know how to do this with django.

Question: What would be the right approach to show one track per line "aggregating" information from references using Django's ORM?

Answer 1

I think that the proper way to do it is to use the array_agg postgresql function (http://postgresql.org/docs/9.5/static/functions-aggregate.html and http://lorenstewart.me/2017/12/03/postgresqls-array_agg-function).

Django seems to actually support this (in v. 2.1 at least: http://docs.djangoproject.com/en/2.1/ref/contrib/postgres/aggregates/) thus that seems like the way to go.

Unfortunately I don't have time to test it right now so I can't provide a thorough answer; however try something like: Song.objects.all().annotate(ArrayAgg(...))