Python Django ORM：按额外属性过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4348124/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django ORM: Filter by extra attribute
提问by Danilo Bargen
I want to filter some database objects by a concatenated string.
我想通过连接的字符串过滤一些数据库对象。
The normal SQL query would be:
正常的 SQL 查询是：
SELECT concat(firstName, ' ', name) FROM person WHERE CONCAT(firstName, ' ', name) LIKE "a%";
In the model, I have created a manager called PersonObjects:
在模型中，我创建了一个名为 PersonObjects 的管理器：
class PersonObjects(Manager):
attrs = {
'fullName': "CONCAT(firstName, ' ', name)"
}
def get_query_set(self):
return super(PersonObjects, self).get_query_set().extra(
select=self.attrs)
I also configured this in my model:
我也在我的模型中配置了这个：
objects = managers.PersonObjects()
Now accessing fullName works for single objects:
现在访问 fullName 适用于单个对象：
>>> p = models.Person.objects.get(pk=4)
>>> p.fullName
u'Fred Borminski'
But it does not work in a filter:
但它在过滤器中不起作用：
>>> p = models.Person.objects.filter(fullName__startswith='Alexei')
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/usr/lib/python2.7/site-packages/django/db/models/manager.py", line 141, in filter
return self.get_query_set().filter(*args, **kwargs)
File "/usr/lib/python2.7/site-packages/django/db/models/query.py", line 550, in filter
return self._filter_or_exclude(False, *args, **kwargs)
File "/usr/lib/python2.7/site-packages/django/db/models/query.py", line 568, in _filter_or_exclude
clone.query.add_q(Q(*args, **kwargs))
File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1128, in add_q
can_reuse=used_aliases)
File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1026, in add_filter
negate=negate, process_extras=process_extras)
File "/usr/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1191, in setup_joins
"Choices are: %s" % (name, ", ".join(names)))
FieldError: Cannot resolve keyword 'fullName' into field. Choices are: firstName, gender, name, (...)
Is this a bug or a feature? How can I fix this?
这是错误还是功能？我怎样才能解决这个问题？
Thanks.
谢谢。
采纳答案by lqc
It's not a bug. filter()only inspects model definitions, so it doesn't recognize fullNameas a declared field (because it's not - it's an extra argument in a query).
这不是一个错误。filter()仅检查模型定义，因此它不会识别fullName为已声明的字段（因为它不是 - 它是查询中的额外参数）。
You can add the fullNameto WHEREusing extra():
您可以添加fullName到WHERE使用extra()：
Person.objects.extra(where=["fullName LIKE %s"], params=["Alexei%"])
回答by Bernd Jerzyna
I solved this by implementing a custom Aggregate function. In this case I needed to concatenate individual fields into a street address to be able to filter/search for matches. The following aggregate function allows to specify a field and one or more others to perform a SQL CONCAT_WS.
我通过实现自定义聚合函数解决了这个问题。在这种情况下，我需要将各个字段连接成一个街道地址，以便能够过滤/搜索匹配项。以下聚合函数允许指定一个字段和一个或多个其他字段来执行 SQL CONCAT_WS。
Edit 3 Aug 2015:
2015 年 8 月 3 日编辑：
A better implementation with details gleaned from https://stackoverflow.com/a/19529861/3230522. The previous implementation would fail if the queryset was used in a subquery. The table names are now correct, although I note that this just works for concatenation of columns from the same table.
从https://stackoverflow.com/a/19529861/3230522收集的细节更好的实现。如果在子查询中使用了查询集，则先前的实现将失败。表名现在是正确的，但我注意到这仅适用于连接同一个表中的列。
from django.db.models import Aggregate
from django.db.models.sql.aggregates import Aggregate as SQLAggregate
class SqlAggregate(SQLAggregate):
sql_function = 'CONCAT_WS'
sql_template = u'%(function)s(" ", %(field)s, %(columns_to_concatenate)s)'
def as_sql(self, qn, connection):
self.extra['columns_to_concatenate'] = ', '.join(
['.'.join([qn(self.col[0]), qn(c.strip())]) for c in self.extra['with_columns'].split(',')])
return super(SqlAggregate, self).as_sql(qn, connection)
class Concatenate(Aggregate):
sql = SqlAggregate
def __init__(self, expression, **extra):
super(Concatenate, self).__init__(
expression,
**extra)
def add_to_query(self, query, alias, col, source, is_summary):
aggregate = self.sql(col,
source=source,
is_summary=is_summary,
**self.extra)
query.aggregates[alias] = aggregate
回答by Reichert
The proposed solution worked great with postgresql and JSONB fields in the code below. Only records that have the 'partner' key under the 'key' jsonb field are returned:
建议的解决方案与下面代码中的 postgresql 和 JSONB 字段配合得很好。仅返回在 'key' jsonb 字段下具有 'partner' 键的记录：
query_partner = "select key->>'partner' from accounting_subaccount " \
"where accounting_subaccount.id = subaccount_id and key ? 'partner'"
qs = queryset.extra(select={'partner': query_partner}, where=["key ? 'partner'"])
