title: “ django复杂查询\t\t”
tags:

  • django
    url: 1213.html
    id: 1213
    categories:
  • 后端
    date: 2018-06-18 13:27:09

介绍

django模型的objects对象中有filter、get等方法，其中可以“字段=XXX”的方式填入参数，最终构成AND关系的SQL Where子句，若需要OR或多种混合的复杂搜索需要使用Q对象。 除此以为，django还支持利用“”方式实现查找条件关键字、方式的多表连接查询。 所有查询相关的官方文档地址：查询简介、查询集完整API

查找关键字

exact 精确等于 like ‘aaa’
iexact 精确等于 忽略大小写 ilike ‘aaa’
contains 包含 like ‘%aaa%’
icontains 包含 忽略大小写 ilike ‘%aaa%’，但是对于sqlite来说，contains的作用效果等同于icontains。
gt 大于
gte 大于等于
lt 小于
lte 小于等于
in 存在于一个list范围内
startswith 以…开头
istartswith 以…开头 忽略大小写
endswith 以…结尾
iendswith 以…结尾，忽略大小写
range 在…范围内
year 日期字段的年份
month 日期字段的月份
day 日期字段的日
isnull=True/False

范例：filter(name__contains=’techie’)等效于WHERE name like ‘%techie%’

连表查询

class A(models.Model):
name = models.CharField()
class B(models.Model):
aa = models.ForeignKey(A)
B.objects.filter(aanamecontains=’test’)

通过B的外键找到A中name字段包含test内容的数据

class A(models.Model):
name = models.CharField()
class B(models.Model):
aa = models.ForeignKey(A,related_name=”FAN”)
bb = models.CharField()
A.objects.filter(FAN__bb=’XXXX’)

反向查询，查询出所有(B.aa=A且B.bb=XXXX)的A实例

Q对象使用

官方文档：https://docs.djangoproject.com/en/2.0/topics/db/queries/#complex-lookups-with-q-objects 引用django.db.models.Q Q对象与SQL语句对应关系如下：

Q(question=’Who’) | Q(question=’What’)
WHERE question = ‘Who’ OR question = ‘What’

Model.objects.get(
Q(question=’Who’),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6))
)
SELECT * from model WHERE question = ‘Who’ AND (pub_date = ‘2005-05-02’ OR pub_date = ‘2005-05-06’)

如果有比较复杂的关系，可以像使用容器一样构造Q对象： 单一类型条件查询：

q1 = Q()
q1.connector = ‘OR’
q1.children.append((‘id’, 1))
q1.children.append((‘id’, 2))
q1.children.append((‘id’, 3))

models.Tb1.objects.filter(q1)

符合类型条件查询：

con = Q()

q1 = Q()
q1.connector = ‘OR’
q1.children.append((‘id’, 1))
q1.children.append((‘id’, 2))
q1.children.append((‘id’, 3))

q2 = Q()
q2.connector = ‘OR’
q2.children.append((‘status’, ‘在线’))

con.add(q1, ‘AND’)
con.add(q2, ‘AND’)

models.Tb1.objects.filter(con)

Q对象官方使用范例

https://github.com/django/django/blob/master/tests/or_lookups/tests.py

from datetime import datetime
from operator import attrgetter

from django.db.models import Q
from django.test import TestCase

from .models import Article

