CODEWITHSUNDEEP
sqldjango
avittoria
avittoria

Reputation: 23

Django get objects that are foreign key of two models

I have the following three models where Budget and Sale both contain a foreign key to Customer:

class Customer(models.Model):
    name = models.CharField(max_length=45)
    # ...

class Budget(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.PROTECT)
    # ...

class Sale(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.PROTECT)
    # ...

I want to get a queryset of all Customer objects for which both a Budget and Sale exists. I initially tried getting the intersection of the customer field of all Budget and Sale objects:

customers = {
    budget.customer for budget in Budget.objects.all()
} & {
    sale.customer for sale in Sale.objects.all()
}

This returns the correct objects, but becomes horribly inefficient as the size of my database grows.

How can I retrieve these objects in a more efficient way? Thanks for any help!

Upvotes: 2

Views: 154

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

You can filter with:

Customer.objects.filter(
    budget__isnull=False,
    sale__isnull=False
).distinct()

Django can follow ForeignKeys in reverse. It uses the related_query_name=… parameter [Django-doc] for the name of relation. If that is not specified, it falls back on the related_name=… parameter [Django-doc] parameter, and if that is not specified, it will use the name of the model in lowercase, so budget and sale. We here make LEFT OUTER JOINs on the Budget and Sale table, and check if for both there is a non-null row. Likely the Django ORM will optimize this to INNER JOINs.

Upvotes: 1

Related Questions