Django cannot determine queryset for chaining one-to-many with one-to-one relationship

Question

I have a system where there is a many to one relationship with a number a model (say 1 a -> many b) and that many model has a one to one relationship with another model (say 1 b -> 1 c). Drawn like so:

  /--- b1 --- c1
 /
a ---- b2 --- c2
 \
  \--- b3 --- c3

I'm determined to create a method in that collects all the c's that correspond to a.

Given an model system with the same structure to mine, the best I could come up with is shown in the method: Person.find_important_treats().

Is there a better way that does not involve so many calls to the database?

from django.db import models

class Person(models.Model): 
    """ The 'a' from my above example """

     def find_important_treats(self):
        return (pet.treat for pet in self.pets)

class Pet(models.Model):
    """ The 'b' from my above example """

    owner = models.ForeignKey(
        to=Person,
        related_name='pets'
    )

    favourite_treat = models.ForeignKey(
        to=Treat,
    )

class Treat(models.Model):
    """ The 'c' from my above example """
    pass

Answer 1

I suggest the two almost similar solution depending on your use case:

• Using caching

    class Person(models.Model): 
        """ The 'a' from my above example """

         @property
         def iter_important_treats(self):
            return (pet.treat_id for pet in self.pets.all()) # will use the cached objects if they exist

    person = Person.objects.get(id=person_id).select_related('pets') # to cache the pets list within the person object avoiding future additional queries
    treats = Treat.objects.filter(id__in=person.iter_importent_treats)

• Without Using caching:

class Person(models.Model): 
        """ The 'a' from my above example """

         @property
         def iter_important_treats(self):
            return iter(self.pets.values_list('treat_id', flat=True)) # caching will not affect the query behviour

    person = Person.objects.get(id=person_id)
    treats = Treat.objects.filter(id__in=person.iter_importent_treats)

NB:

1. we use treat_id instead treat__id to avoid additional join queries, because the django saves already the treat_id at the Pet object level but if you use treat__id then you force a join query.
2. Limiting the property to an iterator of IDs is just for reversibility and maintainability sake

Answer 2

The following should do what you are after:

def find_important_treats(self):
    return Treat.objects.filter(id__in=person.pets.values_list('treat_id'))

It obtains all of the ids of the Treats that the pets have and then returns them.