Как слить два queryset запроса по одинаковому полю?

Question

[RainbowPesimist]

Вот код моего запросы

by_day = transactions_list.annotate(day=TruncDay('date_transaction',output_field=DateField())).filter(type=Transaction.Type.SALE)
date_sales_total = by_day.values('date_transaction',"score_id").annotate(total_sales=Sum('total'))
by_day = transactions_list.annotate(
       day=TruncDay('date_transaction', output_field=DateField())).filter(type=Transaction.Type.EXPENSE)
date_expenses_total = by_day.values('date_transaction',"score_id").annotate(total_expenses=Sum('total'))

Как выглядит результат двух запросов

date_sales_total >> <QuerySet [{'date_transaction': datetime.date(2019, 2, 10), 'score_id': 1, 'total_sales': 2000000.0}, {'date_transaction': datetime.date(2019, 11, 3), 'score_id': 1, 'total_sales': 1245432.0}]>
date_expenses_total >> <QuerySet [{'date_transaction': datetime.date(2019, 2, 10), 'score_id': 1, 'total_expenses': 500000.0}, {'date_transaction': datetime.date(2019, 12, 10), 'score_id': 1, 'total_expenses': 1214.0}]>

Что ожидается получить

<QuerySet [{'date_transaction': datetime.date(2019, 2, 10), 'score_id': 1, 'total_expenses': 500000.0, 'total_sales': 2000000.0}, {'date_transaction': datetime.date(2019, 11, 3), 'score_id': 1, 'total_sales': 1245432.0},{'date_transaction': datetime.date(2019, 12, 10), 'score_id': 1, 'total_expenses': 1214.0}]>

Answer 1

[RainbowPesimist]

Если кому понадобится, было такое решение

date_total = by_day.values('date_transaction', "score_id").\
        annotate(total_sales=Sum('total', filter=Q(type=Transaction.Type.SALE)),
                 total_expenses=Sum('total', filter=Q(type=Transaction.Type.EXPENSE)))

Answer 2

[soremix]

QuerySet.union

Comments

[RainbowPesimist]

Был такой вариант, но результат совсем отличается от ожидаемого

<QuerySet [{'date_transaction': datetime.date(2019, 2, 10), 'score_id': 1, 'total_sales': 500000.0}, {'date_transaction': datetime.date(2019, 2, 10), 'score_id': 1, 'total_sales': 2000000.0}, {'date_transaction': datetime.date(2019, 11, 3), 'score_id': 1, 'total_sales': 1245432.0}, {'date_transaction': datetime.date(2019, 12, 10), 'score_id': 1, 'total_sales': 1214.0}]>