Python 连接两列后的Django查询集过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32559106/
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 queryset filter after a concatenation of two columns
提问by Virgil Balibanu
Is there any way to filter a model using a concatenation of two of its columns? My model is like this:
有没有办法使用连接的两列来过滤模型？我的模型是这样的：
class Item(models.Model):
series = models.CharField(max_length=50)
number = models.CharField(max_length=50)
What I need is to filter after the concatenation of the two columns, if a user inputs A123 I want to be able to find any Item that has series and number like %A and 123% or %A1 and 23% Is this possible using the django models? Or is it possible with raw sql? I would rather not construct a new column with the concatenation.
我需要的是在两列连接后进行过滤，如果用户输入 A123，我希望能够找到任何具有系列和编号的项目，例如 %A 和 123% 或 %A1 和 23% 这是否可能使用Django 模型？或者可以使用原始 sql 吗？我宁愿不使用串联构造一个新列。
采纳答案by mccc
Yes that is possible; you will need to annotatethe QuerySet with the concatenation of the fields, and that new "virtual" column will be capable of filtering.
是的，这是可能的；您将需要annotate连接字段的 QuerySet，并且新的“虚拟”列将能够进行过滤。
relevant documentation on filtering annotations
回答by bilabon
In addition to what was said earlier, example:
除了前面所说的，例如：
from django.db.models import Value
from django.db.models.functions import Concat
queryset = Item.objects.annotate(search_name=Concat('series', Value(' '), 'number'))
# then you can filter:
queryset.filter(search_name__icontains='whatever text')
回答by Don Kirkby
Here's a full example that shows how to filter based on the annotate()functionand a Concatexpression.
这是一个完整的示例，展示了如何根据annotate()函数和Concat表达式进行过滤。
# Tested with Django 1.9.2
import sys
import django
from django.apps import apps
from django.apps.config import AppConfig
from django.conf import settings
from django.db import connections, models, DEFAULT_DB_ALIAS
from django.db.models.base import ModelBase
from django.db.models.functions import Concat
NAME = 'udjango'
def main():
setup()
class Item(models.Model):
series = models.CharField(max_length=50)
number = models.CharField(max_length=50)
syncdb(Item)
Item.objects.create(series='A', number='1234')
Item.objects.create(series='A', number='1230')
Item.objects.create(series='A', number='9999')
Item.objects.create(series='B', number='1234')
print(Item.objects.annotate(
search=Concat('series', 'number')).filter(
search__icontains='A123').values_list('series', 'number'))
# >>> [(u'A', u'1234'), (u'A', u'1230')]
def setup():
DB_FILE = NAME + '.db'
with open(DB_FILE, 'w'):
pass # wipe the database
settings.configure(
DEBUG=True,
DATABASES={
DEFAULT_DB_ALIAS: {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': DB_FILE}},
LOGGING={'version': 1,
'disable_existing_loggers': False,
'formatters': {
'debug': {
'format': '%(asctime)s[%(levelname)s]'
'%(name)s.%(funcName)s(): %(message)s',
'datefmt': '%Y-%m-%d %H:%M:%S'}},
'handlers': {
'console': {
'level': 'DEBUG',
'class': 'logging.StreamHandler',
'formatter': 'debug'}},
'root': {
'handlers': ['console'],
'level': 'WARN'},
'loggers': {
"django.db": {"level": "WARN"}}})
app_config = AppConfig(NAME, sys.modules['__main__'])
apps.populate([app_config])
django.setup()
original_new_func = ModelBase.__new__
@staticmethod
def patched_new(cls, name, bases, attrs):
if 'Meta' not in attrs:
class Meta:
app_label = NAME
attrs['Meta'] = Meta
return original_new_func(cls, name, bases, attrs)
ModelBase.__new__ = patched_new
def syncdb(model):
""" Standard syncdb expects models to be in reliable locations.
Based on https://github.com/django/django/blob/1.9.3
/django/core/management/commands/migrate.py#L285
"""
connection = connections[DEFAULT_DB_ALIAS]
with connection.schema_editor() as editor:
editor.create_model(model)
main()
回答by ozan
I found my way, if you are gonna use some of ajax requests, then I started to use like this
我找到了我的方法，如果你要使用一些 ajax 请求，那么我就开始这样使用了
in views.py
在views.py中
AllUser = User.objects.all()
users = []
for i in AllUser:
if query in i.get_full_name():
users += [{'first_name':i.first_name,'last_name':i.last_name,'full_name':i.get_full_name()}]
qUser = users
and in returned ajax page (in my case 'searchajax.html')
并在返回的 ajax 页面中（在我的情况下为“searchajax.html”）
{% if qUser %}
{% for i in qUser %}
<p class="queryP">{{ i.full_name }}</p>
{% endfor %}
{% endif %}
it works very well for me :))
它对我很有效:))
Another way is using annotate
另一种方法是使用注释
from django.db.models import CharField, Value as V
from django.db.models.functions import Concat
author = User.objects.annotate(screen_name=Concat('first_name', V(' ') ,'last_name'))
for i in author:
print i.screen_name
it makes the same job too :))
它也做同样的工作:))
