Django ORM:按额外属性过滤 [英] Django ORM: Filter by extra attribute

问题描述

我想通过连接的字符串过滤一些数据库对象.

正常的 SQL 查询是:

SELECT concat(firstName, ' ', name) FROM person WHERE CONCAT(firstName, ' ', name) LIKE "a%";

在模型中，我创建了一个名为 PersonObjects 的管理器:

class PersonObjects(Manager):属性 = {'fullName': "CONCAT(firstName, ' ', name)"}def get_query_set(self):返回 super(PersonObjects, self).get_query_set().extra(选择=self.attrs)

我也在我的模型中配置了这个:

objects = manager.PersonObjects()

现在访问 fullName 适用于单个对象:

<预><代码>>>>p = 模型.Person.objects.get(pk=4)>>>全名u'Fred Borminski'

但它在过滤器中不起作用:

<预><代码>>>>p = models.Person.objects.filter(fullName__startswith='Alexei')回溯(最近一次调用最后一次):文件<console>"，第 1 行，在 <module> 中过滤器中的文件/usr/lib/python2.7/site-packages/django/db/models/manager.py"，第 141 行返回 self.get_query_set().filter(*args, **kwargs)过滤器中的文件/usr/lib/python2.7/site-packages/django/db/models/query.py"，第 550 行返回 self._filter_or_exclude(False, *args, **kwargs)文件/usr/lib/python2.7/site-packages/django/db/models/query.py"，第 568 行，在 _filter_or_excludeclone.query.add_q(Q(*args, **kwargs))文件/usr/lib/python2.7/site-packages/django/db/models/sql/query.py"，第 1128 行，在 add_qcan_reuse=used_aliases)文件/usr/lib/python2.7/site-packages/django/db/models/sql/query.py"，第 1026 行，在 add_filter 中否定=否定，process_extras=process_extras)setup_joins 中的文件/usr/lib/python2.7/site-packages/django/db/models/sql/query.py"，第 1191 行"选择是: %s" % (name, ", ".join(names)))FieldError:无法将关键字fullName"解析为字段.选项有:名字、性别、姓名、(...)

这是错误还是功能?我该如何解决这个问题?

谢谢.

解决方案

这不是错误.filter() 仅检查模型定义，因此它不会将 fullName 识别为声明的字段(因为它不是 - 它是查询中的额外参数).

您可以使用 extra() 将 fullName 添加到 WHERE:

Person.objects.extra(where=["fullName LIKE %s"], params=["Alexei%"])

I want to filter some database objects by a concatenated string.

The normal SQL query would be:

SELECT concat(firstName, ' ', name) FROM person WHERE CONCAT(firstName, ' ', name) LIKE "a%";

In the model, I have created a manager called 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:

>>> 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.

解决方案

It's not a bug. filter() only inspects model definitions, so it doesn't recognize fullName as a declared field (because it's not - it's an extra argument in a query).

You can add the fullName to WHERE using extra():

Person.objects.extra(where=["fullName LIKE %s"], params=["Alexei%"])

这篇关于Django ORM:按额外属性过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！