时间戳

首页 > 解决方案 > 带有原始查询的 Django RawQuerySet 减法

django - 带有原始查询的 Django RawQuerySet 减法

问题描述

我在 postgres 中尝试这个查询它工作

select
  (SELECT sum(amount)
   FROM expense_expense
   WHERE flow='INFLOW')-
  (SELECT sum(amount)
   FROM expense_expense
   WHERE flow='OUTFLOW') AS balance;

走出去

balance|
-------|
6370.77|

但是当我尝试使用 Django RawQuerySet 它要求主键

In [168]: r = Expense.objects.raw("select(select sum(amount) FROM expense_expense  where flow='INFLOW') - (select sum(amount) FROM expense_expense  where flow='OUTFLOW') as balance;")                                                          

In [169]: r.columns                                                                                                                                                                                                                             
Out[169]: ['balance']

In [170]: r[0]                                                                    

---------------------------------------------------------------------------
InvalidQuery                              Traceback (most recent call last)
<ipython-input-170-8418cdc095ae> in <module>
----> 1 r[0]

~/Desktop/workspace/projects/python/django/expenditure/venv/lib/python3.7/site-packages/django/db/models/query.py in __getitem__(self, k)
   1433 
   1434     def __getitem__(self, k):
-> 1435         return list(self)[k]
   1436 
   1437     @property

~/Desktop/workspace/projects/python/django/expenditure/venv/lib/python3.7/site-packages/django/db/models/query.py in __iter__(self)
   1393 
   1394     def __iter__(self):
-> 1395         self._fetch_all()
   1396         return iter(self._result_cache)
   1397 

~/Desktop/workspace/projects/python/django/expenditure/venv/lib/python3.7/site-packages/django/db/models/query.py in _fetch_all(self)
   1380     def _fetch_all(self):
   1381         if self._result_cache is None:
-> 1382             self._result_cache = list(self.iterator())
   1383         if self._prefetch_related_lookups and not self._prefetch_done:
   1384             self._prefetch_related_objects()

~/Desktop/workspace/projects/python/django/expenditure/venv/lib/python3.7/site-packages/django/db/models/query.py in iterator(self)
   1408             model_init_names, model_init_pos, annotation_fields = self.resolve_model_init_order()
   1409             if self.model._meta.pk.attname not in model_init_names:
-> 1410                 raise InvalidQuery('Raw query must include the primary key')
   1411             model_cls = self.model
   1412             fields = [self.model_fields.get(c) for c in self.columns]

InvalidQuery: Raw query must include the primary key

In [171]:

有什么我遗漏或需要做的事情，请让我知道如何实现这一目标。这对我很有帮助。提前致谢。

标签： djangoorm

解决方案

您不能对此类聚合使用原始查询，因为.raw(..)查询 [Django-doc]用于检索模型对象：

raw()manager 方法可用于执行返回模型实例的原始 SQL 查询。

但根本没有必要首先使用原始查询。事实上，您应该避免这种情况，除非没有合理的选项可以使用 ORM。

from django.db.models import Q, Sum

Expense.objects.aggregate(
    balance=Sum('amount', filter=Q(flow='INFLOW'))-Sum('amount', filter=Q(flow='OUTFLOW'))
)

这将返回一个字典，其中的键名为'balance'映射到余额。

如果流数多于'INFLOW'和'OUTFLOW'，您可以通过首先过滤查询集来稍微提高性能：

from django.db.models import Q, Sum

Expense.objects.filter(
    flow__in=('INFLOW', 'OUTFLOW')
).aggregate(
    balance=Sum('amount', filter=Q(flow='INFLOW'))-Sum('amount', filter=Q(flow='OUTFLOW'))
)

推荐阅读