CODEWITHSUNDEEP
pythondjango
Max Vallee
Max Vallee

Reputation: 468

Django: Queryset filter with multilevel nesting

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)

Upvotes: 2

Views: 850

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477190

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.

Upvotes: 2

Red Twoon
Red Twoon

Reputation: 755

Your issue is that appointment=x, x must be 1 appointment. (if you want it to check against multiple, you can use appointment__in=x.

So you can change it to:

appointment = Appointment.objects.filter(staff__manager=Manager.objects.get(user=2))[0]  # see [0]
Prospect.objects.filter(appointment=appointment)

see this possible duplicate post: The QuerySet value for an exact lookup must be limited to one result using slicing. Filter error

Upvotes: 1

Related Questions