如何在Djang中注释/聚合字符串字段

2024-03-28 15:00:40 发布

您现在位置:Python中文网/ 问答频道 /正文

我有一个复杂的数据集,如果通过原始SQL进行源代码,则会涉及到许多联接,并且会执行一些奇怪的事情,例如在字符串字段上使用聚合器函数:

SELECT
    "trainer_trainer"."user_id",
    "trainer_trainer"."email",
    "trainer_trainer"."background",
    "trainer_trainer"."history",
    "trainer_trainer"."manualrating",
    COUNT("trainer_helperqueue"."id") AS "available",
    MAX("account_account"."photo") AS "photo",
    COUNT("trainer_trainersession"."id") AS "busy",
    MAX("account_account"."name") AS "name"
FROM
    "trainer_trainer"
LEFT OUTER JOIN
    "auth_user" ON ("trainer_trainer"."user_id" = "auth_user"."id")
LEFT OUTER JOIN
    "trainer_helperqueue" ON ("auth_user"."id" = "trainer_helperqueue"."user_id")
LEFT OUTER JOIN
    "account_account" ON ("auth_user"."id" = "account_account"."user_id")
LEFT OUTER JOIN
    "trainer_trainersession" ON ("auth_user"."id" = "trainer_trainersession"."helper_id")
GROUP BY
    "trainer_trainer"."user_id",
    "trainer_trainer"."email",
    "trainer_trainer"."background",
    "trainer_trainer"."history",
    "trainer_trainer"."manualrating",
    "trainer_trainer"."user_id",
    "trainer_trainer"."email",
    "trainer_trainer"."background",
    "trainer_trainer"."history",
    "trainer_trainer"."manualrating"

这个查询实际上运行得很好,但是Django在试图处理结果时会在内部崩溃:

^{pr2}$

以下是回溯的尾声:

PROJECTROOT/virtualenv/lib/python2.6/site-packages/django/db/models/sql/compiler.pyc in results_iter(self)
    705                         for (alias, aggregate), value
    706                         in zip(self.query.aggregate_select.items(), row[aggregate_start:aggregate_end])
    --> 707                     ]) + tuple(row[aggregate_end:])
    708 
    709                 yield row

PROJECTROOT/virtualenv/lib/python2.6/site-packages/django/db/models/sql/query.pyc in resolve_aggregate(self, value, aggregate, connection)
    320         else:
    321             # Return value depends on the type of the field being processed.

--> 322             return self.convert_values(value, aggregate.field, connection)
    323 
    324     def get_aggregation(self, using):

PROJECTROOT/virtualenv/lib/python2.6/site-packages/django/db/models/sql/query.pyc in convert_values(self, value, field, connection)
    298         it can be overridden by Query classes for specific backends.
    299         """
--> 300         return connection.ops.convert_values(value, field)
    301 
    302     def resolve_aggregate(self, value, aggregate, connection):

PROJECTROOT/virtualenv/lib/python2.6/site-packages/django/db/backends/__init__.pyc in convert_values(self, value, field)
    742         # No field, or the field isn't known to be a decimal or integer

    743         # Default to a float

--> 744         return float(value)
    745 
    746     def check_aggregate_support(self, aggregate_func):

ValueError:float()的文本无效:photos/4/8/7/2012012411455-487767.jpg

奇怪的是,SQL查询就在那里?它直接从db.connection.queries中提取。Django运行它,它在数据库级别工作,然后Django处理结果。在

我是不是应该不对非数值函数使用.annotate()?有更好/更聪明的方法吗?在


Tags: inselfauthidfielddbvalueas