CODEWITHSUNDEEP
pythondjangodjango-queryset
user2194805
user2194805

Reputation: 1379

Django filter reversed foreignkey

I have these tables in a django app:

class Order(models.Model):
    ...

class SubOrder1(models.Model):
    order = models.ForeignKey(Order, blank=True, null=True)

class SubOrder2(models.Model):
    order = models.ForeignKey(Order, blank=True, null=True)

...

How can I write a query on Order which results only the orders which has at least one related SubOrder1 or SubOrder2? I need something like

Order.objects.filter(suborder__count__ge = 1, ...)

I am using Django = 1.9.2 and Python = 3.4.1

Upvotes: 6

Views: 4097

Answers (4)

ndpu
ndpu

Reputation: 22571

Use isnull field lookup:

orders_with_suborders = Order.objects.filter(
    Q(suborder1__isnull=False) | Q(suborder2__isnull=False)
)

Upvotes: 3

Zagorodniy Olexiy
Zagorodniy Olexiy

Reputation: 2212

You can get to the SubOrder1 from Order. 

order = Order.objects.get(pk=pk)
suborders1 = order.suborder1_set.all()

Then you can iterate through the suborders:

for suborder in suborders1:
  print (suborder)

Hope it help you

Upvotes: -1

user2194805
user2194805

Reputation: 1379

A solution i just found would be something like:

Order.objects.filter(suborder1__id__gt = -1)

and i could use the same for SubOrder2. This is a solution, but not really djangonic. Is there any better solution?

Upvotes: 0

Alasdair
Alasdair

Reputation: 309109

Annotate your queryset with the counts of the related models:

from django.db.models import Count

queryset = Order.objects.annotate(
    num_suborder1=Count('suborder1', distinct=True),
    num_suborder2=Count('suborder2', distinct=True),
)

See the docs about combining multiple aggregations to explain why we need distinct=True

Then you can use Q to filter objects where either of the counts is at least 1.

orders_with_suborders = queryset.filter(
    Q(num_suborder1__gte=1) | Q(num_suborder1=1__gte=1),
)

Upvotes: 4

Related Questions