Django Query - Multiple Inner Joins

Question

we currently have some issues with building complex Q-object queries with multiple inner joins with Django.

The model we want to get (called 'main' in the example) is referenced by another model with a foreign key. The back-reference is called 'related' in the example below. There are many objects of the second model that all refer to the same 'main' object all having ids and values.

We want to get all 'main' objects for which a related object with id 7113 exists that has the value 1 AND a related object with id 7114 that has the value 0 exists.

This is our current query:

(Q(related__id=u'7313') & Q(related__value=1)) & (Q(related__id=u'7314') & Q(related__value=0))

This code evaluates to

FROM `prefix_main` INNER JOIN `prefix_related` [...] WHERE (`prefix_related`.`id` = 7313 AND `prefix_related`.`value` = 1 AND `prefix_related`.`id` = 7314 AND `prefix_related`.`value` = 0)

What we would need is quite different:

FROM `prefix_main` INNER JOIN `prefix_related` a INNER JOIN `prefix_related` b [...] WHERE (a.`id` = 7313 AND a.`value` = 1 AND b.`id` = 7314 AND b.`value` = 0)

How can I rewrite the ORM query to use two INNER JOINS / use different related instances for the q-objects? Thanks in advance.

lilaLeon · Accepted Answer · 2017-10-30 12:03:48Z

1

i don't think you even need Q-objects for this. You can just use multiple filters like this:

Mainmodel.objects.filter(related__id = 7114, related__value=1).filter(related__id = 7113, related__value=0)

the first filter matches all objects that have a related object with the id 7114 and value 1. The returned objects are filtered once again with the id 7113 and the value 0.

answered Oct 30, 2017 at 12:03

lilaLeon

5163 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Daniel K. Over a year ago

Yes - this solution works fine for "AND" conditions. Thanks! I forgot to mention, that we try to find a solution that works for "OR" too - in our use case this query should work too: FROM prefix_main` INNER JOIN prefix_related [...] WHERE (prefix_related.id = 7313 AND prefix_related.value = 1 OR prefix_related.id = 7314 AND prefix_related.value = 0)`

lilaLeon Over a year ago

@DanielK. In that case you can just do: Mainmodel.objects.filter(Q(related__id = 7114, related__value=1)|Q(related__id = 7113, related__value=0))

Daniel K. Over a year ago

Our filters are dynamically combines with other filter rules during the runtime so we'd prefer to have a single solution that works almost the same for both cases. Do you see any change to cover both cases with one construct (either using two filter-calls or Q-objects)?

lilaLeon Over a year ago

no sorry, but maybe you could look into the union/intersection/difference queryset-operator. Good luck!

Collectives™ on Stack Overflow

Django Query - Multiple Inner Joins

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related