CODEWITHSUNDEEP
pythondjango
grok_j
grok_j

Reputation: 13

Django: How do you compare a QuerySet with fields on a ForeignKey object's property?

I am trying to query a model against a field on a ForeignKey object's property. I have the following models:

class Song(models.Model):
  name = models.CharField(max_length=20)
  limit = models.IntegerField()


class Recording(models.Model):
  song = models.ForeignKey(Song, on_delete=models.CASCADE)
  status = models.CharField(
    max_length=1,
    choices=STATUS_CHOICES,
    default=OPEN
  )

I would like to query Songs that have Recordings with status OPEN with a count of more than 'limit' (the field on Song). Looking over the django aggregation docs I tried something along the lines of:

# View

get(self):
  songs_count = Count('recording', filter=Q(recording__status='O'))
        songs = Song.objects.annotate(songs_count=songs_count)
        results = songs.filter(songs_count__gt=< each song.limit... >)

Can someone point the way on how to build this query? I greatly appreciate any and all feedback.

Upvotes: 1

Views: 482

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476527

You can work with an F object [Django-doc] to refer to a field, so:

from django.db.models import F, Q

Songs.objects.annotate(
    songs_count=Count('recording', filter=Q(recording__status='O'))
).filter(songs_count__gt=F('limit'))

Upvotes: 1

Related Questions