CODEWITHSUNDEEP
djangodjango-models
akaribi
akaribi

Reputation: 57

How to use UNION in Django + add fake columns

I am trying to convert the following query in django ORM:

SELECT 
    MONTH(date) AS Month,
    col1,
    col2,
    col3 col3,
    SUM(col4) col4,
    SUM(col5) col5
FROM
    table1
WHERE
    date BETWEEN '2018-07-19' AND '2018-10-17'
GROUP BY 1 , 2 , 3 , 4 

UNION ALL 

SELECT 
    MONTH(date) AS Month,
    col1,
    col2,
    0 col3,
    SUM(col4) col4,
    0 col5
FROM
    table2
WHERE
    date BETWEEN '2018-07-19' AND '2018-10-17'
GROUP BY 1 , 2 , 3 , 4

in MySQL Workbench that works good. But in django I see errors - I cannot do this like this:

result2 = table2.objects.\
        filter( date__range=( self.data['start_date'], self.data['end_date'] ) ).\
            annotate(month=TruncMonth('date')).\
        values("month", "col1", "col2", "0").\
            annotate( col4=Sum('col4'), col5=Sum(0))

Because "Cannot resolve keyword '0' into field."

Do you have any ideas for this one ?would like to create 2 the same objects and then use union() to merge tables

I am using django 1.11

Upvotes: 2

Views: 1370

Answers (1)

dirkgroten
dirkgroten

Reputation: 20702

If you want to add a column with a fixed value to your queryset, you can use Value expressions:

from django.db.models import Value, IntegerField

result2 = table2.objects.annotate(col5=Value(0, output_field=IntegerField())

Upvotes: 2

Related Questions