Django 子查询抛出 "more than one row returned by a subquery used as an expression"

Question

我最近升级到 django 1.11，我想使用新发布的 Subquery exprpession 功能。有以下两种型号

class Project(models.Model):
    title = models.CharField(_("Project Name"), max_length=128)

class PrivateDonation(models.Model):
    project = models.ForeignKey(Project)
    value = models.DecimalField(_("value"),max_digits=19, decimal_places=2)
    ............

我想要获取项目对象的查询集，并用每个项目的私有(private)捐赠值的总和进行注释。我知道，还有其他方法可以实现此目的，并且我已经为我的应用程序提供了一个可行的解决方案，但由于我正在学习此功能，因此我尝试了以下查询

private_donations = PrivateDonation.objects.filter(project=OuterRef('pk')).values('value')
private_donations_sum = private_donations.annotate(s=Sum('value')).values('s')
Project.objects.annotate(pd_sum=Subquery(queryset=private_donations_sum))

但是上面的查询抛出异常

django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression

唯一成功运行的查询是下面的查询。

private_donations = PrivateDonation.objects.filter(project=OuterRef('pk')).order_by().values('project')
private_donations_count = private_donations.annotate(c=Count('*')).values('c')
projects = Project.objects.annotate(space_count=Subquery(queryset=private_donations_count))

但是，如果我在“private_donations”查询中选择“project”属性以外的任何内容，则会引发相同的错误

这是堆栈跟踪

The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/models/query.py", line 226, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/models/query.py", line 250, in __iter__
    self._fetch_all()
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/models/query.py", line 1118, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 899, in execute_sql
    raise original_exception
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 889, in execute_sql
    cursor.execute(sql, params)
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/utils.py", line 94, in __exit__
DEBUG (0.002) SELECT "projects_project"."id", "projects_project"."created_at", "projects_project"."updated_at", "projects_project"."deleted_at", "projects_project"."is_deleted", "projects_project"."street", "projects_project"."additional_information", "projects_project"."postal_code", "projects_project"."city", "projects_project"."country", "projects_project"."title", "projects_project"."title_de", "projects_project"."title_en", "projects_project"."slug", "projects_project"."description", "projects_project"."description_de", "projects_project"."description_en", "projects_project"."summary", "projects_project"."summary_de", "projects_project"."summary_en", "projects_project"."description_photos_id", "projects_project"."category_id", "projects_project"."type", "projects_project"."financed_count", "projects_project"."max_financed_count", "projects_project"."ends_at", "projects_project"."status", "projects_project"."is_banned", "projects_project"."address", "projects_project"."longitude", "projects_project"."latitude", "projects_project"."created_by_id", "projects_project"."coin_account_id", "projects_project"."photos_id", "projects_project"."blog_id", "projects_project"."preview_token", "projects_project"."projectpromoter_id", "projects_project"."review_status", "projects_project"."review_rejected_message", "projects_project"."review_status_changed_at", "projects_project"."review_status_changed_by_id", "projects_project"."voting_review_status", "projects_project"."voting_review_rejected_message", "projects_project"."voting_review_status_changed_at", "projects_project"."voting_review_status_changed_by_id", "projects_project"."content_review_status", "projects_project"."content_rejected_message", "projects_project"."content_review_status_changed_at", "projects_project"."content_review_status_changed_by_id", "projects_project"."in_default_list", "projects_project"."custom_transfer_subject", "projects_project"."created_site_id", "projects_project"."statutory_purpose", (SELECT COUNT(*) AS "c" FROM "finances_privatedonation" U0 WHERE U0."project_id" = ("projects_project"."id") GROUP BY U0."created_at" ORDER BY U0."created_at" ASC) AS "space_count" FROM "projects_project" WHERE "projects_project"."is_deleted" = false ORDER BY "projects_project"."created_at" DESC LIMIT 21; args=(False,)    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/Users/sajidur/Coding/socialfunders/sf/venv3/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression

任何指导都将受到高度赞赏。提前致谢。

Answer 1

显然，我对 .values('project') 的含义理解不同/错误。

所以以下内容对我有用。

private_donations = PrivateDonation.objects.filter(project=OuterRef('pk')).order_by().values('project')
private_donations_sum = private_donations.annotate(pd_sum=Sum('value')).values('value')
projects = Project.objects.annotate(sum=Subquery(private_donations_sum))