2

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?

ezdazuzena
  • 6,120
  • 6
  • 41
  • 71
  • 1
    I think that the proper way to do it is to use the `array_agg` postgresql function (https://www.postgresql.org/docs/9.5/static/functions-aggregate.html and https://lorenstewart.me/2017/12/03/postgresqls-array_agg-function/). Django seems to actually support this (in v. 2.1 at least: https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/aggregates/#arrayagg) 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(...))` – Serafeim Oct 31 '18 at 15:20
  • @serafeim thanks, this is what I needed. Don't know why I ignored annotate and just stack to aggregate. – ezdazuzena Nov 08 '18 at 14:42
  • 1
    Glad I helped! I'm adding it also as a proper answer in case anybody stumbles upon the qestion and missed the comment! – Serafeim Nov 08 '18 at 20:23

1 Answers1

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(...))

Serafeim
  • 14,962
  • 14
  • 91
  • 133