2
Various complex queries that have been problematic in the past.
9
from django.conf import settings
10
from django.db import models
11
from django.db.models.query import Q, ITER_CHUNK_SIZE
13
# Python 2.3 doesn't have sorted()
17
from django.utils.itercompat import sorted
19
class DumbCategory(models.Model):
22
class NamedCategory(DumbCategory):
23
name = models.CharField(max_length=10)
25
class Tag(models.Model):
26
name = models.CharField(max_length=10)
27
parent = models.ForeignKey('self', blank=True, null=True,
28
related_name='children')
29
category = models.ForeignKey(NamedCategory, null=True, default=None)
34
def __unicode__(self):
37
class Note(models.Model):
38
note = models.CharField(max_length=100)
39
misc = models.CharField(max_length=10)
44
def __unicode__(self):
47
class Annotation(models.Model):
48
name = models.CharField(max_length=10)
49
tag = models.ForeignKey(Tag)
50
notes = models.ManyToManyField(Note)
52
def __unicode__(self):
55
class ExtraInfo(models.Model):
56
info = models.CharField(max_length=100)
57
note = models.ForeignKey(Note)
62
def __unicode__(self):
65
class Author(models.Model):
66
name = models.CharField(max_length=10)
67
num = models.IntegerField(unique=True)
68
extra = models.ForeignKey(ExtraInfo)
70
def __unicode__(self):
73
class Item(models.Model):
74
name = models.CharField(max_length=10)
75
created = models.DateTimeField()
76
modified = models.DateTimeField(blank=True, null=True)
77
tags = models.ManyToManyField(Tag, blank=True, null=True)
78
creator = models.ForeignKey(Author)
79
note = models.ForeignKey(Note)
82
ordering = ['-note', 'name']
84
def __unicode__(self):
87
class Report(models.Model):
88
name = models.CharField(max_length=10)
89
creator = models.ForeignKey(Author, to_field='num', null=True)
91
def __unicode__(self):
94
class Ranking(models.Model):
95
rank = models.IntegerField()
96
author = models.ForeignKey(Author)
99
# A complex ordering specification. Should stress the system a bit.
100
ordering = ('author__extra__note', 'author__name', 'rank')
102
def __unicode__(self):
103
return '%d: %s' % (self.rank, self.author.name)
105
class Cover(models.Model):
106
title = models.CharField(max_length=50)
107
item = models.ForeignKey(Item)
112
def __unicode__(self):
115
class Number(models.Model):
116
num = models.IntegerField()
118
def __unicode__(self):
119
return unicode(self.num)
121
# Symmetrical m2m field with a normal field using the reverse accesor name
123
class Valid(models.Model):
124
valid = models.CharField(max_length=10)
125
parent = models.ManyToManyField('self')
130
# Some funky cross-linked models for testing a couple of infinite recursion
132
class X(models.Model):
133
y = models.ForeignKey('Y')
135
class Y(models.Model):
136
x1 = models.ForeignKey(X, related_name='y1')
138
# Some models with a cycle in the default ordering. This would be bad if we
139
# didn't catch the infinite loop.
140
class LoopX(models.Model):
141
y = models.ForeignKey('LoopY')
146
class LoopY(models.Model):
147
x = models.ForeignKey(LoopX)
152
class LoopZ(models.Model):
153
z = models.ForeignKey('self')
158
# A model and custom default manager combination.
159
class CustomManager(models.Manager):
160
def get_query_set(self):
161
qs = super(CustomManager, self).get_query_set()
162
return qs.filter(public=True, tag__name='t1')
164
class ManagedModel(models.Model):
165
data = models.CharField(max_length=10)
166
tag = models.ForeignKey(Tag)
167
public = models.BooleanField(default=True)
169
objects = CustomManager()
170
normal_manager = models.Manager()
172
def __unicode__(self):
175
# An inter-related setup with multiple paths from Child to Detail.
176
class Detail(models.Model):
177
data = models.CharField(max_length=10)
179
class MemberManager(models.Manager):
180
def get_query_set(self):
181
return super(MemberManager, self).get_query_set().select_related("details")
183
class Member(models.Model):
184
name = models.CharField(max_length=10)
185
details = models.OneToOneField(Detail, primary_key=True)
187
objects = MemberManager()
189
class Child(models.Model):
190
person = models.OneToOneField(Member, primary_key=True)
191
parent = models.ForeignKey(Member, related_name="children")
193
# Custom primary keys interfered with ordering in the past.
194
class CustomPk(models.Model):
195
name = models.CharField(max_length=10, primary_key=True)
196
extra = models.CharField(max_length=10)
199
ordering = ['name', 'extra']
201
class Related(models.Model):
202
custom = models.ForeignKey(CustomPk)
204
# An inter-related setup with a model subclass that has a nullable
205
# path to another model, and a return path from that model.
207
class Celebrity(models.Model):
208
name = models.CharField("Name", max_length=20)
209
greatest_fan = models.ForeignKey("Fan", null=True, unique=True)
211
class TvChef(Celebrity):
214
class Fan(models.Model):
215
fan_of = models.ForeignKey(Celebrity)
217
# Multiple foreign keys
218
class LeafA(models.Model):
219
data = models.CharField(max_length=10)
221
def __unicode__(self):
224
class LeafB(models.Model):
225
data = models.CharField(max_length=10)
227
class Join(models.Model):
228
a = models.ForeignKey(LeafA)
229
b = models.ForeignKey(LeafB)
231
class ReservedName(models.Model):
232
name = models.CharField(max_length=20)
233
order = models.IntegerField()
235
def __unicode__(self):
238
# A simpler shared-foreign-key setup that can expose some problems.
239
class SharedConnection(models.Model):
240
data = models.CharField(max_length=10)
242
class PointerA(models.Model):
243
connection = models.ForeignKey(SharedConnection)
245
class PointerB(models.Model):
246
connection = models.ForeignKey(SharedConnection)
248
# Multi-layer ordering
249
class SingleObject(models.Model):
250
name = models.CharField(max_length=10)
255
def __unicode__(self):
258
class RelatedObject(models.Model):
259
single = models.ForeignKey(SingleObject)
262
ordering = ['single']
264
class Plaything(models.Model):
265
name = models.CharField(max_length=10)
266
others = models.ForeignKey(RelatedObject, null=True)
269
ordering = ['others']
271
def __unicode__(self):
275
__test__ = {'API_TESTS':"""
276
>>> generic = NamedCategory.objects.create(name="Generic")
277
>>> t1 = Tag.objects.create(name='t1', category=generic)
278
>>> t2 = Tag.objects.create(name='t2', parent=t1, category=generic)
279
>>> t3 = Tag.objects.create(name='t3', parent=t1)
280
>>> t4 = Tag.objects.create(name='t4', parent=t3)
281
>>> t5 = Tag.objects.create(name='t5', parent=t3)
283
>>> n1 = Note.objects.create(note='n1', misc='foo')
284
>>> n2 = Note.objects.create(note='n2', misc='bar')
285
>>> n3 = Note.objects.create(note='n3', misc='foo')
287
>>> ann1 = Annotation.objects.create(name='a1', tag=t1)
288
>>> ann1.notes.add(n1)
289
>>> ann2 = Annotation.objects.create(name='a2', tag=t4)
290
>>> ann2.notes.add(n2, n3)
292
Create these out of order so that sorting by 'id' will be different to sorting
293
by 'info'. Helps detect some problems later.
294
>>> e2 = ExtraInfo.objects.create(info='e2', note=n2)
295
>>> e1 = ExtraInfo.objects.create(info='e1', note=n1)
297
>>> a1 = Author.objects.create(name='a1', num=1001, extra=e1)
298
>>> a2 = Author.objects.create(name='a2', num=2002, extra=e1)
299
>>> a3 = Author.objects.create(name='a3', num=3003, extra=e2)
300
>>> a4 = Author.objects.create(name='a4', num=4004, extra=e2)
302
>>> time1 = datetime.datetime(2007, 12, 19, 22, 25, 0)
303
>>> time2 = datetime.datetime(2007, 12, 19, 21, 0, 0)
304
>>> time3 = datetime.datetime(2007, 12, 20, 22, 25, 0)
305
>>> time4 = datetime.datetime(2007, 12, 20, 21, 0, 0)
306
>>> i1 = Item.objects.create(name='one', created=time1, modified=time1, creator=a1, note=n3)
307
>>> i1.tags = [t1, t2]
308
>>> i2 = Item.objects.create(name='two', created=time2, creator=a2, note=n2)
309
>>> i2.tags = [t1, t3]
310
>>> i3 = Item.objects.create(name='three', created=time3, creator=a2, note=n3)
311
>>> i4 = Item.objects.create(name='four', created=time4, creator=a4, note=n3)
314
>>> r1 = Report.objects.create(name='r1', creator=a1)
315
>>> r2 = Report.objects.create(name='r2', creator=a3)
316
>>> r3 = Report.objects.create(name='r3')
318
Ordering by 'rank' gives us rank2, rank1, rank3. Ordering by the Meta.ordering
319
will be rank3, rank2, rank1.
320
>>> rank1 = Ranking.objects.create(rank=2, author=a2)
321
>>> rank2 = Ranking.objects.create(rank=1, author=a3)
322
>>> rank3 = Ranking.objects.create(rank=3, author=a1)
324
>>> c1 = Cover.objects.create(title="first", item=i4)
325
>>> c2 = Cover.objects.create(title="second", item=i2)
327
>>> num1 = Number.objects.create(num=4)
328
>>> num2 = Number.objects.create(num=8)
329
>>> num3 = Number.objects.create(num=12)
332
>>> Item.objects.filter(tags__isnull=True)
334
>>> Item.objects.filter(tags__id__isnull=True)
338
>>> Author.objects.filter(item=i2)
340
>>> Author.objects.filter(item=i3)
342
>>> Author.objects.filter(item=i2) & Author.objects.filter(item=i3)
346
Checking that no join types are "left outer" joins.
347
>>> query = Item.objects.filter(tags=t2).query
348
>>> query.LOUTER not in [x[2] for x in query.alias_map.values()]
351
>>> Item.objects.filter(Q(tags=t1)).order_by('name')
352
[<Item: one>, <Item: two>]
353
>>> Item.objects.filter(Q(tags=t1)).filter(Q(tags=t2))
355
>>> Item.objects.filter(Q(tags=t1)).filter(Q(creator__name='fred')|Q(tags=t2))
358
Each filter call is processed "at once" against a single table, so this is
359
different from the previous example as it tries to find tags that are two
360
things at once (rather than two tags).
361
>>> Item.objects.filter(Q(tags=t1) & Q(tags=t2))
363
>>> Item.objects.filter(Q(tags=t1), Q(creator__name='fred')|Q(tags=t2))
366
>>> qs = Author.objects.filter(ranking__rank=2, ranking__id=rank1.id)
369
>>> qs.query.count_active_tables()
371
>>> qs = Author.objects.filter(ranking__rank=2).filter(ranking__id=rank1.id)
372
>>> qs.query.count_active_tables()
376
>>> Item.objects.filter(tags=t1).filter(tags=t2)
378
>>> Item.objects.filter(tags__in=[t1, t2]).distinct().order_by('name')
379
[<Item: one>, <Item: two>]
380
>>> Item.objects.filter(tags__in=[t1, t2]).filter(tags=t3)
383
Make sure .distinct() works with slicing (this was broken in Oracle).
384
>>> Item.objects.filter(tags__in=[t1, t2]).order_by('name')[:3]
385
[<Item: one>, <Item: one>, <Item: two>]
386
>>> Item.objects.filter(tags__in=[t1, t2]).distinct().order_by('name')[:3]
387
[<Item: one>, <Item: two>]
390
>>> Author.objects.filter(item__name='one') | Author.objects.filter(name='a3')
391
[<Author: a1>, <Author: a3>]
392
>>> Author.objects.filter(Q(item__name='one') | Q(name='a3'))
393
[<Author: a1>, <Author: a3>]
394
>>> Author.objects.filter(Q(name='a3') | Q(item__name='one'))
395
[<Author: a1>, <Author: a3>]
396
>>> Author.objects.filter(Q(item__name='three') | Q(report__name='r3'))
400
A slight variation on the above theme: restricting the choices by the lookup
402
>>> Number.objects.filter(num__lt=4)
404
>>> Number.objects.filter(num__gt=8, num__lt=12)
406
>>> Number.objects.filter(num__gt=8, num__lt=13)
408
>>> Number.objects.filter(Q(num__lt=4) | Q(num__gt=8, num__lt=12))
410
>>> Number.objects.filter(Q(num__gt=8, num__lt=12) | Q(num__lt=4))
412
>>> Number.objects.filter(Q(num__gt=8) & Q(num__lt=12) | Q(num__lt=4))
414
>>> Number.objects.filter(Q(num__gt=7) & Q(num__lt=12) | Q(num__lt=4))
418
Another variation on the disjunctive filtering theme.
420
# For the purposes of this regression test, it's important that there is no
421
# Join object releated to the LeafA we create.
422
>>> LeafA.objects.create(data='first')
424
>>> LeafA.objects.filter(Q(data='first')|Q(join__b__data='second'))
428
Merging two empty result sets shouldn't leave a queryset with no constraints
429
(which would match everything).
430
>>> Author.objects.filter(Q(id__in=[]))
432
>>> Author.objects.filter(Q(id__in=[])|Q(id__in=[]))
436
>>> Item.objects.values('creator').distinct().count()
439
# Create something with a duplicate 'name' so that we can test multi-column
440
# cases (which require some tricky SQL transformations under the covers).
441
>>> xx = Item(name='four', created=time1, creator=a2, note=n1)
443
>>> Item.objects.exclude(name='two').values('creator', 'name').distinct().count()
445
>>> Item.objects.exclude(name='two').extra(select={'foo': '%s'}, select_params=(1,)).values('creator', 'name', 'foo').distinct().count()
447
>>> Item.objects.exclude(name='two').extra(select={'foo': '%s'}, select_params=(1,)).values('creator', 'name').distinct().count()
452
>>> Item.objects.values('creator', 'name').count()
456
>>> q1 = Item.objects.order_by('name')
457
>>> q2 = Item.objects.filter(id=i1.id)
459
[<Item: four>, <Item: one>, <Item: three>, <Item: two>]
462
>>> (q1 | q2).order_by('name')
463
[<Item: four>, <Item: one>, <Item: three>, <Item: two>]
464
>>> (q1 & q2).order_by('name')
467
# FIXME: This is difficult to fix and very much an edge case, so punt for now.
468
# # This is related to the order_by() tests, below, but the old bug exhibited
469
# # itself here (q2 was pulling too many tables into the combined query with the
470
# # new ordering, but only because we have evaluated q2 already).
471
# >>> len((q1 & q2).order_by('name').query.tables)
474
>>> q1 = Item.objects.filter(tags=t1)
475
>>> q2 = Item.objects.filter(note=n3, tags=t2)
476
>>> q3 = Item.objects.filter(creator=a4)
477
>>> ((q1 & q2) | q3).order_by('name')
478
[<Item: four>, <Item: one>]
481
>>> Report.objects.filter(creator=1001)
483
>>> Report.objects.filter(creator__num=1001)
485
>>> Report.objects.filter(creator__id=1001)
487
>>> Report.objects.filter(creator__id=a1.id)
489
>>> Report.objects.filter(creator__name='a1')
493
>>> Author.objects.filter(report__name='r1')
497
>>> a1.report_set.all()
501
>>> Item.objects.filter(tags__name='t4')
503
>>> Item.objects.exclude(tags__name='t4').order_by('name').distinct()
504
[<Item: one>, <Item: three>, <Item: two>]
505
>>> Item.objects.exclude(tags__name='t4').order_by('name').distinct().reverse()
506
[<Item: two>, <Item: three>, <Item: one>]
507
>>> Author.objects.exclude(item__name='one').distinct().order_by('name')
508
[<Author: a2>, <Author: a3>, <Author: a4>]
511
# Excluding across a m2m relation when there is more than one related object
512
# associated was problematic.
513
>>> Item.objects.exclude(tags__name='t1').order_by('name')
514
[<Item: four>, <Item: three>]
515
>>> Item.objects.exclude(tags__name='t1').exclude(tags__name='t4')
518
# Excluding from a relation that cannot be NULL should not use outer joins.
519
>>> query = Item.objects.exclude(creator__in=[a1, a2]).query
520
>>> query.LOUTER not in [x[2] for x in query.alias_map.values()]
523
Similarly, when one of the joins cannot possibly, ever, involve NULL values (Author -> ExtraInfo, in the following), it should never be promoted to a left outer join. So the following query should only involve one "left outer" join (Author -> Item is 0-to-many).
524
>>> qs = Author.objects.filter(id=a1.id).filter(Q(extra__note=n1)|Q(item__note=n3))
525
>>> len([x[2] for x in qs.query.alias_map.values() if x[2] == query.LOUTER and qs.query.alias_refcount[x[1]]])
528
The previous changes shouldn't affect nullable foreign key joins.
529
>>> Tag.objects.filter(parent__isnull=True).order_by('name')
531
>>> Tag.objects.exclude(parent__isnull=True).order_by('name')
532
[<Tag: t2>, <Tag: t3>, <Tag: t4>, <Tag: t5>]
533
>>> Tag.objects.exclude(Q(parent__name='t1') | Q(parent__isnull=True)).order_by('name')
534
[<Tag: t4>, <Tag: t5>]
535
>>> Tag.objects.exclude(Q(parent__isnull=True) | Q(parent__name='t1')).order_by('name')
536
[<Tag: t4>, <Tag: t5>]
537
>>> Tag.objects.exclude(Q(parent__parent__isnull=True)).order_by('name')
538
[<Tag: t4>, <Tag: t5>]
539
>>> Tag.objects.filter(~Q(parent__parent__isnull=True)).order_by('name')
540
[<Tag: t4>, <Tag: t5>]
543
>>> t = Tag.objects.get(name='t4')
544
>>> Item.objects.filter(tags__in=[t])
547
Combining querysets built on different models should behave in a well-defined
548
fashion. We raise an error.
549
>>> Author.objects.all() & Tag.objects.all()
550
Traceback (most recent call last):
552
AssertionError: Cannot combine queries on two different base models.
553
>>> Author.objects.all() | Tag.objects.all()
554
Traceback (most recent call last):
556
AssertionError: Cannot combine queries on two different base models.
559
>>> Author.objects.extra(select={'foo': '1'}).count()
561
>>> Author.objects.extra(select={'foo': '%s'}, select_params=(1,)).count()
565
>>> Author.objects.filter(item__isnull=True)
567
>>> Tag.objects.filter(item__isnull=True)
571
>>> Item.objects.extra(tables=['queries_author']).select_related().order_by('name')[:1]
575
# Ordering on related tables should be possible, even if the table is not
576
# otherwise involved.
577
>>> Item.objects.order_by('note__note', 'name')
578
[<Item: two>, <Item: four>, <Item: one>, <Item: three>]
580
# Ordering on a related field should use the remote model's default ordering as
582
>>> Author.objects.order_by('extra', '-name')
583
[<Author: a2>, <Author: a1>, <Author: a4>, <Author: a3>]
585
# Using remote model default ordering can span multiple models (in this case,
586
# Cover is ordered by Item's default, which uses Note's default).
587
>>> Cover.objects.all()
588
[<Cover: first>, <Cover: second>]
590
# If the remote model does not have a default ordering, we order by its 'id'
592
>>> Item.objects.order_by('creator', 'name')
593
[<Item: one>, <Item: three>, <Item: two>, <Item: four>]
595
# Cross model ordering is possible in Meta, too.
596
>>> Ranking.objects.all()
597
[<Ranking: 3: a1>, <Ranking: 2: a2>, <Ranking: 1: a3>]
598
>>> Ranking.objects.all().order_by('rank')
599
[<Ranking: 1: a3>, <Ranking: 2: a2>, <Ranking: 3: a1>]
601
# Ordering by a many-valued attribute (e.g. a many-to-many or reverse
602
# ForeignKey) is legal, but the results might not make sense. That isn't
603
# Django's problem. Garbage in, garbage out.
604
>>> Item.objects.filter(tags__isnull=False).order_by('tags', 'id')
605
[<Item: one>, <Item: two>, <Item: one>, <Item: two>, <Item: four>]
607
# If we replace the default ordering, Django adjusts the required tables
608
# automatically. Item normally requires a join with Note to do the default
609
# ordering, but that isn't needed here.
610
>>> qs = Item.objects.order_by('name')
612
[<Item: four>, <Item: one>, <Item: three>, <Item: two>]
613
>>> len(qs.query.tables)
616
# Ordering of extra() pieces is possible, too and you can mix extra fields and
617
# model fields in the ordering.
618
>>> Ranking.objects.extra(tables=['django_site'], order_by=['-django_site.id', 'rank'])
619
[<Ranking: 1: a3>, <Ranking: 2: a2>, <Ranking: 3: a1>]
621
>>> qs = Ranking.objects.extra(select={'good': 'case when rank > 2 then 1 else 0 end'})
622
>>> [o.good for o in qs.extra(order_by=('-good',))] == [True, False, False]
624
>>> qs.extra(order_by=('-good', 'id'))
625
[<Ranking: 3: a1>, <Ranking: 2: a2>, <Ranking: 1: a3>]
627
# Despite having some extra aliases in the query, we can still omit them in a
629
>>> dicts = qs.values('id', 'rank').order_by('id')
630
>>> [sorted(d.items()) for d in dicts]
631
[[('id', 1), ('rank', 2)], [('id', 2), ('rank', 1)], [('id', 3), ('rank', 3)]]
634
# An empty values() call includes all aliases, including those from an extra()
635
>>> dicts = qs.values().order_by('id')
636
>>> [sorted(d.items()) for d in dicts]
637
[[('author_id', 2), ('good', 0), ('id', 1), ('rank', 2)], [('author_id', 3), ('good', 0), ('id', 2), ('rank', 1)], [('author_id', 1), ('good', 1), ('id', 3), ('rank', 3)]]
640
>>> qs = Item.objects.select_related().order_by('note__note', 'name')
642
[<Item: two>, <Item: four>, <Item: one>, <Item: three>]
644
# This is also a good select_related() test because there are multiple Note
645
# entries in the SQL. The two Note items should be different.
646
>>> qs[0].note, qs[0].creator.extra.note
647
(<Note: n2>, <Note: n1>)
650
>>> Item.objects.filter(Q(creator__name='a3', name='two')|Q(creator__name='a4', name='four'))
655
Ordering columns must be included in the output columns. Note that this means
656
results that might otherwise be distinct are not (if there are multiple values
657
in the ordering cols), as in this example. This isn't a bug; it's a warning to
658
be careful with the selection of ordering columns.
660
>>> Note.objects.values('misc').distinct().order_by('note', '-misc')
661
[{'misc': u'foo'}, {'misc': u'bar'}, {'misc': u'foo'}]
664
If you don't pass any fields to values(), relation fields are returned as
665
"foo_id" keys, not "foo". For consistency, you should be able to pass "foo_id"
666
in the fields list and have it work, too. We actually allow both "foo" and
669
# The *_id version is returned by default.
670
>>> 'note_id' in ExtraInfo.objects.values()[0]
673
# You can also pass it in explicitly.
674
>>> ExtraInfo.objects.values('note_id')
675
[{'note_id': 1}, {'note_id': 2}]
677
# ...or use the field name.
678
>>> ExtraInfo.objects.values('note')
679
[{'note': 1}, {'note': 2}]
682
>>> Note.objects.exclude(Q())
683
[<Note: n1>, <Note: n2>, <Note: n3>]
686
Once upon a time, select_related() with circular relations would loop
687
infinitely if you forgot to specify "depth". Now we set an arbitrary default
691
>>> X.objects.select_related()
695
The all() method on querysets returns a copy of the queryset.
696
>>> q1 = Item.objects.order_by('name')
697
>>> id(q1) == id(q1.all())
701
Parameters can be given to extra_select, *if* you use a SortedDict.
703
(First we need to know which order the keys fall in "naturally" on your system,
704
so we can put things in the wrong way around from normal. A normal dict would
706
>>> from django.utils.datastructures import SortedDict
707
>>> s = [('a', '%s'), ('b', '%s')]
708
>>> params = ['one', 'two']
709
>>> if {'a': 1, 'b': 2}.keys() == ['a', 'b']:
713
# This slightly odd comparison works around the fact that PostgreSQL will
714
# return 'one' and 'two' as strings, not Unicode objects. It's a side-effect of
715
# using constants here and not a real concern.
716
>>> d = Item.objects.extra(select=SortedDict(s), select_params=params).values('a', 'b')[0]
717
>>> d == {'a': u'one', 'b': u'two'}
720
# Order by the number of tags attached to an item.
721
>>> l = Item.objects.extra(select={'count': 'select count(*) from queries_item_tags where queries_item_tags.item_id = queries_item.id'}).order_by('-count')
722
>>> [o.count for o in l]
726
Multiple filter statements are joined using "AND" all the time.
728
>>> Author.objects.filter(id=a1.id).filter(Q(extra__note=n1)|Q(item__note=n3))
730
>>> Author.objects.filter(Q(extra__note=n1)|Q(item__note=n3)).filter(id=a1.id)
734
>>> Tag.objects.select_related('parent').order_by('name')
735
[<Tag: t1>, <Tag: t2>, <Tag: t3>, <Tag: t4>, <Tag: t5>]
738
>>> Tag.objects.select_related("parent", "category").order_by('name')
739
[<Tag: t1>, <Tag: t2>, <Tag: t3>, <Tag: t4>, <Tag: t5>]
740
>>> Tag.objects.select_related('parent', "parent__category").order_by('name')
741
[<Tag: t1>, <Tag: t2>, <Tag: t3>, <Tag: t4>, <Tag: t5>]
743
Bug #6180, #6203 -- dates with limits and/or counts
744
>>> Item.objects.count()
746
>>> Item.objects.dates('created', 'month').count()
748
>>> Item.objects.dates('created', 'day').count()
750
>>> len(Item.objects.dates('created', 'day'))
752
>>> Item.objects.dates('created', 'day')[0]
753
datetime.datetime(2007, 12, 19, 0, 0)
755
Bug #7087 -- dates with extra select columns
756
>>> Item.objects.dates('created', 'day').extra(select={'a': 1})
757
[datetime.datetime(2007, 12, 19, 0, 0), datetime.datetime(2007, 12, 20, 0, 0)]
759
Bug #7155 -- nullable dates
760
>>> Item.objects.dates('modified', 'day')
761
[datetime.datetime(2007, 12, 19, 0, 0)]
763
Test that parallel iterators work.
765
>>> qs = Tag.objects.all()
766
>>> i1, i2 = iter(qs), iter(qs)
767
>>> i1.next(), i1.next()
768
(<Tag: t1>, <Tag: t2>)
769
>>> i2.next(), i2.next(), i2.next()
770
(<Tag: t1>, <Tag: t2>, <Tag: t3>)
774
>>> qs = X.objects.all()
780
We can do slicing beyond what is currently in the result cache, too.
782
## FIXME!! This next test causes really weird PostgreSQL behaviour, but it's
783
## only apparent much later when the full test suite runs. I don't understand
784
## what's going on here yet.
786
## # We need to mess with the implementation internals a bit here to decrease the
787
## # cache fill size so that we don't read all the results at once.
788
## >>> from django.db.models import query
789
## >>> query.ITER_CHUNK_SIZE = 2
790
## >>> qs = Tag.objects.all()
792
## # Fill the cache with the first chunk.
795
## >>> len(qs._result_cache)
798
## # Query beyond the end of the cache and check that it is filled out as required.
801
## >>> len(qs._result_cache)
804
## # But querying beyond the end of the result set will fail.
806
## Traceback (most recent call last):
810
Bug #7045 -- extra tables used to crash SQL construction on the second use.
811
>>> qs = Ranking.objects.extra(tables=['django_site'])
812
>>> s = qs.query.as_sql()
813
>>> s = qs.query.as_sql() # test passes if this doesn't raise an exception.
815
Bug #7098 -- Make sure semi-deprecated ordering by related models syntax still
817
>>> Item.objects.values('note__note').order_by('queries_note.note', 'id')
818
[{'note__note': u'n2'}, {'note__note': u'n3'}, {'note__note': u'n3'}, {'note__note': u'n3'}]
820
Bug #7096 -- Make sure exclude() with multiple conditions continues to work.
821
>>> Tag.objects.filter(parent=t1, name='t3').order_by('name')
823
>>> Tag.objects.exclude(parent=t1, name='t3').order_by('name')
824
[<Tag: t1>, <Tag: t2>, <Tag: t4>, <Tag: t5>]
825
>>> Item.objects.exclude(tags__name='t1', name='one').order_by('name').distinct()
826
[<Item: four>, <Item: three>, <Item: two>]
827
>>> Item.objects.filter(name__in=['three', 'four']).exclude(tags__name='t1').order_by('name')
828
[<Item: four>, <Item: three>]
830
More twisted cases, involving nested negations.
831
>>> Item.objects.exclude(~Q(tags__name='t1', name='one'))
833
>>> Item.objects.filter(~Q(tags__name='t1', name='one'), name='two')
835
>>> Item.objects.exclude(~Q(tags__name='t1', name='one'), name='two')
836
[<Item: four>, <Item: one>, <Item: three>]
839
Updates that are filtered on the model being updated are somewhat tricky
840
in MySQL. This exercises that case.
841
>>> mm = ManagedModel.objects.create(data='mm1', tag=t1, public=True)
842
>>> ManagedModel.objects.update(data='mm')
845
A values() or values_list() query across joined models must use outer joins
847
>>> Report.objects.values_list("creator__extra__info", flat=True).order_by("name")
848
[u'e1', u'e2', <NONE_OR_EMPTY_UNICODE>]
850
Similarly for select_related(), joins beyond an initial nullable join must
851
use outer joins so that all results are included.
852
>>> Report.objects.select_related("creator", "creator__extra").order_by("name")
853
[<Report: r1>, <Report: r2>, <Report: r3>]
855
When there are multiple paths to a table from another table, we have to be
856
careful not to accidentally reuse an inappropriate join when using
857
select_related(). We used to return the parent's Detail record here by mistake.
859
>>> d1 = Detail.objects.create(data="d1")
860
>>> d2 = Detail.objects.create(data="d2")
861
>>> m1 = Member.objects.create(name="m1", details=d1)
862
>>> m2 = Member.objects.create(name="m2", details=d2)
863
>>> c1 = Child.objects.create(person=m2, parent=m1)
864
>>> obj = m1.children.select_related("person__details")[0]
865
>>> obj.person.details.data
868
Bug #7076 -- excluding shouldn't eliminate NULL entries.
869
>>> Item.objects.exclude(modified=time1).order_by('name')
870
[<Item: four>, <Item: three>, <Item: two>]
871
>>> Tag.objects.exclude(parent__name=t1.name)
872
[<Tag: t1>, <Tag: t4>, <Tag: t5>]
874
Bug #7181 -- ordering by related tables should accomodate nullable fields (this
875
test is a little tricky, since NULL ordering is database dependent. Instead, we
876
just count the number of results).
877
>>> len(Tag.objects.order_by('parent__name'))
880
Bug #7107 -- this shouldn't create an infinite loop.
881
>>> Valid.objects.all()
884
Empty querysets can be merged with others.
885
>>> Note.objects.none() | Note.objects.all()
886
[<Note: n1>, <Note: n2>, <Note: n3>]
887
>>> Note.objects.all() | Note.objects.none()
888
[<Note: n1>, <Note: n2>, <Note: n3>]
889
>>> Note.objects.none() & Note.objects.all()
891
>>> Note.objects.all() & Note.objects.none()
894
Bug #7204, #7506 -- make sure querysets with related fields can be pickled. If
895
this doesn't crash, it's a Good Thing.
896
>>> out = pickle.dumps(Item.objects.all())
898
We should also be able to pickle things that use select_related(). The only
899
tricky thing here is to ensure that we do the related selections properly after
901
>>> qs = Item.objects.select_related()
902
>>> query = qs.query.as_sql()[0]
903
>>> query2 = pickle.loads(pickle.dumps(qs.query))
904
>>> query2.as_sql()[0] == query
907
Check pickling of deferred-loading querysets
908
>>> qs = Item.objects.defer('name', 'creator')
909
>>> q2 = pickle.loads(pickle.dumps(qs))
910
>>> list(qs) == list(q2)
912
>>> q3 = pickle.loads(pickle.dumps(qs, pickle.HIGHEST_PROTOCOL))
913
>>> list(qs) == list(q3)
917
>>> n1.annotation_set.filter(Q(tag=t5) | Q(tag__children=t5) | Q(tag__children__children=t5))
921
>>> Related.objects.order_by('custom')
924
Bug #7448, #7707 -- Complex objects should be converted to strings before being
926
>>> Item.objects.filter(created__in=[time1, time2])
927
[<Item: one>, <Item: two>]
929
Bug #7698, #10202 -- People like to slice with '0' as the high-water mark.
930
>>> Item.objects.all()[0:0]
932
>>> Item.objects.all()[0:0][:10]
934
>>> Item.objects.all()[:0].count()
936
>>> Item.objects.all()[:0].latest('created')
937
Traceback (most recent call last):
939
AssertionError: Cannot change a query once a slice has been taken.
941
Bug #7411 - saving to db must work even with partially read result set in
944
>>> for num in range(2 * ITER_CHUNK_SIZE + 1):
945
... _ = Number.objects.create(num=num)
947
>>> for i, obj in enumerate(Number.objects.all()):
951
Bug #7759 -- count should work with a partially read result set.
952
>>> count = Number.objects.count()
953
>>> qs = Number.objects.all()
955
... qs.count() == count
959
Bug #7791 -- there were "issues" when ordering and distinct-ing on fields
960
related via ForeignKeys.
961
>>> len(Note.objects.order_by('extrainfo__info').distinct())
964
Bug #7778 - Model subclasses could not be deleted if a nullable foreign key
965
relates to a model that relates back.
967
>>> num_celebs = Celebrity.objects.count()
968
>>> tvc = TvChef.objects.create(name="Huey")
969
>>> Celebrity.objects.count() == num_celebs + 1
971
>>> f1 = Fan.objects.create(fan_of=tvc)
972
>>> f2 = Fan.objects.create(fan_of=tvc)
975
# The parent object should have been deleted as well.
976
>>> Celebrity.objects.count() == num_celebs
979
Bug #8283 -- Checking that applying filters after a disjunction works correctly.
980
>>> (ExtraInfo.objects.filter(note=n1)|ExtraInfo.objects.filter(info='e2')).filter(note=n1)
982
>>> (ExtraInfo.objects.filter(info='e2')|ExtraInfo.objects.filter(note=n1)).filter(note=n1)
985
Pickling of DateQuerySets used to fail
986
>>> qs = Item.objects.dates('created', 'month')
987
>>> _ = pickle.loads(pickle.dumps(qs))
989
Bug #8683 -- raise proper error when a DateQuerySet gets passed a wrong type of field
990
>>> Item.objects.dates('name', 'month')
991
Traceback (most recent call last):
993
AssertionError: 'name' isn't a DateField.
995
Bug #8597: regression tests for case-insensitive comparisons
996
>>> _ = Item.objects.create(name="a_b", created=datetime.datetime.now(), creator=a2, note=n1)
997
>>> _ = Item.objects.create(name="x%y", created=datetime.datetime.now(), creator=a2, note=n1)
998
>>> Item.objects.filter(name__iexact="A_b")
1000
>>> Item.objects.filter(name__iexact="x%Y")
1002
>>> Item.objects.filter(name__istartswith="A_b")
1004
>>> Item.objects.filter(name__iendswith="A_b")
1007
Bug #7302: reserved names are appropriately escaped
1008
>>> _ = ReservedName.objects.create(name='a',order=42)
1009
>>> _ = ReservedName.objects.create(name='b',order=37)
1010
>>> ReservedName.objects.all().order_by('order')
1011
[<ReservedName: b>, <ReservedName: a>]
1012
>>> ReservedName.objects.extra(select={'stuff':'name'}, order_by=('order','stuff'))
1013
[<ReservedName: b>, <ReservedName: a>]
1015
Bug #8439 -- complex combinations of conjunctions, disjunctions and nullable
1017
>>> Author.objects.filter(Q(item__note__extrainfo=e2)|Q(report=r1, name='xyz'))
1019
>>> Author.objects.filter(Q(report=r1, name='xyz')|Q(item__note__extrainfo=e2))
1021
>>> Annotation.objects.filter(Q(tag__parent=t1)|Q(notes__note='n1', name='a1'))
1023
>>> xx = ExtraInfo.objects.create(info='xx', note=n3)
1024
>>> Note.objects.filter(Q(extrainfo__author=a1)|Q(extrainfo=xx))
1025
[<Note: n1>, <Note: n3>]
1027
>>> q = Note.objects.filter(Q(extrainfo__author=a1)|Q(extrainfo=xx)).query
1028
>>> len([x[2] for x in q.alias_map.values() if x[2] == q.LOUTER and q.alias_refcount[x[1]]])
1031
Make sure bump_prefix() (an internal Query method) doesn't (re-)break. It's
1032
sufficient that this query runs without error.
1033
>>> qs = Tag.objects.values_list('id', flat=True).order_by('id')
1034
>>> qs.query.bump_prefix()
1038
Calling order_by() with no parameters removes any existing ordering on the
1039
model. But it should still be possible to add new ordering after that.
1040
>>> qs = Author.objects.order_by().order_by('name')
1041
>>> 'ORDER BY' in qs.query.as_sql()[0]
1044
Incorrect SQL was being generated for certain types of exclude() queries that
1045
crossed multi-valued relations (#8921, #9188 and some pre-emptively discovered
1048
>>> PointerA.objects.filter(connection__pointerb__id=1)
1050
>>> PointerA.objects.exclude(connection__pointerb__id=1)
1053
>>> Tag.objects.exclude(children=None)
1054
[<Tag: t1>, <Tag: t3>]
1056
# This example is tricky because the parent could be NULL, so only checking
1057
# parents with annotations omits some results (tag t1, in this case).
1058
>>> Tag.objects.exclude(parent__annotation__name="a1")
1059
[<Tag: t1>, <Tag: t4>, <Tag: t5>]
1061
# The annotation->tag link is single values and tag->children links is
1062
# multi-valued. So we have to split the exclude filter in the middle and then
1063
# optimise the inner query without losing results.
1064
>>> Annotation.objects.exclude(tag__children__name="t2")
1067
Nested queries are possible (although should be used with care, since they have
1068
performance problems on backends like MySQL.
1070
>>> Annotation.objects.filter(notes__in=Note.objects.filter(note="n1"))
1073
Nested queries should not evaluate the inner query as part of constructing the
1074
SQL (so we should see a nested query here, indicated by two "SELECT" calls).
1075
>>> Annotation.objects.filter(notes__in=Note.objects.filter(note="xyzzy")).query.as_sql()[0].count('SELECT')
1078
Bug #10181 -- Avoid raising an EmptyResultSet if an inner query is provably
1079
empty (and hence, not executed).
1080
>>> Tag.objects.filter(id__in=Tag.objects.filter(id__in=[]))
1083
Bug #9997 -- If a ValuesList or Values queryset is passed as an inner query, we
1084
make sure it's only requesting a single value and use that as the thing to
1086
>>> Tag.objects.filter(name__in=Tag.objects.filter(parent=t1).values('name'))
1087
[<Tag: t2>, <Tag: t3>]
1089
# Multi-valued values() and values_list() querysets should raise errors.
1090
>>> Tag.objects.filter(name__in=Tag.objects.filter(parent=t1).values('name', 'id'))
1091
Traceback (most recent call last):
1093
TypeError: Cannot use a multi-field ValuesQuerySet as a filter value.
1094
>>> Tag.objects.filter(name__in=Tag.objects.filter(parent=t1).values_list('name', 'id'))
1095
Traceback (most recent call last):
1097
TypeError: Cannot use a multi-field ValuesListQuerySet as a filter value.
1099
Bug #9985 -- qs.values_list(...).values(...) combinations should work.
1100
>>> Note.objects.values_list("note", flat=True).values("id").order_by("id")
1101
[{'id': 1}, {'id': 2}, {'id': 3}]
1102
>>> Annotation.objects.filter(notes__in=Note.objects.filter(note="n1").values_list('note').values('id'))
1105
Bug #10028 -- ordering by model related to nullable relations(!) should use
1106
outer joins, so that all results are included.
1107
>>> _ = Plaything.objects.create(name="p1")
1108
>>> Plaything.objects.all()
1111
Bug #10205 -- When bailing out early because of an empty "__in" filter, we need
1112
to set things up correctly internally so that subqueries can continue properly.
1113
>>> Tag.objects.filter(name__in=()).update(name="foo")
1116
Bug #10432 (see also the Python 2.4+ tests for this, below). Testing an empty
1117
"__in" filter with a generator as the value.
1120
>>> n_obj = Note.objects.all()[0]
1122
... for i in [n_obj.pk]:
1124
>>> Note.objects.filter(pk__in=f())
1126
>>> list(Note.objects.filter(pk__in=g())) == [n_obj]
1129
Make sure that updates which only filter on sub-tables don't inadvertently
1130
update the wrong records (bug #9848).
1132
# Make sure that the IDs from different tables don't happen to match.
1133
>>> Ranking.objects.filter(author__name='a1')
1135
>>> Ranking.objects.filter(author__name='a1').update(rank='4')
1137
>>> r = Ranking.objects.filter(author__name='a1')[0]
1138
>>> r.id != r.author.id
1144
>>> Ranking.objects.all()
1145
[<Ranking: 3: a1>, <Ranking: 2: a2>, <Ranking: 1: a3>]
1147
# Regression test for #10742:
1148
# Queries used in an __in clause don't execute subqueries
1150
>>> subq = Author.objects.filter(num__lt=3000)
1151
>>> qs = Author.objects.filter(pk__in=subq)
1153
[<Author: a1>, <Author: a2>]
1155
# The subquery result cache should not be populated
1156
>>> subq._result_cache is None
1159
>>> subq = Author.objects.filter(num__lt=3000)
1160
>>> qs = Author.objects.exclude(pk__in=subq)
1162
[<Author: a3>, <Author: a4>]
1164
# The subquery result cache should not be populated
1165
>>> subq._result_cache is None
1168
>>> subq = Author.objects.filter(num__lt=3000)
1169
>>> list(Author.objects.filter(Q(pk__in=subq) & Q(name='a1')))
1172
# The subquery result cache should not be populated
1173
>>> subq._result_cache is None
1178
# In Python 2.3 and the Python 2.6 beta releases, exceptions raised in __len__
1179
# are swallowed (Python issue 1242657), so these cases return an empty list,
1180
# rather than raising an exception. Not a lot we can do about that,
1181
# unfortunately, due to the way Python handles list() calls internally. Thus,
1182
# we skip the tests for Python 2.3 and 2.6.
1183
if (2, 4) <= sys.version_info < (2, 6):
1184
__test__["API_TESTS"] += """
1185
# If you're not careful, it's possible to introduce infinite loops via default
1186
# ordering on foreign keys in a cycle. We detect that.
1187
>>> LoopX.objects.all()
1188
Traceback (most recent call last):
1190
FieldError: Infinite loop caused by ordering.
1192
>>> LoopZ.objects.all()
1193
Traceback (most recent call last):
1195
FieldError: Infinite loop caused by ordering.
1197
# Note that this doesn't cause an infinite loop, since the default ordering on
1198
# the Tag model is empty (and thus defaults to using "id" for the related
1200
>>> len(Tag.objects.order_by('parent'))
1203
# ... but you can still order in a non-recursive fashion amongst linked fields
1204
# (the previous test failed because the default ordering was recursive).
1205
>>> LoopX.objects.all().order_by('y__x__y__x__id')
1211
# In Oracle, we expect a null CharField to return u'' instead of None.
1212
if settings.DATABASE_ENGINE == "oracle":
1213
__test__["API_TESTS"] = __test__["API_TESTS"].replace("<NONE_OR_EMPTY_UNICODE>", "u''")
1215
__test__["API_TESTS"] = __test__["API_TESTS"].replace("<NONE_OR_EMPTY_UNICODE>", "None")
1218
if settings.DATABASE_ENGINE == "mysql":
1219
__test__["API_TESTS"] += """
1220
When grouping without specifying ordering, we add an explicit "ORDER BY NULL"
1221
portion in MySQL to prevent unnecessary sorting.
1223
>>> query = Tag.objects.values_list('parent_id', flat=True).order_by().query
1224
>>> query.group_by = ['parent_id']
1225
>>> sql = query.as_sql()[0]
1226
>>> fragment = "ORDER BY "
1227
>>> pos = sql.find(fragment)
1228
>>> sql.find(fragment, pos + 1) == -1
1230
>>> sql.find("NULL", pos + len(fragment)) == pos + len(fragment)
1235
# Generator expressions are only in Python 2.4 and later.
1236
if sys.version_info >= (2, 4):
1237
__test__["API_TESTS"] += """
1238
Using an empty generator expression as the rvalue for an "__in" lookup is legal
1239
(regression for #10432).
1240
>>> Note.objects.filter(pk__in=(x for x in ()))
added
removed
tests/regressiontests/queries/models.py
