Django: Queryset filter with multilevel nesting

user_12501197 0
Viewed 441

The use case is the following: I'm trying to retrieve all Prospects that were generated under a Manager. The models are as follow: Prospect <---> Appointment <---> Staff <---> Manager. As you can see, there is no direct relationship between a prospect and a manager so I'm forced to do a nested, reverse lookup through the other models.

For example, I try to retrieve the prospects generated under Manager id=2 through the following query:

Prospect.objects.filter(appointment=Appointment.objects.filter(staff__manager=Manager.objects.get(user=2)))

But then I get the following error:

ValueError: The QuerySet value for an exact lookup must be limited to one result using slicing.

Any hint about what's wrong with my query?

Models.py

class Prospect(models.Model):
   first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    email = models.EmailField(max_length=40)
    phone = models.CharField(max_length=20)

class Appointment(models.Model):
    appointment_time = models.DateTimeField()
    unit = models.ForeignKey(Unit, on_delete=models.CASCADE)
    staff = models.ForeignKey(Staff, on_delete=models.CASCADE)
    prospect = models.ForeignKey(Prospect, on_delete=models.CASCADE)
    status = models.BooleanField(default=False)

class Staff(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, primary_key=True)
    manager = models.ManyToManyField(Manager, through=Manager.staff.through, related_name='+', blank=True)
    unit = models.ManyToManyField(Unit, through=Unit.staff.through, related_name='+', blank=True)
    profile_pic = models.ImageField(upload_to='profile_pic', blank=True)

class Manager(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, primary_key=True)
    staff = models.ManyToManyField('Staff', related_name='+', blank=True)
    company = models.CharField(max_length=40)
    logo = models.ImageField(upload_to='logo_image', blank=True)
django python
2 Answers

I think you make it too complex, you can use double underscores (__) to follow a relation, Django also conceptually has bidirectional relations, so you also query ForeignKeys, etc. in "reverse":

Prospect.objects.filter(appointment__staff__manager_user_id=2)

You can use .distinct() [Django-doc] to retrieve unique Prospects:

Prospect.objects.filter(appointment__staff__manager_user_id=2).distinct()

The reason your query does not work is because your Appointment.objects.filter(..) query is a collection of Appointments, you can query with:

Prospect.objects.filter(
    appointment__in=Appointment.objects.filter(
        staff__manager=Manager.objects.get(user=2)
    )
)

But regardless, this will increase the number of queries with one, and is thus less efficient.

user_675790
user_46376430
Related