Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django Query: How to use sql "union" and "not in" function

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

like image 237
Shoaib Ijaz Avatar asked Mar 17 '14 13:03

Shoaib Ijaz


People also ask

How to Union querysets in Django?

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.

How do you use Union in SQL?

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.

How do I join two tables in SQL Server?

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.

When to use Union all in a SELECT statement?

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.


1 Answers

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
like image 57
Germano Avatar answered Oct 16 '22 07:10

Germano