2
Various complex queries that have been problematic in the past.
9
from django.db import models
10
from django.db.models.query import Q, ITER_CHUNK_SIZE
12
# Python 2.3 doesn't have sorted()
16
from django.utils.itercompat import sorted
18
class Tag(models.Model):
19
name = models.CharField(max_length=10)
20
parent = models.ForeignKey('self', blank=True, null=True,
21
related_name='children')
26
def __unicode__(self):
29
class Note(models.Model):
30
note = models.CharField(max_length=100)
31
misc = models.CharField(max_length=10)
36
def __unicode__(self):
39
class Annotation(models.Model):
40
name = models.CharField(max_length=10)
41
tag = models.ForeignKey(Tag)
42
notes = models.ManyToManyField(Note)
44
def __unicode__(self):
47
class ExtraInfo(models.Model):
48
info = models.CharField(max_length=100)
49
note = models.ForeignKey(Note)
54
def __unicode__(self):
57
class Author(models.Model):
58
name = models.CharField(max_length=10)
59
num = models.IntegerField(unique=True)
60
extra = models.ForeignKey(ExtraInfo)
62
def __unicode__(self):
65
class Item(models.Model):
66
name = models.CharField(max_length=10)
67
created = models.DateTimeField()
68
modified = models.DateTimeField(blank=True, null=True)
69
tags = models.ManyToManyField(Tag, blank=True, null=True)
70
creator = models.ForeignKey(Author)
71
note = models.ForeignKey(Note)
74
ordering = ['-note', 'name']
76
def __unicode__(self):
79
class Report(models.Model):
80
name = models.CharField(max_length=10)
81
creator = models.ForeignKey(Author, to_field='num', null=True)
83
def __unicode__(self):
86
class Ranking(models.Model):
87
rank = models.IntegerField()
88
author = models.ForeignKey(Author)
91
# A complex ordering specification. Should stress the system a bit.
92
ordering = ('author__extra__note', 'author__name', 'rank')
94
def __unicode__(self):
95
return '%d: %s' % (self.rank, self.author.name)
97
class Cover(models.Model):
98
title = models.CharField(max_length=50)
99
item = models.ForeignKey(Item)
104
def __unicode__(self):
107
class Number(models.Model):
108
num = models.IntegerField()
110
def __unicode__(self):
111
return unicode(self.num)
113
# Symmetrical m2m field with a normal field using the reverse accesor name
115
class Valid(models.Model):
116
valid = models.CharField(max_length=10)
117
parent = models.ManyToManyField('self')
122
# Some funky cross-linked models for testing a couple of infinite recursion
124
class X(models.Model):
125
y = models.ForeignKey('Y')
127
class Y(models.Model):
128
x1 = models.ForeignKey(X, related_name='y1')
130
# Some models with a cycle in the default ordering. This would be bad if we
131
# didn't catch the infinite loop.
132
class LoopX(models.Model):
133
y = models.ForeignKey('LoopY')
138
class LoopY(models.Model):
139
x = models.ForeignKey(LoopX)
144
class LoopZ(models.Model):
145
z = models.ForeignKey('self')
150
# A model and custom default manager combination.
151
class CustomManager(models.Manager):
152
def get_query_set(self):
153
qs = super(CustomManager, self).get_query_set()
154
return qs.filter(public=True, tag__name='t1')
156
class ManagedModel(models.Model):
157
data = models.CharField(max_length=10)
158
tag = models.ForeignKey(Tag)
159
public = models.BooleanField(default=True)
161
objects = CustomManager()
162
normal_manager = models.Manager()
164
def __unicode__(self):
167
# An inter-related setup with multiple paths from Child to Detail.
168
class Detail(models.Model):
169
data = models.CharField(max_length=10)
171
class MemberManager(models.Manager):
172
def get_query_set(self):
173
return super(MemberManager, self).get_query_set().select_related("details")
175
class Member(models.Model):
176
name = models.CharField(max_length=10)
177
details = models.OneToOneField(Detail, primary_key=True)
179
objects = MemberManager()
181
class Child(models.Model):
182
person = models.OneToOneField(Member, primary_key=True)
183
parent = models.ForeignKey(Member, related_name="children")
185
# Custom primary keys interfered with ordering in the past.
186
class CustomPk(models.Model):
187
name = models.CharField(max_length=10, primary_key=True)
188
extra = models.CharField(max_length=10)
191
ordering = ['name', 'extra']
193
class Related(models.Model):
194
custom = models.ForeignKey(CustomPk)
196
# An inter-related setup with a model subclass that has a nullable
197
# path to another model, and a return path from that model.
199
class Celebrity(models.Model):
200
name = models.CharField("Name", max_length=20)
201
greatest_fan = models.ForeignKey("Fan", null=True, unique=True)
203
class TvChef(Celebrity):
206
class Fan(models.Model):
207
fan_of = models.ForeignKey(Celebrity)
209
# Multiple foreign keys
210
class LeafA(models.Model):
211
data = models.CharField(max_length=10)
213
def __unicode__(self):
216
class LeafB(models.Model):
217
data = models.CharField(max_length=10)
219
class Join(models.Model):
220
a = models.ForeignKey(LeafA)
221
b = models.ForeignKey(LeafB)
223
class ReservedName(models.Model):
224
name = models.CharField(max_length=20)
225
order = models.IntegerField()
227
def __unicode__(self):
230
__test__ = {'API_TESTS':"""
231
>>> t1 = Tag.objects.create(name='t1')
232
>>> t2 = Tag.objects.create(name='t2', parent=t1)
233
>>> t3 = Tag.objects.create(name='t3', parent=t1)
234
>>> t4 = Tag.objects.create(name='t4', parent=t3)
235
>>> t5 = Tag.objects.create(name='t5', parent=t3)
237
>>> n1 = Note.objects.create(note='n1', misc='foo')
238
>>> n2 = Note.objects.create(note='n2', misc='bar')
239
>>> n3 = Note.objects.create(note='n3', misc='foo')
241
>>> ann1 = Annotation.objects.create(name='a1', tag=t1)
242
>>> ann1.notes.add(n1)
243
>>> ann2 = Annotation.objects.create(name='a2', tag=t4)
244
>>> ann2.notes.add(n2, n3)
246
Create these out of order so that sorting by 'id' will be different to sorting
247
by 'info'. Helps detect some problems later.
248
>>> e2 = ExtraInfo.objects.create(info='e2', note=n2)
249
>>> e1 = ExtraInfo.objects.create(info='e1', note=n1)
251
>>> a1 = Author.objects.create(name='a1', num=1001, extra=e1)
252
>>> a2 = Author.objects.create(name='a2', num=2002, extra=e1)
253
>>> a3 = Author.objects.create(name='a3', num=3003, extra=e2)
254
>>> a4 = Author.objects.create(name='a4', num=4004, extra=e2)
256
>>> time1 = datetime.datetime(2007, 12, 19, 22, 25, 0)
257
>>> time2 = datetime.datetime(2007, 12, 19, 21, 0, 0)
258
>>> time3 = datetime.datetime(2007, 12, 20, 22, 25, 0)
259
>>> time4 = datetime.datetime(2007, 12, 20, 21, 0, 0)
260
>>> i1 = Item.objects.create(name='one', created=time1, modified=time1, creator=a1, note=n3)
261
>>> i1.tags = [t1, t2]
262
>>> i2 = Item.objects.create(name='two', created=time2, creator=a2, note=n2)
263
>>> i2.tags = [t1, t3]
264
>>> i3 = Item.objects.create(name='three', created=time3, creator=a2, note=n3)
265
>>> i4 = Item.objects.create(name='four', created=time4, creator=a4, note=n3)
268
>>> r1 = Report.objects.create(name='r1', creator=a1)
269
>>> r2 = Report.objects.create(name='r2', creator=a3)
270
>>> r3 = Report.objects.create(name='r3')
272
Ordering by 'rank' gives us rank2, rank1, rank3. Ordering by the Meta.ordering
273
will be rank3, rank2, rank1.
274
>>> rank1 = Ranking.objects.create(rank=2, author=a2)
275
>>> rank2 = Ranking.objects.create(rank=1, author=a3)
276
>>> rank3 = Ranking.objects.create(rank=3, author=a1)
278
>>> c1 = Cover.objects.create(title="first", item=i4)
279
>>> c2 = Cover.objects.create(title="second", item=i2)
281
>>> num1 = Number.objects.create(num=4)
282
>>> num2 = Number.objects.create(num=8)
283
>>> num3 = Number.objects.create(num=12)
286
>>> Item.objects.filter(tags__isnull=True)
288
>>> Item.objects.filter(tags__id__isnull=True)
292
>>> Author.objects.filter(item=i2)
294
>>> Author.objects.filter(item=i3)
296
>>> Author.objects.filter(item=i2) & Author.objects.filter(item=i3)
300
Checking that no join types are "left outer" joins.
301
>>> query = Item.objects.filter(tags=t2).query
302
>>> query.LOUTER not in [x[2] for x in query.alias_map.values()]
305
>>> Item.objects.filter(Q(tags=t1)).order_by('name')
306
[<Item: one>, <Item: two>]
307
>>> Item.objects.filter(Q(tags=t1)).filter(Q(tags=t2))
309
>>> Item.objects.filter(Q(tags=t1)).filter(Q(creator__name='fred')|Q(tags=t2))
312
Each filter call is processed "at once" against a single table, so this is
313
different from the previous example as it tries to find tags that are two
314
things at once (rather than two tags).
315
>>> Item.objects.filter(Q(tags=t1) & Q(tags=t2))
317
>>> Item.objects.filter(Q(tags=t1), Q(creator__name='fred')|Q(tags=t2))
320
>>> qs = Author.objects.filter(ranking__rank=2, ranking__id=rank1.id)
323
>>> qs.query.count_active_tables()
325
>>> qs = Author.objects.filter(ranking__rank=2).filter(ranking__id=rank1.id)
326
>>> qs.query.count_active_tables()
330
>>> Item.objects.filter(tags=t1).filter(tags=t2)
332
>>> Item.objects.filter(tags__in=[t1, t2]).distinct().order_by('name')
333
[<Item: one>, <Item: two>]
334
>>> Item.objects.filter(tags__in=[t1, t2]).filter(tags=t3)
338
>>> Author.objects.filter(item__name='one') | Author.objects.filter(name='a3')
339
[<Author: a1>, <Author: a3>]
340
>>> Author.objects.filter(Q(item__name='one') | Q(name='a3'))
341
[<Author: a1>, <Author: a3>]
342
>>> Author.objects.filter(Q(name='a3') | Q(item__name='one'))
343
[<Author: a1>, <Author: a3>]
344
>>> Author.objects.filter(Q(item__name='three') | Q(report__name='r3'))
348
A slight variation on the above theme: restricting the choices by the lookup
350
>>> Number.objects.filter(num__lt=4)
352
>>> Number.objects.filter(num__gt=8, num__lt=12)
354
>>> Number.objects.filter(num__gt=8, num__lt=13)
356
>>> Number.objects.filter(Q(num__lt=4) | Q(num__gt=8, num__lt=12))
358
>>> Number.objects.filter(Q(num__gt=8, num__lt=12) | Q(num__lt=4))
360
>>> Number.objects.filter(Q(num__gt=8) & Q(num__lt=12) | Q(num__lt=4))
362
>>> Number.objects.filter(Q(num__gt=7) & Q(num__lt=12) | Q(num__lt=4))
366
Another variation on the disjunctive filtering theme.
368
# For the purposes of this regression test, it's important that there is no
369
# Join object releated to the LeafA we create.
370
>>> LeafA.objects.create(data='first')
372
>>> LeafA.objects.filter(Q(data='first')|Q(join__b__data='second'))
376
Merging two empty result sets shouldn't leave a queryset with no constraints
377
(which would match everything).
378
>>> Author.objects.filter(Q(id__in=[]))
380
>>> Author.objects.filter(Q(id__in=[])|Q(id__in=[]))
384
>>> Item.objects.values('creator').distinct().count()
387
# Create something with a duplicate 'name' so that we can test multi-column
388
# cases (which require some tricky SQL transformations under the covers).
389
>>> xx = Item(name='four', created=time1, creator=a2, note=n1)
391
>>> Item.objects.exclude(name='two').values('creator', 'name').distinct().count()
393
>>> Item.objects.exclude(name='two').extra(select={'foo': '%s'}, select_params=(1,)).values('creator', 'name', 'foo').distinct().count()
395
>>> Item.objects.exclude(name='two').extra(select={'foo': '%s'}, select_params=(1,)).values('creator', 'name').distinct().count()
400
>>> Item.objects.values('creator', 'name').count()
404
>>> q1 = Item.objects.order_by('name')
405
>>> q2 = Item.objects.filter(id=i1.id)
407
[<Item: four>, <Item: one>, <Item: three>, <Item: two>]
410
>>> (q1 | q2).order_by('name')
411
[<Item: four>, <Item: one>, <Item: three>, <Item: two>]
412
>>> (q1 & q2).order_by('name')
415
# FIXME: This is difficult to fix and very much an edge case, so punt for now.
416
# # This is related to the order_by() tests, below, but the old bug exhibited
417
# # itself here (q2 was pulling too many tables into the combined query with the
418
# # new ordering, but only because we have evaluated q2 already).
419
# >>> len((q1 & q2).order_by('name').query.tables)
422
>>> q1 = Item.objects.filter(tags=t1)
423
>>> q2 = Item.objects.filter(note=n3, tags=t2)
424
>>> q3 = Item.objects.filter(creator=a4)
425
>>> ((q1 & q2) | q3).order_by('name')
426
[<Item: four>, <Item: one>]
429
>>> Report.objects.filter(creator=1001)
431
>>> Report.objects.filter(creator__num=1001)
433
>>> Report.objects.filter(creator__id=1001)
435
>>> Report.objects.filter(creator__id=a1.id)
437
>>> Report.objects.filter(creator__name='a1')
441
>>> Author.objects.filter(report__name='r1')
445
>>> a1.report_set.all()
449
>>> Item.objects.filter(tags__name='t4')
451
>>> Item.objects.exclude(tags__name='t4').order_by('name').distinct()
452
[<Item: one>, <Item: three>, <Item: two>]
453
>>> Item.objects.exclude(tags__name='t4').order_by('name').distinct().reverse()
454
[<Item: two>, <Item: three>, <Item: one>]
455
>>> Author.objects.exclude(item__name='one').distinct().order_by('name')
456
[<Author: a2>, <Author: a3>, <Author: a4>]
459
# Excluding across a m2m relation when there is more than one related object
460
# associated was problematic.
461
>>> Item.objects.exclude(tags__name='t1').order_by('name')
462
[<Item: four>, <Item: three>]
463
>>> Item.objects.exclude(tags__name='t1').exclude(tags__name='t4')
466
# Excluding from a relation that cannot be NULL should not use outer joins.
467
>>> query = Item.objects.exclude(creator__in=[a1, a2]).query
468
>>> query.LOUTER not in [x[2] for x in query.alias_map.values()]
471
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).
472
>>> qs = Author.objects.filter(id=a1.id).filter(Q(extra__note=n1)|Q(item__note=n3))
473
>>> len([x[2] for x in qs.query.alias_map.values() if x[2] == query.LOUTER and qs.query.alias_refcount[x[1]]])
476
The previous changes shouldn't affect nullable foreign key joins.
477
>>> Tag.objects.filter(parent__isnull=True).order_by('name')
479
>>> Tag.objects.exclude(parent__isnull=True).order_by('name')
480
[<Tag: t2>, <Tag: t3>, <Tag: t4>, <Tag: t5>]
481
>>> Tag.objects.exclude(Q(parent__name='t1') | Q(parent__isnull=True)).order_by('name')
482
[<Tag: t4>, <Tag: t5>]
483
>>> Tag.objects.exclude(Q(parent__isnull=True) | Q(parent__name='t1')).order_by('name')
484
[<Tag: t4>, <Tag: t5>]
485
>>> Tag.objects.exclude(Q(parent__parent__isnull=True)).order_by('name')
486
[<Tag: t4>, <Tag: t5>]
487
>>> Tag.objects.filter(~Q(parent__parent__isnull=True)).order_by('name')
488
[<Tag: t4>, <Tag: t5>]
491
>>> t = Tag.objects.get(name='t4')
492
>>> Item.objects.filter(tags__in=[t])
495
Combining querysets built on different models should behave in a well-defined
496
fashion. We raise an error.
497
>>> Author.objects.all() & Tag.objects.all()
498
Traceback (most recent call last):
500
AssertionError: Cannot combine queries on two different base models.
501
>>> Author.objects.all() | Tag.objects.all()
502
Traceback (most recent call last):
504
AssertionError: Cannot combine queries on two different base models.
507
>>> Author.objects.extra(select={'foo': '1'}).count()
509
>>> Author.objects.extra(select={'foo': '%s'}, select_params=(1,)).count()
513
>>> Author.objects.filter(item__isnull=True)
515
>>> Tag.objects.filter(item__isnull=True)
519
>>> Item.objects.extra(tables=['queries_author']).select_related().order_by('name')[:1]
523
# Ordering on related tables should be possible, even if the table is not
524
# otherwise involved.
525
>>> Item.objects.order_by('note__note', 'name')
526
[<Item: two>, <Item: four>, <Item: one>, <Item: three>]
528
# Ordering on a related field should use the remote model's default ordering as
530
>>> Author.objects.order_by('extra', '-name')
531
[<Author: a2>, <Author: a1>, <Author: a4>, <Author: a3>]
533
# Using remote model default ordering can span multiple models (in this case,
534
# Cover is ordered by Item's default, which uses Note's default).
535
>>> Cover.objects.all()
536
[<Cover: first>, <Cover: second>]
538
# If the remote model does not have a default ordering, we order by its 'id'
540
>>> Item.objects.order_by('creator', 'name')
541
[<Item: one>, <Item: three>, <Item: two>, <Item: four>]
543
# Cross model ordering is possible in Meta, too.
544
>>> Ranking.objects.all()
545
[<Ranking: 3: a1>, <Ranking: 2: a2>, <Ranking: 1: a3>]
546
>>> Ranking.objects.all().order_by('rank')
547
[<Ranking: 1: a3>, <Ranking: 2: a2>, <Ranking: 3: a1>]
549
# Ordering by a many-valued attribute (e.g. a many-to-many or reverse
550
# ForeignKey) is legal, but the results might not make sense. That isn't
551
# Django's problem. Garbage in, garbage out.
552
>>> Item.objects.filter(tags__isnull=False).order_by('tags', 'id')
553
[<Item: one>, <Item: two>, <Item: one>, <Item: two>, <Item: four>]
555
# If we replace the default ordering, Django adjusts the required tables
556
# automatically. Item normally requires a join with Note to do the default
557
# ordering, but that isn't needed here.
558
>>> qs = Item.objects.order_by('name')
560
[<Item: four>, <Item: one>, <Item: three>, <Item: two>]
561
>>> len(qs.query.tables)
564
# Ordering of extra() pieces is possible, too and you can mix extra fields and
565
# model fields in the ordering.
566
>>> Ranking.objects.extra(tables=['django_site'], order_by=['-django_site.id', 'rank'])
567
[<Ranking: 1: a3>, <Ranking: 2: a2>, <Ranking: 3: a1>]
569
>>> qs = Ranking.objects.extra(select={'good': 'case when rank > 2 then 1 else 0 end'})
570
>>> [o.good for o in qs.extra(order_by=('-good',))] == [True, False, False]
572
>>> qs.extra(order_by=('-good', 'id'))
573
[<Ranking: 3: a1>, <Ranking: 2: a2>, <Ranking: 1: a3>]
575
# Despite having some extra aliases in the query, we can still omit them in a
577
>>> dicts = qs.values('id', 'rank').order_by('id')
578
>>> [sorted(d.items()) for d in dicts]
579
[[('id', 1), ('rank', 2)], [('id', 2), ('rank', 1)], [('id', 3), ('rank', 3)]]
582
# An empty values() call includes all aliases, including those from an extra()
583
>>> dicts = qs.values().order_by('id')
584
>>> [sorted(d.items()) for d in dicts]
585
[[('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)]]
588
>>> qs = Item.objects.select_related().order_by('note__note', 'name')
590
[<Item: two>, <Item: four>, <Item: one>, <Item: three>]
592
# This is also a good select_related() test because there are multiple Note
593
# entries in the SQL. The two Note items should be different.
594
>>> qs[0].note, qs[0].creator.extra.note
595
(<Note: n2>, <Note: n1>)
598
>>> Item.objects.filter(Q(creator__name='a3', name='two')|Q(creator__name='a4', name='four'))
603
Ordering columns must be included in the output columns. Note that this means
604
results that might otherwise be distinct are not (if there are multiple values
605
in the ordering cols), as in this example. This isn't a bug; it's a warning to
606
be careful with the selection of ordering columns.
608
>>> Note.objects.values('misc').distinct().order_by('note', '-misc')
609
[{'misc': u'foo'}, {'misc': u'bar'}, {'misc': u'foo'}]
612
If you don't pass any fields to values(), relation fields are returned as
613
"foo_id" keys, not "foo". For consistency, you should be able to pass "foo_id"
614
in the fields list and have it work, too. We actually allow both "foo" and
617
# The *_id version is returned by default.
618
>>> 'note_id' in ExtraInfo.objects.values()[0]
621
# You can also pass it in explicitly.
622
>>> ExtraInfo.objects.values('note_id')
623
[{'note_id': 1}, {'note_id': 2}]
625
# ...or use the field name.
626
>>> ExtraInfo.objects.values('note')
627
[{'note': 1}, {'note': 2}]
630
>>> Note.objects.exclude(Q())
631
[<Note: n1>, <Note: n2>, <Note: n3>]
634
Once upon a time, select_related() with circular relations would loop
635
infinitely if you forgot to specify "depth". Now we set an arbitrary default
639
>>> X.objects.select_related()
643
The all() method on querysets returns a copy of the queryset.
644
>>> q1 = Item.objects.order_by('name')
645
>>> id(q1) == id(q1.all())
649
Parameters can be given to extra_select, *if* you use a SortedDict.
651
(First we need to know which order the keys fall in "naturally" on your system,
652
so we can put things in the wrong way around from normal. A normal dict would
654
>>> from django.utils.datastructures import SortedDict
655
>>> s = [('a', '%s'), ('b', '%s')]
656
>>> params = ['one', 'two']
657
>>> if {'a': 1, 'b': 2}.keys() == ['a', 'b']:
661
# This slightly odd comparison works aorund the fact that PostgreSQL will
662
# return 'one' and 'two' as strings, not Unicode objects. It's a side-effect of
663
# using constants here and not a real concern.
664
>>> d = Item.objects.extra(select=SortedDict(s), select_params=params).values('a', 'b')[0]
665
>>> d == {'a': u'one', 'b': u'two'}
668
# Order by the number of tags attached to an item.
669
>>> l = Item.objects.extra(select={'count': 'select count(*) from queries_item_tags where queries_item_tags.item_id = queries_item.id'}).order_by('-count')
670
>>> [o.count for o in l]
674
Multiple filter statements are joined using "AND" all the time.
676
>>> Author.objects.filter(id=a1.id).filter(Q(extra__note=n1)|Q(item__note=n3))
678
>>> Author.objects.filter(Q(extra__note=n1)|Q(item__note=n3)).filter(id=a1.id)
682
>>> Tag.objects.select_related('parent').order_by('name')
683
[<Tag: t1>, <Tag: t2>, <Tag: t3>, <Tag: t4>, <Tag: t5>]
685
Bug #6180, #6203 -- dates with limits and/or counts
686
>>> Item.objects.count()
688
>>> Item.objects.dates('created', 'month').count()
690
>>> Item.objects.dates('created', 'day').count()
692
>>> len(Item.objects.dates('created', 'day'))
694
>>> Item.objects.dates('created', 'day')[0]
695
datetime.datetime(2007, 12, 19, 0, 0)
697
Bug #7087 -- dates with extra select columns
698
>>> Item.objects.dates('created', 'day').extra(select={'a': 1})
699
[datetime.datetime(2007, 12, 19, 0, 0), datetime.datetime(2007, 12, 20, 0, 0)]
701
Bug #7155 -- nullable dates
702
>>> Item.objects.dates('modified', 'day')
703
[datetime.datetime(2007, 12, 19, 0, 0)]
705
Test that parallel iterators work.
707
>>> qs = Tag.objects.all()
708
>>> i1, i2 = iter(qs), iter(qs)
709
>>> i1.next(), i1.next()
710
(<Tag: t1>, <Tag: t2>)
711
>>> i2.next(), i2.next(), i2.next()
712
(<Tag: t1>, <Tag: t2>, <Tag: t3>)
716
>>> qs = X.objects.all()
722
We can do slicing beyond what is currently in the result cache, too.
724
## FIXME!! This next test causes really weird PostgreSQL behaviour, but it's
725
## only apparent much later when the full test suite runs. I don't understand
726
## what's going on here yet.
728
## # We need to mess with the implemenation internals a bit here to decrease the
729
## # cache fill size so that we don't read all the results at once.
730
## >>> from django.db.models import query
731
## >>> query.ITER_CHUNK_SIZE = 2
732
## >>> qs = Tag.objects.all()
734
## # Fill the cache with the first chunk.
737
## >>> len(qs._result_cache)
740
## # Query beyond the end of the cache and check that it is filled out as required.
743
## >>> len(qs._result_cache)
746
## # But querying beyond the end of the result set will fail.
748
## Traceback (most recent call last):
752
Bug #7045 -- extra tables used to crash SQL construction on the second use.
753
>>> qs = Ranking.objects.extra(tables=['django_site'])
754
>>> s = qs.query.as_sql()
755
>>> s = qs.query.as_sql() # test passes if this doesn't raise an exception.
757
Bug #7098 -- Make sure semi-deprecated ordering by related models syntax still
759
>>> Item.objects.values('note__note').order_by('queries_note.note', 'id')
760
[{'note__note': u'n2'}, {'note__note': u'n3'}, {'note__note': u'n3'}, {'note__note': u'n3'}]
762
Bug #7096 -- Make sure exclude() with multiple conditions continues to work.
763
>>> Tag.objects.filter(parent=t1, name='t3').order_by('name')
765
>>> Tag.objects.exclude(parent=t1, name='t3').order_by('name')
766
[<Tag: t1>, <Tag: t2>, <Tag: t4>, <Tag: t5>]
767
>>> Item.objects.exclude(tags__name='t1', name='one').order_by('name').distinct()
768
[<Item: four>, <Item: three>, <Item: two>]
769
>>> Item.objects.filter(name__in=['three', 'four']).exclude(tags__name='t1').order_by('name')
770
[<Item: four>, <Item: three>]
772
More twisted cases, involving nested negations.
773
>>> Item.objects.exclude(~Q(tags__name='t1', name='one'))
775
>>> Item.objects.filter(~Q(tags__name='t1', name='one'), name='two')
777
>>> Item.objects.exclude(~Q(tags__name='t1', name='one'), name='two')
778
[<Item: four>, <Item: one>, <Item: three>]
781
Updates that are filtered on the model being updated are somewhat tricky to get
782
in MySQL. This exercises that case.
783
>>> mm = ManagedModel.objects.create(data='mm1', tag=t1, public=True)
784
>>> ManagedModel.objects.update(data='mm')
787
A values() or values_list() query across joined models must use outer joins
789
>>> Report.objects.values_list("creator__extra__info", flat=True).order_by("name")
792
Similarly for select_related(), joins beyond an initial nullable join must
793
use outer joins so that all results are included.
794
>>> Report.objects.select_related("creator", "creator__extra").order_by("name")
795
[<Report: r1>, <Report: r2>, <Report: r3>]
797
When there are multiple paths to a table from another table, we have to be
798
careful not to accidentally reuse an inappropriate join when using
799
select_related(). We used to return the parent's Detail record here by mistake.
801
>>> d1 = Detail.objects.create(data="d1")
802
>>> d2 = Detail.objects.create(data="d2")
803
>>> m1 = Member.objects.create(name="m1", details=d1)
804
>>> m2 = Member.objects.create(name="m2", details=d2)
805
>>> c1 = Child.objects.create(person=m2, parent=m1)
806
>>> obj = m1.children.select_related("person__details")[0]
807
>>> obj.person.details.data
810
Bug #7076 -- excluding shouldn't eliminate NULL entries.
811
>>> Item.objects.exclude(modified=time1).order_by('name')
812
[<Item: four>, <Item: three>, <Item: two>]
813
>>> Tag.objects.exclude(parent__name=t1.name)
814
[<Tag: t1>, <Tag: t4>, <Tag: t5>]
816
Bug #7181 -- ordering by related tables should accomodate nullable fields (this
817
test is a little tricky, since NULL ordering is database dependent. Instead, we
818
just count the number of results).
819
>>> len(Tag.objects.order_by('parent__name'))
822
Bug #7107 -- this shouldn't create an infinite loop.
823
>>> Valid.objects.all()
826
Empty querysets can be merged with others.
827
>>> Note.objects.none() | Note.objects.all()
828
[<Note: n1>, <Note: n2>, <Note: n3>]
829
>>> Note.objects.all() | Note.objects.none()
830
[<Note: n1>, <Note: n2>, <Note: n3>]
831
>>> Note.objects.none() & Note.objects.all()
833
>>> Note.objects.all() & Note.objects.none()
836
Bug #7204, #7506 -- make sure querysets with related fields can be pickled. If
837
this doesn't crash, it's a Good Thing.
838
>>> out = pickle.dumps(Item.objects.all())
840
We should also be able to pickle things that use select_related(). The only
841
tricky thing here is to ensure that we do the related selections properly after
843
>>> qs = Item.objects.select_related()
844
>>> query = qs.query.as_sql()[0]
845
>>> query2 = pickle.loads(pickle.dumps(qs.query))
846
>>> query2.as_sql()[0] == query
850
>>> n1.annotation_set.filter(Q(tag=t5) | Q(tag__children=t5) | Q(tag__children__children=t5))
854
>>> Related.objects.order_by('custom')
857
Bug #7448, #7707 -- Complex objects should be converted to strings before being
859
>>> Item.objects.filter(created__in=[time1, time2])
860
[<Item: one>, <Item: two>]
862
Bug #7698 -- People like to slice with '0' as the high-water mark.
863
>>> Item.objects.all()[0:0]
866
Bug #7411 - saving to db must work even with partially read result set in
869
>>> for num in range(2 * ITER_CHUNK_SIZE + 1):
870
... _ = Number.objects.create(num=num)
872
>>> for i, obj in enumerate(Number.objects.all()):
876
Bug #7759 -- count should work with a partially read result set.
877
>>> count = Number.objects.count()
878
>>> qs = Number.objects.all()
880
... qs.count() == count
884
Bug #7791 -- there were "issues" when ordering and distinct-ing on fields
885
related via ForeignKeys.
886
>>> len(Note.objects.order_by('extrainfo__info').distinct())
889
Bug #7778 - Model subclasses could not be deleted if a nullable foreign key
890
relates to a model that relates back.
892
>>> num_celebs = Celebrity.objects.count()
893
>>> tvc = TvChef.objects.create(name="Huey")
894
>>> Celebrity.objects.count() == num_celebs + 1
896
>>> f1 = Fan.objects.create(fan_of=tvc)
897
>>> f2 = Fan.objects.create(fan_of=tvc)
900
# The parent object should have been deleted as well.
901
>>> Celebrity.objects.count() == num_celebs
904
Bug #8283 -- Checking that applying filters after a disjunction works correctly.
905
>>> (ExtraInfo.objects.filter(note=n1)|ExtraInfo.objects.filter(info='e2')).filter(note=n1)
907
>>> (ExtraInfo.objects.filter(info='e2')|ExtraInfo.objects.filter(note=n1)).filter(note=n1)
910
Pickling of DateQuerySets used to fail
911
>>> qs = Item.objects.dates('created', 'month')
912
>>> _ = pickle.loads(pickle.dumps(qs))
914
Bug #8683 -- raise proper error when a DateQuerySet gets passed a wrong type of field
915
>>> Item.objects.dates('name', 'month')
916
Traceback (most recent call last):
918
AssertionError: 'name' isn't a DateField.
920
Bug #8597: regression tests for case-insensitive comparisons
921
>>> _ = Item.objects.create(name="a_b", created=datetime.datetime.now(), creator=a2, note=n1)
922
>>> _ = Item.objects.create(name="x%y", created=datetime.datetime.now(), creator=a2, note=n1)
923
>>> Item.objects.filter(name__iexact="A_b")
925
>>> Item.objects.filter(name__iexact="x%Y")
927
>>> Item.objects.filter(name__istartswith="A_b")
929
>>> Item.objects.filter(name__iendswith="A_b")
932
Bug #7302: reserved names are appropriately escaped
933
>>> _ = ReservedName.objects.create(name='a',order=42)
934
>>> _ = ReservedName.objects.create(name='b',order=37)
935
>>> ReservedName.objects.all().order_by('order')
936
[<ReservedName: b>, <ReservedName: a>]
937
>>> ReservedName.objects.extra(select={'stuff':'name'}, order_by=('order','stuff'))
938
[<ReservedName: b>, <ReservedName: a>]
940
Bug #8439 -- complex combinations of conjunctions, disjunctions and nullable
942
>>> Author.objects.filter(Q(item__note__extrainfo=e2)|Q(report=r1, name='xyz'))
944
>>> Author.objects.filter(Q(report=r1, name='xyz')|Q(item__note__extrainfo=e2))
946
>>> Annotation.objects.filter(Q(tag__parent=t1)|Q(notes__note='n1', name='a1'))
948
>>> xx = ExtraInfo.objects.create(info='xx', note=n3)
949
>>> Note.objects.filter(Q(extrainfo__author=a1)|Q(extrainfo=xx))
950
[<Note: n1>, <Note: n3>]
952
>>> q = Note.objects.filter(Q(extrainfo__author=a1)|Q(extrainfo=xx)).query
953
>>> len([x[2] for x in q.alias_map.values() if x[2] == q.LOUTER and q.alias_refcount[x[1]]])
958
# In Python 2.3 and the Python 2.6 beta releases, exceptions raised in __len__
959
# are swallowed (Python issue 1242657), so these cases return an empty list,
960
# rather than raising an exception. Not a lot we can do about that,
961
# unfortunately, due to the way Python handles list() calls internally. Thus,
962
# we skip the tests for Python 2.3 and 2.6.
963
if (2, 4) <= sys.version_info < (2, 6):
964
__test__["API_TESTS"] += """
965
# If you're not careful, it's possible to introduce infinite loops via default
966
# ordering on foreign keys in a cycle. We detect that.
967
>>> LoopX.objects.all()
968
Traceback (most recent call last):
970
FieldError: Infinite loop caused by ordering.
972
>>> LoopZ.objects.all()
973
Traceback (most recent call last):
975
FieldError: Infinite loop caused by ordering.
977
# Note that this doesn't cause an infinite loop, since the default ordering on
978
# the Tag model is empty (and thus defaults to using "id" for the related
980
>>> len(Tag.objects.order_by('parent'))
983
# ... but you can still order in a non-recursive fashion amongst linked fields
984
# (the previous test failed because the default ordering was recursive).
985
>>> LoopX.objects.all().order_by('y__x__y__x__id')
added
removed
tests/regressiontests/queries/models.py
