如何在Django中使用UNION +添加伪列 [英] How to use UNION in Django + add fake columns

问题描述

我正在尝试在Django ORM中转换以下查询：

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

效果很好。但是在django中，我看到错误-我不能这样做：

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))

因为无法将关键字'0'解析为字段。

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

对这个有什么想法？想创建2个相同的对象，然后使用 union（）合并表

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

我正在使用django 1.11

I am using django 1.11

推荐答案

如果要向查询集添加固定值的列，可以使用 值表达式 ：

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())

这篇关于如何在Django中使用UNION +添加伪列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！