class OrLookupsTests(TestCase):

  1. def setUp(self):
  2.     self.a1 = Article.objects.create(
  3.         headline='Hello', pub_date=datetime(2005, 11, 27)
  4.     ).pk
  5.     self.a2 = Article.objects.create(
  6.         headline='Goodbye', pub_date=datetime(2005, 11, 28)
  7.     ).pk
  8.     self.a3 = Article.objects.create(
  9.         headline='Hello and goodbye', pub_date=datetime(2005, 11, 29)
  10.     ).pk
  12. def test\_filter\_or(self):
  13.     self.assertQuerysetEqual(
  14.         (
  15.             Article.objects.filter(headline__startswith='Hello') |
  16.             Article.objects.filter(headline__startswith='Goodbye')
  17.         ), \[
  18.             'Hello',
  19.             'Goodbye',
  20.             'Hello and goodbye'
  21.         \],
  22.         attrgetter("headline")
  23.     )
  25.     self.assertQuerysetEqual(
  26.         Article.objects.filter(headline\_\_contains='Hello') | Article.objects.filter(headline\_\_contains='bye'), \[
  27.             'Hello',
  28.             'Goodbye',
  29.             'Hello and goodbye'
  30.         \],
  31.         attrgetter("headline")
  32.     )
  34.     self.assertQuerysetEqual(
  35.         Article.objects.filter(headline\_\_iexact='Hello') | Article.objects.filter(headline\_\_contains='ood'), \[
  36.             'Hello',
  37.             'Goodbye',
  38.             'Hello and goodbye'
  39.         \],
  40.         attrgetter("headline")
  41.     )
  43.     self.assertQuerysetEqual(
  44.         Article.objects.filter(Q(headline\_\_startswith='Hello') | Q(headline\_\_startswith='Goodbye')), \[
  45.             'Hello',
  46.             'Goodbye',
  47.             'Hello and goodbye'
  48.         \],
  49.         attrgetter("headline")
  50.     )
  52. def test_stages(self):
  53.     # You can shorten this syntax with code like the following,  which is
  54.     # especially useful if building the query in stages:
  55.     articles = Article.objects.all()
  56.     self.assertQuerysetEqual(
  57.         articles.filter(headline\_\_startswith='Hello') & articles.filter(headline\_\_startswith='Goodbye'),
  58.         \[\]
  59.     )
  60.     self.assertQuerysetEqual(
  61.         articles.filter(headline\_\_startswith='Hello') & articles.filter(headline\_\_contains='bye'), \[
  62.             'Hello and goodbye'
  63.         \],
  64.         attrgetter("headline")
  65.     )
  67. def test\_pk\_q(self):
  68.     self.assertQuerysetEqual(
  69.         Article.objects.filter(Q(pk=self.a1) | Q(pk=self.a2)), \[
  70.             'Hello',
  71.             'Goodbye'
  72.         \],
  73.         attrgetter("headline")
  74.     )
  76.     self.assertQuerysetEqual(
  77.         Article.objects.filter(Q(pk=self.a1) | Q(pk=self.a2) | Q(pk=self.a3)), \[
  78.             'Hello',
  79.             'Goodbye',
  80.             'Hello and goodbye'
  81.         \],
  82.         attrgetter("headline"),
  83.     )
  85. def test\_pk\_in(self):
  86.     self.assertQuerysetEqual(
  87.         Article.objects.filter(pk__in=\[self.a1, self.a2, self.a3\]), \[
  88.             'Hello',
  89.             'Goodbye',
  90.             'Hello and goodbye'
  91.         \],
  92.         attrgetter("headline"),
  93.     )
  95.     self.assertQuerysetEqual(
  96.         Article.objects.filter(pk__in=(self.a1, self.a2, self.a3)), \[
  97.             'Hello',
  98.             'Goodbye',
  99.             'Hello and goodbye'
  100.         \],
  101.         attrgetter("headline"),
  102.     )
  104.     self.assertQuerysetEqual(
  105.         Article.objects.filter(pk__in=\[self.a1, self.a2, self.a3, 40000\]), \[
  106.             'Hello',
  107.             'Goodbye',
  108.             'Hello and goodbye'
  109.         \],
  110.         attrgetter("headline"),
  111.     )
  113. def test\_q\_repr(self):
  114.     or_expr = Q(baz=Article(headline="Fo?"))
  115.     self.assertEqual(repr(or_expr), "<Q: (AND: ('baz', <Article: Fo?>))>")
  116.     negated_or = ~Q(baz=Article(headline="Fo?"))
  117.     self.assertEqual(repr(negated_or), "<Q: (NOT (AND: ('baz', <Article: Fo?>)))>")
  119. def test\_q\_negated(self):
  120.     # Q objects can be negated
  121.     self.assertQuerysetEqual(
  122.         Article.objects.filter(Q(pk=self.a1) | ~Q(pk=self.a2)), \[
  123.             'Hello',
  124.             'Hello and goodbye'
  125.         \],
  126.         attrgetter("headline")
  127.     )
  129.     self.assertQuerysetEqual(
  130.         Article.objects.filter(~Q(pk=self.a1) & ~Q(pk=self.a2)), \[
  131.             'Hello and goodbye'
  132.         \],
  133.         attrgetter("headline"),
  134.     )
  135.     # This allows for more complex queries than filter() and exclude()
  136.     # alone would allow
  137.     self.assertQuerysetEqual(
  138.         Article.objects.filter(Q(pk=self.a1) & (~Q(pk=self.a2) | Q(pk=self.a3))), \[
  139.             'Hello'
  140.         \],
  141.         attrgetter("headline"),
  142.     )
  144. def test\_complex\_filter(self):
  145.     # The 'complex_filter' method supports framework features such as
  146.     # 'limit\_choices\_to' which normally take a single dictionary of lookup
  147.     # arguments but need to support arbitrary queries via Q objects too.
  148.     self.assertQuerysetEqual(
  149.         Article.objects.complex_filter({'pk': self.a1}), \[
  150.             'Hello'
  151.         \],
  152.         attrgetter("headline"),
  153.     )
  155.     self.assertQuerysetEqual(
  156.         Article.objects.complex_filter(Q(pk=self.a1) | Q(pk=self.a2)), \[
  157.             'Hello',
  158.             'Goodbye'
  159.         \],
  160.         attrgetter("headline"),
  161.     )
  163. def test\_empty\_in(self):
  164.     # Passing "in" an empty list returns no results ...
  165.     self.assertQuerysetEqual(
  166.         Article.objects.filter(pk__in=\[\]),
  167.         \[\]
  168.     )
  169.     # ... but can return results if we OR it with another query.
  170.     self.assertQuerysetEqual(
  171.         Article.objects.filter(Q(pk\_\_in=\[\]) | Q(headline\_\_icontains='goodbye')), \[
  172.             'Goodbye',
  173.             'Hello and goodbye'
  174.         \],
  175.         attrgetter("headline"),
  176.     )
  178. def test\_q\_and(self):
  179.     # Q arg objects are ANDed
  180.     self.assertQuerysetEqual(
  181.         Article.objects.filter(Q(headline\_\_startswith='Hello'), Q(headline\_\_contains='bye')), \[
  182.             'Hello and goodbye'
  183.         \],
  184.         attrgetter("headline")
  185.     )
  186.     # Q arg AND order is irrelevant
  187.     self.assertQuerysetEqual(
  188.         Article.objects.filter(Q(headline\_\_contains='bye'), headline\_\_startswith='Hello'), \[
  189.             'Hello and goodbye'
  190.         \],
  191.         attrgetter("headline"),
  192.     )
  194.     self.assertQuerysetEqual(
  195.         Article.objects.filter(Q(headline\_\_startswith='Hello') & Q(headline\_\_startswith='Goodbye')),
  196.         \[\]
  197.     )
  199. def test\_q\_exclude(self):
  200.     self.assertQuerysetEqual(
  201.         Article.objects.exclude(Q(headline__startswith='Hello')), \[
  202.             'Goodbye'
  203.         \],
  204.         attrgetter("headline")
  205.     )
  207. def test\_other\_arg_queries(self):
  208.     # Try some arg queries with operations other than filter.
  209.     self.assertEqual(
  210.         Article.objects.get(Q(headline\_\_startswith='Hello'), Q(headline\_\_contains='bye')).headline,
  211.         'Hello and goodbye'
  212.     )
  214.     self.assertEqual(
  215.         Article.objects.filter(Q(headline\_\_startswith='Hello') | Q(headline\_\_contains='bye')).count(),
  216.         3
  217.     )
  219.     self.assertSequenceEqual(
  220.         Article.objects.filter(Q(headline\_\_startswith='Hello'), Q(headline\_\_contains='bye')).values(), \[
  221.             {"headline": "Hello and goodbye", "id": self.a3, "pub_date": datetime(2005, 11, 29)},
  222.         \],
  223.     )
  225.     self.assertEqual(
  226.         Article.objects.filter(Q(headline\_\_startswith='Hello')).in\_bulk(\[self.a1, self.a2\]),
  227.         {self.a1: Article.objects.get(pk=self.a1)}

)

F对象使用

官方文档：https://docs.djangoproject.com/en/2.0/ref/models/expressions/#f-expressions 有关聚合、分组、F查询可看博客：django-聚合、分组、F查询和Q查询、总结