Django Models descending/nested query

Question

I'm having trouble understanding how to use the table/model structure in Django to perform useful queries.

What I'm trying to do is: given the primary key of a Show object, get a set of all the characters in the show.

My models below:

class Location(models.Model):
    identifier = models.CharField(max_length=200)
    def __str__(self):
        """String for representing the Model object."""
        return self.identifier

class Character(models.Model):
    identifier = models.CharField(max_length=200)
    def __str__(self):
        """String for representing the Model object."""
        return self.identifier

class Show(models.Model):
    name = models.CharField(max_length=200)
    shorthand = models.CharField(max_length=2, unique=True)
    def __str__(self):
        """String for representing the Model object."""
        return self.shorthand

class Season(models.Model):
    number = models.IntegerField()
    show = models.ForeignKey(Show, on_delete=models.SET_NULL, null=True, related_name="seasons")
    class Meta:
        unique_together = (("number", "show"))
    def __str__(self):
        """String for representing the Model object."""
        return (str(self.show) + "s" + str(self.number))

class Episode(models.Model):
    number = models.IntegerField()
    season = models.ForeignKey(Season, on_delete=models.SET_NULL, null=True, related_name="episodes")
    class Meta:
        unique_together = (("number", "season"))
    def __str__(self):
        """String for representing the Model object."""
        return (str(self.season) + "ep" + str(self.number))

class Scene(models.Model):
    number = models.IntegerField()
    character = models.ManyToManyField(Character, related_name="scenes")
    location = models.ForeignKey(Location, on_delete=models.SET_NULL, null=True, related_name="scenes")
    episode = models.ForeignKey(Episode, on_delete=models.SET_NULL, null=True, related_name="scenes")
    class Meta:
        unique_together = (("number", "episode"))
    def __str__(self):
        """String for representing the Model object."""
        return (str(self.episode) + "sc" + str(self.number))

This seems basic enough, but I haven't cracked how to descend the models via a Foreign key backtrack or other method. I've had a go at using prefetch_related but I think I'm getting confused as to what is a QuerySet vs. an object. I'm put off by the number of descending calls I would need to do, surely there is a more compact/concise way in Django? I've also considered adding a foreign key to the Character model, but pretty sure I would end up with the same problem.

Any help/pointers would be much appreciated!

user_10746224 · Answer

Using the models you have, you would need to chain together several lookups in the filter:

characters = Character.objects.filter(scenes__episode__season__show_id=show_id)

That being said, I would recommend modifying your schema a bit because this query is quite expensive.

If you add a ManyToManyField to your Show model (and populate the table), it turns this query into a trivial M2M lookup:

class Show(models.Model):
    characters = models.ManyToManyField(Character)
    ...

characters = Show.objects.get(id=show_id).characters.all()

In regards to the prefetch_related, it is simply an optimization that prevents multiple database hits.

The first query hits the database 4 separate times, but if you add a prefetch:

characters = Character.objects.prefetch_related(
    'scenes', 'episode', 'season', 'show'
).filter(scenes__episode__season__show_id=show_id)

It only hits the database once, and does all the joining in python.

Django Models descending/nested query

1 Answers