How can use union and "not in" function in django query. I have searched on it but cannot find any example
SELECT id,address
FROM tbl_nt
WHERE st_id IN (1,2) AND name = 'foo'
UNION (
SELECT d.id,d.addrses
FROM tbl_nt_123 d
WHERE d.name='foo' AND condition_id NOT IN (
SELECT condition_id
FROM tbl_conditions
WHERE version_id = 5
)
)
i have tried this query for lower portion but didn't work
tbl_nt_123.objects.values_list('id', 'address').exclude(
condition_id=tbl_conditions
).objects.filter(version_id=5).values_list(
'condition_id', flat=True)
)
How can i do this?
Please refer me some good links or books for understand advance django queries.
Thank you
Starting from version 1.11, django querysets have a builtin union method. q = q1.union(q2) #q will contain all unique records of q1 + q2 q = q1.union(q2, all=True) #q will contain all records of q1 + q2 including duplicates q = q1.union(q2,q3) # more than 2 queryset union. See my blog post on this for more examples.
The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns The columns in every SELECT statement must also be in the same order The UNION operator selects only distinct values by default.
SELECT column1, column2 FROM table1 UNION [ ALL ] SELECT column3, column4 FROM table2; To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION.
We can use the UNION ALL operator if we want duplicates to be present in the combination of two or more SELECT statements. This SQL operator follows the same rules as the UNION operator, except for the use of the UNION ALL keyword instead of the UNION keyword in the syntax.
Probably you should just add the __in
lookup modifier:
tbl_nt_123.objects.values_list('id','address').exclude(
condition_id__in=tbl_conditions.objects.filter(version_id=5).values_list('condition_id',flat=True))
As for the union, you can fake it using the |
operator.
union = queryset1 | queryset2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With