Django ORM Union, Intersection and Difference
Starting from Version 1.11 (April 2017), Django ORM comes with Union, Intersection and Difference capabilities . While it was possible to implement this capability our own, it is nicer to have the ‘batteries included’.
Lets examine this capability using a simple model.
1 2 3 4 5 | class MyModel(models.Model): name = models.CharField(max_length=1) def __unicode__(self): return self.name |
I have added some sample data so that we can make queries
Let’s make some queries:
1 2 3 4 5 6 7 8 9 10 | >>> from myapp.models import MyModel >>> q1 = MyModel.objects.all() >>> q2 = MyModel.objects.filter(name__in=('A','B')) >>> q3 = MyModel.objects.filter(name__in=('C','D')) >>> print q1 <QuerySet [<MyModel: A>, <MyModel: B>, <MyModel: C>, <MyModel: D>, <MyModel: E>]> >>> print q2 <QuerySet [<MyModel: A>, <MyModel: B>]> >>> print q3 <QuerySet [<MyModel: C>, <MyModel: D>]> |
Uses SQL’s
UNIONoperator to combine the results of two or moreQuerySets. TheUNIONoperator selects only distinct values by default. To allow duplicate values, use theall=Trueargument.
Examples:
1 2 3 4 5 6 7 8 9 10 11 | >>> q4 = q2.union(q3) >>> print q4 <QuerySet [<MyModel: A>, <MyModel: B>, <MyModel: C>, <MyModel: D>]> >>> >>> q5 = q1.union(q2, q3) #Only distinct objects selected by default >>> print q5 <QuerySet [<MyModel: A>, <MyModel: B>, <MyModel: C>, <MyModel: D>, <MyModel: E>]> >>> >>> q6 = q1.union(q2, q3, all=True) #Include duplicate objects >>> print q6 <QuerySet [<MyModel: A>, <MyModel: B>, <MyModel: C>, <MyModel: D>, <MyModel: E>, <MyModel: A>, <MyModel: B>, <MyModel: C>, <MyModel: D>]> |
Uses SQL’s
INTERSECToperator to return the shared elements of two or moreQuerySets.
Examples:
1 2 3 4 5 6 7 8 9 10 11 12 | >>> q7 = q1.intersection(q2) >>> print q7 <QuerySet [<MyModel: A>, <MyModel: B>]> >>> >>> q8 = q2.intersection(q3) >>> print q8 <QuerySet [ ]> >>> >>> q9 = q1.intersection(q2, q3) >>> print q9 <QuerySet [ ]> >>> |
Uses SQL’s
EXCEPToperator to keep only elements present in theQuerySetbut not in some otherQuerySets.
Examples:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | >>> q10 = q1.difference(q2) #q1 - q2 >>> print q10 <QuerySet [<MyModel: C>, <MyModel: D>, <MyModel: E>]> >>> >>> q11 = q1.difference(q2, q3) #q1 - (q2 + q3) >>> print q11 <QuerySet [<MyModel: E>]> >>> >>> q12 = q2.difference(q3) #Will return q2 as q2 and q3 are mutually exclussive >>> print q12 <QuerySet [<MyModel: A>, <MyModel: B>]> >>> >>> q13 = q1.difference(q1) #q1 - q1 >>> print q13 <QuerySet [ ]> |
union(),intersection(), anddifference()return model instances of the type of the firstQuerySeteven if the arguments areQuerySets of other models. Passing different models works as long as theSELECTlist is the same in allQuerySets (at least the types, the names don’t matter as long as the types in the same order).In addition, only
LIMIT,OFFSET,COUNT(*), andORDER BY(i.e. slicing,count(), andorder_by()) are allowed on the resultingQuerySet. Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allowLIMITorOFFSETin the combined queries.
Thank you for this post. what’s the difference using union to using the | operator?
They are similar. query1 | query2 will give a union of the two queries, but will include duplicates. For query1.uninon(query2), it returns a new queryset with only unique items by default.