Debian Codegraph @

tests/lookup/tests.py

raw
1 import collections.abc
2 from datetime import datetime
3 from math import ceil
4 from operator import attrgetter
5 from unittest import mock, skipUnless
6
7 from django.core.exceptions import FieldError
8 from django.db import connection, models
9 from django.db.models import (
10 BooleanField,
11 Case,
12 Exists,
13 ExpressionWrapper,
14 F,
15 Max,
16 OuterRef,
17 Q,
18 Subquery,
19 Value,
20 When,
21 )
22 from django.db.models.functions import Abs, Cast, Length, Substr
23 from django.db.models.lookups import (
24 Exact,
25 GreaterThan,
26 GreaterThanOrEqual,
27 In,
28 IsNull,
29 LessThan,
30 LessThanOrEqual,
31 )
32 from django.test import TestCase, skipUnlessDBFeature
33 from django.test.utils import isolate_apps, register_lookup
34
35 from .models import (
36 Article,
37 Author,
38 Freebie,
39 Game,
40 IsNullWithNoneAsRHS,
41 Player,
42 Product,
43 Season,
44 Stock,
45 Tag,
46 )
47
48
49 class LookupTests(TestCase):
50 @classmethod
51 def setUpTestData(cls):
52 # Create a few Authors.
53 cls.au1 = Author.objects.create(name="Author 1", alias="a1", bio="x" * 4001)
54 cls.au2 = Author.objects.create(name="Author 2", alias="a2")
55 # Create a few Articles.
56 cls.a1 = Article.objects.create(
57 headline="Article 1",
58 pub_date=datetime(2005, 7, 26),
59 author=cls.au1,
60 slug="a1",
61 )
62 cls.a2 = Article.objects.create(
63 headline="Article 2",
64 pub_date=datetime(2005, 7, 27),
65 author=cls.au1,
66 slug="a2",
67 )
68 cls.a3 = Article.objects.create(
69 headline="Article 3",
70 pub_date=datetime(2005, 7, 27),
71 author=cls.au1,
72 slug="a3",
73 )
74 cls.a4 = Article.objects.create(
75 headline="Article 4",
76 pub_date=datetime(2005, 7, 28),
77 author=cls.au1,
78 slug="a4",
79 )
80 cls.a5 = Article.objects.create(
81 headline="Article 5",
82 pub_date=datetime(2005, 8, 1, 9, 0),
83 author=cls.au2,
84 slug="a5",
85 )
86 cls.a6 = Article.objects.create(
87 headline="Article 6",
88 pub_date=datetime(2005, 8, 1, 8, 0),
89 author=cls.au2,
90 slug="a6",
91 )
92 cls.a7 = Article.objects.create(
93 headline="Article 7",
94 pub_date=datetime(2005, 7, 27),
95 author=cls.au2,
96 slug="a7",
97 )
98 # Create a few Tags.
99 cls.t1 = Tag.objects.create(name="Tag 1")
100 cls.t1.articles.add(cls.a1, cls.a2, cls.a3)
101 cls.t2 = Tag.objects.create(name="Tag 2")
102 cls.t2.articles.add(cls.a3, cls.a4, cls.a5)
103 cls.t3 = Tag.objects.create(name="Tag 3")
104 cls.t3.articles.add(cls.a5, cls.a6, cls.a7)
105
106 def test_exists(self):
107 # We can use .exists() to check that there are some
108 self.assertTrue(Article.objects.exists())
109 for a in Article.objects.all():
110 a.delete()
111 # There should be none now!
112 self.assertFalse(Article.objects.exists())
113
114 def test_lookup_int_as_str(self):
115 # Integer value can be queried using string
116 self.assertSequenceEqual(
117 Article.objects.filter(id__iexact=str(self.a1.id)),
118 [self.a1],
119 )
120
121 @skipUnlessDBFeature("supports_date_lookup_using_string")
122 def test_lookup_date_as_str(self):
123 # A date lookup can be performed using a string search
124 self.assertSequenceEqual(
125 Article.objects.filter(pub_date__startswith="2005"),
126 [self.a5, self.a6, self.a4, self.a2, self.a3, self.a7, self.a1],
127 )
128
129 def test_iterator(self):
130 # Each QuerySet gets iterator(), which is a generator that "lazily"
131 # returns results using database-level iteration.
132 self.assertIsInstance(Article.objects.iterator(), collections.abc.Iterator)
133
134 self.assertQuerySetEqual(
135 Article.objects.iterator(),
136 [
137 "Article 5",
138 "Article 6",
139 "Article 4",
140 "Article 2",
141 "Article 3",
142 "Article 7",
143 "Article 1",
144 ],
145 transform=attrgetter("headline"),
146 )
147 # iterator() can be used on any QuerySet.
148 self.assertQuerySetEqual(
149 Article.objects.filter(headline__endswith="4").iterator(),
150 ["Article 4"],
151 transform=attrgetter("headline"),
152 )
153
154 def test_count(self):
155 # count() returns the number of objects matching search criteria.
156 self.assertEqual(Article.objects.count(), 7)
157 self.assertEqual(
158 Article.objects.filter(pub_date__exact=datetime(2005, 7, 27)).count(), 3
159 )
160 self.assertEqual(
161 Article.objects.filter(headline__startswith="Blah blah").count(), 0
162 )
163
164 # count() should respect sliced query sets.
165 articles = Article.objects.all()
166 self.assertEqual(articles.count(), 7)
167 self.assertEqual(articles[:4].count(), 4)
168 self.assertEqual(articles[1:100].count(), 6)
169 self.assertEqual(articles[10:100].count(), 0)
170
171 # Date and date/time lookups can also be done with strings.
172 self.assertEqual(
173 Article.objects.filter(pub_date__exact="2005-07-27 00:00:00").count(), 3
174 )
175
176 def test_in_bulk(self):
177 # in_bulk() takes a list of IDs and returns a dictionary mapping IDs to objects.
178 arts = Article.objects.in_bulk([self.a1.id, self.a2.id])
179 self.assertEqual(arts[self.a1.id], self.a1)
180 self.assertEqual(arts[self.a2.id], self.a2)
181 self.assertEqual(
182 Article.objects.in_bulk(),
183 {
184 self.a1.id: self.a1,
185 self.a2.id: self.a2,
186 self.a3.id: self.a3,
187 self.a4.id: self.a4,
188 self.a5.id: self.a5,
189 self.a6.id: self.a6,
190 self.a7.id: self.a7,
191 },
192 )
193 self.assertEqual(Article.objects.in_bulk([self.a3.id]), {self.a3.id: self.a3})
194 self.assertEqual(Article.objects.in_bulk({self.a3.id}), {self.a3.id: self.a3})
195 self.assertEqual(
196 Article.objects.in_bulk(frozenset([self.a3.id])), {self.a3.id: self.a3}
197 )
198 self.assertEqual(Article.objects.in_bulk((self.a3.id,)), {self.a3.id: self.a3})
199 self.assertEqual(Article.objects.in_bulk([1000]), {})
200 self.assertEqual(Article.objects.in_bulk([]), {})
201 self.assertEqual(
202 Article.objects.in_bulk(iter([self.a1.id])), {self.a1.id: self.a1}
203 )
204 self.assertEqual(Article.objects.in_bulk(iter([])), {})
205 with self.assertRaises(TypeError):
206 Article.objects.in_bulk(headline__startswith="Blah")
207
208 def test_in_bulk_lots_of_ids(self):
209 test_range = 2000
210 max_query_params = connection.features.max_query_params
211 expected_num_queries = (
212 ceil(test_range / max_query_params) if max_query_params else 1
213 )
214 Author.objects.bulk_create(
215 [Author() for i in range(test_range - Author.objects.count())]
216 )
217 authors = {author.pk: author for author in Author.objects.all()}
218 with self.assertNumQueries(expected_num_queries):
219 self.assertEqual(Author.objects.in_bulk(authors), authors)
220
221 def test_in_bulk_with_field(self):
222 self.assertEqual(
223 Article.objects.in_bulk(
224 [self.a1.slug, self.a2.slug, self.a3.slug], field_name="slug"
225 ),
226 {
227 self.a1.slug: self.a1,
228 self.a2.slug: self.a2,
229 self.a3.slug: self.a3,
230 },
231 )
232
233 def test_in_bulk_meta_constraint(self):
234 season_2011 = Season.objects.create(year=2011)
235 season_2012 = Season.objects.create(year=2012)
236 Season.objects.create(year=2013)
237 self.assertEqual(
238 Season.objects.in_bulk(
239 [season_2011.year, season_2012.year],
240 field_name="year",
241 ),
242 {season_2011.year: season_2011, season_2012.year: season_2012},
243 )
244
245 def test_in_bulk_non_unique_field(self):
246 msg = "in_bulk()'s field_name must be a unique field but 'author' isn't."
247 with self.assertRaisesMessage(ValueError, msg):
248 Article.objects.in_bulk([self.au1], field_name="author")
249
250 @skipUnlessDBFeature("can_distinct_on_fields")
251 def test_in_bulk_preserve_ordering(self):
252 articles = (
253 Article.objects.order_by("author_id", "-pub_date")
254 .distinct("author_id")
255 .in_bulk([self.au1.id, self.au2.id], field_name="author_id")
256 )
257 self.assertEqual(
258 articles,
259 {self.au1.id: self.a4, self.au2.id: self.a5},
260 )
261
262 @skipUnlessDBFeature("can_distinct_on_fields")
263 def test_in_bulk_preserve_ordering_with_batch_size(self):
264 qs = Article.objects.order_by("author_id", "-pub_date").distinct("author_id")
265 with (
266 mock.patch.object(connection.features.__class__, "max_query_params", 1),
267 self.assertNumQueries(2),
268 ):
269 self.assertEqual(
270 qs.in_bulk([self.au1.id, self.au2.id], field_name="author_id"),
271 {self.au1.id: self.a4, self.au2.id: self.a5},
272 )
273
274 @skipUnlessDBFeature("can_distinct_on_fields")
275 def test_in_bulk_distinct_field(self):
276 self.assertEqual(
277 Article.objects.order_by("headline")
278 .distinct("headline")
279 .in_bulk(
280 [self.a1.headline, self.a5.headline],
281 field_name="headline",
282 ),
283 {self.a1.headline: self.a1, self.a5.headline: self.a5},
284 )
285
286 @skipUnlessDBFeature("can_distinct_on_fields")
287 def test_in_bulk_multiple_distinct_field(self):
288 msg = "in_bulk()'s field_name must be a unique field but 'pub_date' isn't."
289 with self.assertRaisesMessage(ValueError, msg):
290 Article.objects.order_by("headline", "pub_date").distinct(
291 "headline",
292 "pub_date",
293 ).in_bulk(field_name="pub_date")
294
295 @isolate_apps("lookup")
296 def test_in_bulk_non_unique_meta_constaint(self):
297 class Model(models.Model):
298 ean = models.CharField(max_length=100)
299 brand = models.CharField(max_length=100)
300 name = models.CharField(max_length=80)
301
302 class Meta:
303 constraints = [
304 models.UniqueConstraint(
305 fields=["ean"],
306 name="partial_ean_unique",
307 condition=models.Q(is_active=True),
308 ),
309 models.UniqueConstraint(
310 fields=["brand", "name"],
311 name="together_brand_name_unique",
312 ),
313 ]
314
315 msg = "in_bulk()'s field_name must be a unique field but '%s' isn't."
316 for field_name in ["brand", "ean"]:
317 with self.subTest(field_name=field_name):
318 with self.assertRaisesMessage(ValueError, msg % field_name):
319 Model.objects.in_bulk(field_name=field_name)
320
321 def test_in_bulk_sliced_queryset(self):
322 msg = "Cannot use 'limit' or 'offset' with in_bulk()."
323 with self.assertRaisesMessage(TypeError, msg):
324 Article.objects.all()[0:5].in_bulk([self.a1.id, self.a2.id])
325
326 def test_in_bulk_not_model_iterable(self):
327 msg = "in_bulk() cannot be used with values() or values_list()."
328 with self.assertRaisesMessage(TypeError, msg):
329 Author.objects.values().in_bulk()
330 with self.assertRaisesMessage(TypeError, msg):
331 Author.objects.values_list().in_bulk()
332
333 def test_values(self):
334 # values() returns a list of dictionaries instead of object instances --
335 # and you can specify which fields you want to retrieve.
336 self.assertSequenceEqual(
337 Article.objects.values("headline"),
338 [
339 {"headline": "Article 5"},
340 {"headline": "Article 6"},
341 {"headline": "Article 4"},
342 {"headline": "Article 2"},
343 {"headline": "Article 3"},
344 {"headline": "Article 7"},
345 {"headline": "Article 1"},
346 ],
347 )
348 self.assertSequenceEqual(
349 Article.objects.filter(pub_date__exact=datetime(2005, 7, 27)).values("id"),
350 [{"id": self.a2.id}, {"id": self.a3.id}, {"id": self.a7.id}],
351 )
352 self.assertSequenceEqual(
353 Article.objects.values("id", "headline"),
354 [
355 {"id": self.a5.id, "headline": "Article 5"},
356 {"id": self.a6.id, "headline": "Article 6"},
357 {"id": self.a4.id, "headline": "Article 4"},
358 {"id": self.a2.id, "headline": "Article 2"},
359 {"id": self.a3.id, "headline": "Article 3"},
360 {"id": self.a7.id, "headline": "Article 7"},
361 {"id": self.a1.id, "headline": "Article 1"},
362 ],
363 )
364 # You can use values() with iterator() for memory savings,
365 # because iterator() uses database-level iteration.
366 self.assertSequenceEqual(
367 list(Article.objects.values("id", "headline").iterator()),
368 [
369 {"headline": "Article 5", "id": self.a5.id},
370 {"headline": "Article 6", "id": self.a6.id},
371 {"headline": "Article 4", "id": self.a4.id},
372 {"headline": "Article 2", "id": self.a2.id},
373 {"headline": "Article 3", "id": self.a3.id},
374 {"headline": "Article 7", "id": self.a7.id},
375 {"headline": "Article 1", "id": self.a1.id},
376 ],
377 )
378 # The values() method works with "extra" fields specified in extra(select).
379 self.assertSequenceEqual(
380 Article.objects.extra(select={"id_plus_one": "id + 1"}).values(
381 "id", "id_plus_one"
382 ),
383 [
384 {"id": self.a5.id, "id_plus_one": self.a5.id + 1},
385 {"id": self.a6.id, "id_plus_one": self.a6.id + 1},
386 {"id": self.a4.id, "id_plus_one": self.a4.id + 1},
387 {"id": self.a2.id, "id_plus_one": self.a2.id + 1},
388 {"id": self.a3.id, "id_plus_one": self.a3.id + 1},
389 {"id": self.a7.id, "id_plus_one": self.a7.id + 1},
390 {"id": self.a1.id, "id_plus_one": self.a1.id + 1},
391 ],
392 )
393 data = {
394 "id_plus_one": "id+1",
395 "id_plus_two": "id+2",
396 "id_plus_three": "id+3",
397 "id_plus_four": "id+4",
398 "id_plus_five": "id+5",
399 "id_plus_six": "id+6",
400 "id_plus_seven": "id+7",
401 "id_plus_eight": "id+8",
402 }
403 self.assertSequenceEqual(
404 Article.objects.filter(id=self.a1.id).extra(select=data).values(*data),
405 [
406 {
407 "id_plus_one": self.a1.id + 1,
408 "id_plus_two": self.a1.id + 2,
409 "id_plus_three": self.a1.id + 3,
410 "id_plus_four": self.a1.id + 4,
411 "id_plus_five": self.a1.id + 5,
412 "id_plus_six": self.a1.id + 6,
413 "id_plus_seven": self.a1.id + 7,
414 "id_plus_eight": self.a1.id + 8,
415 }
416 ],
417 )
418 # You can specify fields from forward and reverse relations, just like filter().
419 self.assertSequenceEqual(
420 Article.objects.values("headline", "author__name"),
421 [
422 {"headline": self.a5.headline, "author__name": self.au2.name},
423 {"headline": self.a6.headline, "author__name": self.au2.name},
424 {"headline": self.a4.headline, "author__name": self.au1.name},
425 {"headline": self.a2.headline, "author__name": self.au1.name},
426 {"headline": self.a3.headline, "author__name": self.au1.name},
427 {"headline": self.a7.headline, "author__name": self.au2.name},
428 {"headline": self.a1.headline, "author__name": self.au1.name},
429 ],
430 )
431 self.assertSequenceEqual(
432 Author.objects.values("name", "article__headline").order_by(
433 "name", "article__headline"
434 ),
435 [
436 {"name": self.au1.name, "article__headline": self.a1.headline},
437 {"name": self.au1.name, "article__headline": self.a2.headline},
438 {"name": self.au1.name, "article__headline": self.a3.headline},
439 {"name": self.au1.name, "article__headline": self.a4.headline},
440 {"name": self.au2.name, "article__headline": self.a5.headline},
441 {"name": self.au2.name, "article__headline": self.a6.headline},
442 {"name": self.au2.name, "article__headline": self.a7.headline},
443 ],
444 )
445 self.assertSequenceEqual(
446 (
447 Author.objects.values(
448 "name", "article__headline", "article__tag__name"
449 ).order_by("name", "article__headline", "article__tag__name")
450 ),
451 [
452 {
453 "name": self.au1.name,
454 "article__headline": self.a1.headline,
455 "article__tag__name": self.t1.name,
456 },
457 {
458 "name": self.au1.name,
459 "article__headline": self.a2.headline,
460 "article__tag__name": self.t1.name,
461 },
462 {
463 "name": self.au1.name,
464 "article__headline": self.a3.headline,
465 "article__tag__name": self.t1.name,
466 },
467 {
468 "name": self.au1.name,
469 "article__headline": self.a3.headline,
470 "article__tag__name": self.t2.name,
471 },
472 {
473 "name": self.au1.name,
474 "article__headline": self.a4.headline,
475 "article__tag__name": self.t2.name,
476 },
477 {
478 "name": self.au2.name,
479 "article__headline": self.a5.headline,
480 "article__tag__name": self.t2.name,
481 },
482 {
483 "name": self.au2.name,
484 "article__headline": self.a5.headline,
485 "article__tag__name": self.t3.name,
486 },
487 {
488 "name": self.au2.name,
489 "article__headline": self.a6.headline,
490 "article__tag__name": self.t3.name,
491 },
492 {
493 "name": self.au2.name,
494 "article__headline": self.a7.headline,
495 "article__tag__name": self.t3.name,
496 },
497 ],
498 )
499 # However, an exception FieldDoesNotExist will be thrown if you specify
500 # a nonexistent field name in values() (a field that is neither in the
501 # model nor in extra(select)).
502 msg = (
503 "Cannot resolve keyword 'id_plus_two' into field. Choices are: "
504 "author, author_id, headline, id, id_plus_one, pub_date, slug, tag"
505 )
506 with self.assertRaisesMessage(FieldError, msg):
507 Article.objects.extra(select={"id_plus_one": "id + 1"}).values(
508 "id", "id_plus_two"
509 )
510 # If you don't specify field names to values(), all are returned.
511 self.assertSequenceEqual(
512 Article.objects.filter(id=self.a5.id).values(),
513 [
514 {
515 "id": self.a5.id,
516 "author_id": self.au2.id,
517 "headline": "Article 5",
518 "pub_date": datetime(2005, 8, 1, 9, 0),
519 "slug": "a5",
520 }
521 ],
522 )
523
524 def test_values_list(self):
525 # values_list() is similar to values(), except that the results are
526 # returned as a list of tuples, rather than a list of dictionaries.
527 # Within each tuple, the order of the elements is the same as the order
528 # of fields in the values_list() call.
529 self.assertSequenceEqual(
530 Article.objects.values_list("headline"),
531 [
532 ("Article 5",),
533 ("Article 6",),
534 ("Article 4",),
535 ("Article 2",),
536 ("Article 3",),
537 ("Article 7",),
538 ("Article 1",),
539 ],
540 )
541 self.assertSequenceEqual(
542 Article.objects.values_list("id").order_by("id"),
543 [
544 (self.a1.id,),
545 (self.a2.id,),
546 (self.a3.id,),
547 (self.a4.id,),
548 (self.a5.id,),
549 (self.a6.id,),
550 (self.a7.id,),
551 ],
552 )
553 self.assertSequenceEqual(
554 Article.objects.values_list("id", flat=True).order_by("id"),
555 [
556 self.a1.id,
557 self.a2.id,
558 self.a3.id,
559 self.a4.id,
560 self.a5.id,
561 self.a6.id,
562 self.a7.id,
563 ],
564 )
565 self.assertSequenceEqual(
566 Article.objects.extra(select={"id_plus_one": "id+1"})
567 .order_by("id")
568 .values_list("id"),
569 [
570 (self.a1.id,),
571 (self.a2.id,),
572 (self.a3.id,),
573 (self.a4.id,),
574 (self.a5.id,),
575 (self.a6.id,),
576 (self.a7.id,),
577 ],
578 )
579 self.assertSequenceEqual(
580 Article.objects.extra(select={"id_plus_one": "id+1"})
581 .order_by("id")
582 .values_list("id_plus_one", "id"),
583 [
584 (self.a1.id + 1, self.a1.id),
585 (self.a2.id + 1, self.a2.id),
586 (self.a3.id + 1, self.a3.id),
587 (self.a4.id + 1, self.a4.id),
588 (self.a5.id + 1, self.a5.id),
589 (self.a6.id + 1, self.a6.id),
590 (self.a7.id + 1, self.a7.id),
591 ],
592 )
593 self.assertSequenceEqual(
594 Article.objects.extra(select={"id_plus_one": "id+1"})
595 .order_by("id")
596 .values_list("id", "id_plus_one"),
597 [
598 (self.a1.id, self.a1.id + 1),
599 (self.a2.id, self.a2.id + 1),
600 (self.a3.id, self.a3.id + 1),
601 (self.a4.id, self.a4.id + 1),
602 (self.a5.id, self.a5.id + 1),
603 (self.a6.id, self.a6.id + 1),
604 (self.a7.id, self.a7.id + 1),
605 ],
606 )
607 args = ("name", "article__headline", "article__tag__name")
608 self.assertSequenceEqual(
609 Author.objects.values_list(*args).order_by(*args),
610 [
611 (self.au1.name, self.a1.headline, self.t1.name),
612 (self.au1.name, self.a2.headline, self.t1.name),
613 (self.au1.name, self.a3.headline, self.t1.name),
614 (self.au1.name, self.a3.headline, self.t2.name),
615 (self.au1.name, self.a4.headline, self.t2.name),
616 (self.au2.name, self.a5.headline, self.t2.name),
617 (self.au2.name, self.a5.headline, self.t3.name),
618 (self.au2.name, self.a6.headline, self.t3.name),
619 (self.au2.name, self.a7.headline, self.t3.name),
620 ],
621 )
622 with self.assertRaises(TypeError):
623 Article.objects.values_list("id", "headline", flat=True)
624
625 def test_get_next_previous_by(self):
626 # Every DateField and DateTimeField creates get_next_by_FOO() and
627 # get_previous_by_FOO() methods. In the case of identical date values,
628 # these methods will use the ID as a fallback check. This guarantees
629 # that no records are skipped or duplicated.
630 self.assertEqual(repr(self.a1.get_next_by_pub_date()), "<Article: Article 2>")
631 self.assertEqual(repr(self.a2.get_next_by_pub_date()), "<Article: Article 3>")
632 self.assertEqual(
633 repr(self.a2.get_next_by_pub_date(headline__endswith="6")),
634 "<Article: Article 6>",
635 )
636 self.assertEqual(repr(self.a3.get_next_by_pub_date()), "<Article: Article 7>")
637 self.assertEqual(repr(self.a4.get_next_by_pub_date()), "<Article: Article 6>")
638 with self.assertRaises(Article.DoesNotExist):
639 self.a5.get_next_by_pub_date()
640 self.assertEqual(repr(self.a6.get_next_by_pub_date()), "<Article: Article 5>")
641 self.assertEqual(repr(self.a7.get_next_by_pub_date()), "<Article: Article 4>")
642
643 self.assertEqual(
644 repr(self.a7.get_previous_by_pub_date()), "<Article: Article 3>"
645 )
646 self.assertEqual(
647 repr(self.a6.get_previous_by_pub_date()), "<Article: Article 4>"
648 )
649 self.assertEqual(
650 repr(self.a5.get_previous_by_pub_date()), "<Article: Article 6>"
651 )
652 self.assertEqual(
653 repr(self.a4.get_previous_by_pub_date()), "<Article: Article 7>"
654 )
655 self.assertEqual(
656 repr(self.a3.get_previous_by_pub_date()), "<Article: Article 2>"
657 )
658 self.assertEqual(
659 repr(self.a2.get_previous_by_pub_date()), "<Article: Article 1>"
660 )
661
662 def test_escaping(self):
663 # Underscores, percent signs and backslashes have special meaning in the
664 # underlying SQL code, but Django handles the quoting of them automatically.
665 a8 = Article.objects.create(
666 headline="Article_ with underscore", pub_date=datetime(2005, 11, 20)
667 )
668
669 self.assertSequenceEqual(
670 Article.objects.filter(headline__startswith="Article"),
671 [a8, self.a5, self.a6, self.a4, self.a2, self.a3, self.a7, self.a1],
672 )
673 self.assertSequenceEqual(
674 Article.objects.filter(headline__startswith="Article_"),
675 [a8],
676 )
677 a9 = Article.objects.create(
678 headline="Article% with percent sign", pub_date=datetime(2005, 11, 21)
679 )
680 self.assertSequenceEqual(
681 Article.objects.filter(headline__startswith="Article"),
682 [a9, a8, self.a5, self.a6, self.a4, self.a2, self.a3, self.a7, self.a1],
683 )
684 self.assertSequenceEqual(
685 Article.objects.filter(headline__startswith="Article%"),
686 [a9],
687 )
688 a10 = Article.objects.create(
689 headline="Article with \\ backslash", pub_date=datetime(2005, 11, 22)
690 )
691 self.assertSequenceEqual(
692 Article.objects.filter(headline__contains="\\"),
693 [a10],
694 )
695
696 def test_exclude(self):
697 a8 = Article.objects.create(
698 headline="Article_ with underscore", pub_date=datetime(2005, 11, 20)
699 )
700 a9 = Article.objects.create(
701 headline="Article% with percent sign", pub_date=datetime(2005, 11, 21)
702 )
703 a10 = Article.objects.create(
704 headline="Article with \\ backslash", pub_date=datetime(2005, 11, 22)
705 )
706 # exclude() is the opposite of filter() when doing lookups:
707 self.assertSequenceEqual(
708 Article.objects.filter(headline__contains="Article").exclude(
709 headline__contains="with"
710 ),
711 [self.a5, self.a6, self.a4, self.a2, self.a3, self.a7, self.a1],
712 )
713 self.assertSequenceEqual(
714 Article.objects.exclude(headline__startswith="Article_"),
715 [a10, a9, self.a5, self.a6, self.a4, self.a2, self.a3, self.a7, self.a1],
716 )
717 self.assertSequenceEqual(
718 Article.objects.exclude(headline="Article 7"),
719 [a10, a9, a8, self.a5, self.a6, self.a4, self.a2, self.a3, self.a1],
720 )
721
722 def test_none(self):
723 # none() returns a QuerySet that behaves like any other QuerySet object
724 self.assertSequenceEqual(Article.objects.none(), [])
725 self.assertSequenceEqual(
726 Article.objects.none().filter(headline__startswith="Article"), []
727 )
728 self.assertSequenceEqual(
729 Article.objects.filter(headline__startswith="Article").none(), []
730 )
731 self.assertEqual(Article.objects.none().count(), 0)
732 self.assertEqual(
733 Article.objects.none().update(headline="This should not take effect"), 0
734 )
735 self.assertSequenceEqual(list(Article.objects.none().iterator()), [])
736
737 def test_in(self):
738 self.assertSequenceEqual(
739 Article.objects.exclude(id__in=[]),
740 [self.a5, self.a6, self.a4, self.a2, self.a3, self.a7, self.a1],
741 )
742
743 def test_in_empty_list(self):
744 self.assertSequenceEqual(Article.objects.filter(id__in=[]), [])
745
746 def test_in_different_database(self):
747 with self.assertRaisesMessage(
748 ValueError,
749 "Subqueries aren't allowed across different databases. Force the "
750 "inner query to be evaluated using `list(inner_query)`.",
751 ):
752 list(Article.objects.filter(id__in=Article.objects.using("other").all()))
753
754 def test_in_keeps_value_ordering(self):
755 query = (
756 Article.objects.filter(slug__in=["a%d" % i for i in range(1, 8)])
757 .values("pk")
758 .query
759 )
760 self.assertIn(" IN (a1, a2, a3, a4, a5, a6, a7) ", str(query))
761
762 def test_in_ignore_none(self):
763 with self.assertNumQueries(1) as ctx:
764 self.assertSequenceEqual(
765 Article.objects.filter(id__in=[None, self.a1.id]),
766 [self.a1],
767 )
768 sql = ctx.captured_queries[0]["sql"]
769 self.assertIn("IN (%s)" % self.a1.pk, sql)
770
771 def test_in_ignore_solo_none(self):
772 with self.assertNumQueries(0):
773 self.assertSequenceEqual(Article.objects.filter(id__in=[None]), [])
774
775 def test_in_ignore_none_with_unhashable_items(self):
776 class UnhashableInt(int):
777 __hash__ = None
778
779 with self.assertNumQueries(1) as ctx:
780 self.assertSequenceEqual(
781 Article.objects.filter(id__in=[None, UnhashableInt(self.a1.id)]),
782 [self.a1],
783 )
784 sql = ctx.captured_queries[0]["sql"]
785 self.assertIn("IN (%s)" % self.a1.pk, sql)
786
787 def test_in_select_mismatch(self):
788 msg = (
789 "The QuerySet value for the 'in' lookup must have 1 "
790 "selected fields (received 2)"
791 )
792 with self.assertRaisesMessage(ValueError, msg):
793 Article.objects.filter(id__in=Article.objects.values("id", "headline"))
794
795 def test_error_messages(self):
796 # Programming errors are pointed out with nice error messages
797 with self.assertRaisesMessage(
798 FieldError,
799 "Cannot resolve keyword 'pub_date_year' into field. Choices are: "
800 "author, author_id, headline, id, pub_date, slug, tag",
801 ):
802 Article.objects.filter(pub_date_year="2005").count()
803
804 def test_unsupported_lookups(self):
805 with self.assertRaisesMessage(
806 FieldError,
807 "Unsupported lookup 'starts' for CharField or join on the field "
808 "not permitted, perhaps you meant startswith or istartswith?",
809 ):
810 Article.objects.filter(headline__starts="Article")
811
812 with self.assertRaisesMessage(
813 FieldError,
814 "Unsupported lookup 'is_null' for DateTimeField or join on the field "
815 "not permitted, perhaps you meant isnull?",
816 ):
817 Article.objects.filter(pub_date__is_null=True)
818
819 with self.assertRaisesMessage(
820 FieldError,
821 "Unsupported lookup 'gobbledygook' for DateTimeField or join on the field "
822 "not permitted.",
823 ):
824 Article.objects.filter(pub_date__gobbledygook="blahblah")
825
826 with self.assertRaisesMessage(
827 FieldError,
828 "Unsupported lookup 'gt__foo' for DateTimeField or join on the field "
829 "not permitted, perhaps you meant gt or gte?",
830 ):
831 Article.objects.filter(pub_date__gt__foo="blahblah")
832
833 with self.assertRaisesMessage(
834 FieldError,
835 "Unsupported lookup 'gt__' for DateTimeField or join on the field "
836 "not permitted, perhaps you meant gt or gte?",
837 ):
838 Article.objects.filter(pub_date__gt__="blahblah")
839
840 with self.assertRaisesMessage(
841 FieldError,
842 "Unsupported lookup 'gt__lt' for DateTimeField or join on the field "
843 "not permitted, perhaps you meant gt or gte?",
844 ):
845 Article.objects.filter(pub_date__gt__lt="blahblah")
846
847 with self.assertRaisesMessage(
848 FieldError,
849 "Unsupported lookup 'gt__lt__foo' for DateTimeField or join"
850 " on the field not permitted, perhaps you meant gt or gte?",
851 ):
852 Article.objects.filter(pub_date__gt__lt__foo="blahblah")
853
854 def test_unsupported_lookups_custom_lookups(self):
855 slug_field = Article._meta.get_field("slug")
856 msg = (
857 "Unsupported lookup 'lengtp' for SlugField or join on the field not "
858 "permitted, perhaps you meant length?"
859 )
860 with self.assertRaisesMessage(FieldError, msg):
861 with register_lookup(slug_field, Length):
862 Article.objects.filter(slug__lengtp=20)
863
864 def test_relation_nested_lookup_error(self):
865 # An invalid nested lookup on a related field raises a useful error.
866 msg = (
867 "Unsupported lookup 'editor__name' for ForeignKey or join on the field not "
868 "permitted."
869 )
870 with self.assertRaisesMessage(FieldError, msg):
871 Article.objects.filter(author__editor__name="James")
872 msg = (
873 "Unsupported lookup 'foo' for ForeignKey or join on the field not "
874 "permitted."
875 )
876 with self.assertRaisesMessage(FieldError, msg):
877 Tag.objects.filter(articles__foo="bar")
878
879 def test_unsupported_lookup_reverse_foreign_key(self):
880 msg = (
881 "Unsupported lookup 'title' for ManyToOneRel or join on the field not "
882 "permitted."
883 )
884 with self.assertRaisesMessage(FieldError, msg):
885 Author.objects.filter(article__title="Article 1")
886
887 def test_unsupported_lookup_reverse_foreign_key_custom_lookups(self):
888 msg = (
889 "Unsupported lookup 'abspl' for ManyToOneRel or join on the field not "
890 "permitted, perhaps you meant abspk?"
891 )
892 fk_field = Article._meta.get_field("author")
893 with self.assertRaisesMessage(FieldError, msg):
894 with register_lookup(fk_field, Abs, lookup_name="abspk"):
895 Author.objects.filter(article__abspl=2)
896
897 def test_filter_by_reverse_related_field_transform(self):
898 fk_field = Article._meta.get_field("author")
899 with register_lookup(fk_field, Abs):
900 self.assertSequenceEqual(
901 Author.objects.filter(article__abs=self.a1.pk), [self.au1]
902 )
903
904 def test_regex(self):
905 # Create some articles with a bit more interesting headlines for
906 # testing field lookups.
907 Article.objects.all().delete()
908 now = datetime.now()
909 Article.objects.bulk_create(
910 [
911 Article(pub_date=now, headline="f"),
912 Article(pub_date=now, headline="fo"),
913 Article(pub_date=now, headline="foo"),
914 Article(pub_date=now, headline="fooo"),
915 Article(pub_date=now, headline="hey-Foo"),
916 Article(pub_date=now, headline="bar"),
917 Article(pub_date=now, headline="AbBa"),
918 Article(pub_date=now, headline="baz"),
919 Article(pub_date=now, headline="baxZ"),
920 ]
921 )
922 # zero-or-more
923 self.assertQuerySetEqual(
924 Article.objects.filter(headline__regex=r"fo*"),
925 Article.objects.filter(headline__in=["f", "fo", "foo", "fooo"]),
926 )
927 self.assertQuerySetEqual(
928 Article.objects.filter(headline__iregex=r"fo*"),
929 Article.objects.filter(headline__in=["f", "fo", "foo", "fooo", "hey-Foo"]),
930 )
931 # one-or-more
932 self.assertQuerySetEqual(
933 Article.objects.filter(headline__regex=r"fo+"),
934 Article.objects.filter(headline__in=["fo", "foo", "fooo"]),
935 )
936 # wildcard
937 self.assertQuerySetEqual(
938 Article.objects.filter(headline__regex=r"fooo?"),
939 Article.objects.filter(headline__in=["foo", "fooo"]),
940 )
941 # leading anchor
942 self.assertQuerySetEqual(
943 Article.objects.filter(headline__regex=r"^b"),
944 Article.objects.filter(headline__in=["bar", "baxZ", "baz"]),
945 )
946 self.assertQuerySetEqual(
947 Article.objects.filter(headline__iregex=r"^a"),
948 Article.objects.filter(headline="AbBa"),
949 )
950 # trailing anchor
951 self.assertQuerySetEqual(
952 Article.objects.filter(headline__regex=r"z$"),
953 Article.objects.filter(headline="baz"),
954 )
955 self.assertQuerySetEqual(
956 Article.objects.filter(headline__iregex=r"z$"),
957 Article.objects.filter(headline__in=["baxZ", "baz"]),
958 )
959 # character sets
960 self.assertQuerySetEqual(
961 Article.objects.filter(headline__regex=r"ba[rz]"),
962 Article.objects.filter(headline__in=["bar", "baz"]),
963 )
964 self.assertQuerySetEqual(
965 Article.objects.filter(headline__regex=r"ba.[RxZ]"),
966 Article.objects.filter(headline="baxZ"),
967 )
968 self.assertQuerySetEqual(
969 Article.objects.filter(headline__iregex=r"ba[RxZ]"),
970 Article.objects.filter(headline__in=["bar", "baxZ", "baz"]),
971 )
972
973 # and more articles:
974 Article.objects.bulk_create(
975 [
976 Article(pub_date=now, headline="foobar"),
977 Article(pub_date=now, headline="foobaz"),
978 Article(pub_date=now, headline="ooF"),
979 Article(pub_date=now, headline="foobarbaz"),
980 Article(pub_date=now, headline="zoocarfaz"),
981 Article(pub_date=now, headline="barfoobaz"),
982 Article(pub_date=now, headline="bazbaRFOO"),
983 ]
984 )
985
986 # alternation
987 self.assertQuerySetEqual(
988 Article.objects.filter(headline__regex=r"oo(f|b)"),
989 Article.objects.filter(
990 headline__in=[
991 "barfoobaz",
992 "foobar",
993 "foobarbaz",
994 "foobaz",
995 ]
996 ),
997 )
998 self.assertQuerySetEqual(
999 Article.objects.filter(headline__iregex=r"oo(f|b)"),
1000 Article.objects.filter(
1001 headline__in=[
1002 "barfoobaz",
1003 "foobar",
1004 "foobarbaz",
1005 "foobaz",
1006 "ooF",
1007 ]
1008 ),
1009 )
1010 self.assertQuerySetEqual(
1011 Article.objects.filter(headline__regex=r"^foo(f|b)"),
1012 Article.objects.filter(headline__in=["foobar", "foobarbaz", "foobaz"]),
1013 )
1014
1015 # greedy matching
1016 self.assertQuerySetEqual(
1017 Article.objects.filter(headline__regex=r"b.*az"),
1018 Article.objects.filter(
1019 headline__in=[
1020 "barfoobaz",
1021 "baz",
1022 "bazbaRFOO",
1023 "foobarbaz",
1024 "foobaz",
1025 ]
1026 ),
1027 )
1028 self.assertQuerySetEqual(
1029 Article.objects.filter(headline__iregex=r"b.*ar"),
1030 Article.objects.filter(
1031 headline__in=[
1032 "bar",
1033 "barfoobaz",
1034 "bazbaRFOO",
1035 "foobar",
1036 "foobarbaz",
1037 ]
1038 ),
1039 )
1040
1041 @skipUnlessDBFeature("supports_regex_backreferencing")
1042 def test_regex_backreferencing(self):
1043 # grouping and backreferences
1044 now = datetime.now()
1045 Article.objects.bulk_create(
1046 [
1047 Article(pub_date=now, headline="foobar"),
1048 Article(pub_date=now, headline="foobaz"),
1049 Article(pub_date=now, headline="ooF"),
1050 Article(pub_date=now, headline="foobarbaz"),
1051 Article(pub_date=now, headline="zoocarfaz"),
1052 Article(pub_date=now, headline="barfoobaz"),
1053 Article(pub_date=now, headline="bazbaRFOO"),
1054 ]
1055 )
1056 self.assertQuerySetEqual(
1057 Article.objects.filter(headline__regex=r"b(.).*b\1").values_list(
1058 "headline", flat=True
1059 ),
1060 ["barfoobaz", "bazbaRFOO", "foobarbaz"],
1061 )
1062
1063 def test_regex_null(self):
1064 """
1065 A regex lookup does not fail on null/None values
1066 """
1067 Season.objects.create(year=2012, gt=None)
1068 self.assertQuerySetEqual(Season.objects.filter(gt__regex=r"^$"), [])
1069
1070 def test_textfield_exact_null(self):
1071 with self.assertNumQueries(1) as ctx:
1072 self.assertSequenceEqual(Author.objects.filter(bio=None), [self.au2])
1073 # Columns with IS NULL condition are not wrapped (except PostgreSQL).
1074 bio_column = connection.ops.quote_name(Author._meta.get_field("bio").column)
1075 self.assertIn(f"{bio_column} IS NULL", ctx.captured_queries[0]["sql"])
1076
1077 def test_regex_non_string(self):
1078 """
1079 A regex lookup does not fail on non-string fields
1080 """
1081 s = Season.objects.create(year=2013, gt=444)
1082 self.assertQuerySetEqual(Season.objects.filter(gt__regex=r"^444$"), [s])
1083
1084 def test_regex_non_ascii(self):
1085 """
1086 A regex lookup does not trip on non-ASCII characters.
1087 """
1088 Player.objects.create(name="\u2660")
1089 Player.objects.get(name__regex="\u2660")
1090
1091 def test_nonfield_lookups(self):
1092 """
1093 A lookup query containing non-fields raises the proper exception.
1094 """
1095 msg = (
1096 "Unsupported lookup 'blahblah' for CharField or join on the field not "
1097 "permitted."
1098 )
1099 with self.assertRaisesMessage(FieldError, msg):
1100 Article.objects.filter(headline__blahblah=99)
1101 msg = (
1102 "Unsupported lookup 'blahblah__exact' for CharField or join "
1103 "on the field not permitted."
1104 )
1105 with self.assertRaisesMessage(FieldError, msg):
1106 Article.objects.filter(headline__blahblah__exact=99)
1107 msg = (
1108 "Cannot resolve keyword 'blahblah' into field. Choices are: "
1109 "author, author_id, headline, id, pub_date, slug, tag"
1110 )
1111 with self.assertRaisesMessage(FieldError, msg):
1112 Article.objects.filter(blahblah=99)
1113
1114 def test_lookup_collision(self):
1115 """
1116 Genuine field names don't collide with built-in lookup types
1117 ('year', 'gt', 'range', 'in' etc.) (#11670).
1118 """
1119 # 'gt' is used as a code number for the year, e.g. 111=>2009.
1120 season_2009 = Season.objects.create(year=2009, gt=111)
1121 season_2009.games.create(home="Houston Astros", away="St. Louis Cardinals")
1122 season_2010 = Season.objects.create(year=2010, gt=222)
1123 season_2010.games.create(home="Houston Astros", away="Chicago Cubs")
1124 season_2010.games.create(home="Houston Astros", away="Milwaukee Brewers")
1125 season_2010.games.create(home="Houston Astros", away="St. Louis Cardinals")
1126 season_2011 = Season.objects.create(year=2011, gt=333)
1127 season_2011.games.create(home="Houston Astros", away="St. Louis Cardinals")
1128 season_2011.games.create(home="Houston Astros", away="Milwaukee Brewers")
1129 hunter_pence = Player.objects.create(name="Hunter Pence")
1130 hunter_pence.games.set(Game.objects.filter(season__year__in=[2009, 2010]))
1131 pudge = Player.objects.create(name="Ivan Rodriquez")
1132 pudge.games.set(Game.objects.filter(season__year=2009))
1133 pedro_feliz = Player.objects.create(name="Pedro Feliz")
1134 pedro_feliz.games.set(Game.objects.filter(season__year__in=[2011]))
1135 johnson = Player.objects.create(name="Johnson")
1136 johnson.games.set(Game.objects.filter(season__year__in=[2011]))
1137
1138 # Games in 2010
1139 self.assertEqual(Game.objects.filter(season__year=2010).count(), 3)
1140 self.assertEqual(Game.objects.filter(season__year__exact=2010).count(), 3)
1141 self.assertEqual(Game.objects.filter(season__gt=222).count(), 3)
1142 self.assertEqual(Game.objects.filter(season__gt__exact=222).count(), 3)
1143
1144 # Games in 2011
1145 self.assertEqual(Game.objects.filter(season__year=2011).count(), 2)
1146 self.assertEqual(Game.objects.filter(season__year__exact=2011).count(), 2)
1147 self.assertEqual(Game.objects.filter(season__gt=333).count(), 2)
1148 self.assertEqual(Game.objects.filter(season__gt__exact=333).count(), 2)
1149 self.assertEqual(Game.objects.filter(season__year__gt=2010).count(), 2)
1150 self.assertEqual(Game.objects.filter(season__gt__gt=222).count(), 2)
1151
1152 # Games played in 2010 and 2011
1153 self.assertEqual(Game.objects.filter(season__year__in=[2010, 2011]).count(), 5)
1154 self.assertEqual(Game.objects.filter(season__year__gt=2009).count(), 5)
1155 self.assertEqual(Game.objects.filter(season__gt__in=[222, 333]).count(), 5)
1156 self.assertEqual(Game.objects.filter(season__gt__gt=111).count(), 5)
1157
1158 # Players who played in 2009
1159 self.assertEqual(
1160 Player.objects.filter(games__season__year=2009).distinct().count(), 2
1161 )
1162 self.assertEqual(
1163 Player.objects.filter(games__season__year__exact=2009).distinct().count(), 2
1164 )
1165 self.assertEqual(
1166 Player.objects.filter(games__season__gt=111).distinct().count(), 2
1167 )
1168 self.assertEqual(
1169 Player.objects.filter(games__season__gt__exact=111).distinct().count(), 2
1170 )
1171
1172 # Players who played in 2010
1173 self.assertEqual(
1174 Player.objects.filter(games__season__year=2010).distinct().count(), 1
1175 )
1176 self.assertEqual(
1177 Player.objects.filter(games__season__year__exact=2010).distinct().count(), 1
1178 )
1179 self.assertEqual(
1180 Player.objects.filter(games__season__gt=222).distinct().count(), 1
1181 )
1182 self.assertEqual(
1183 Player.objects.filter(games__season__gt__exact=222).distinct().count(), 1
1184 )
1185
1186 # Players who played in 2011
1187 self.assertEqual(
1188 Player.objects.filter(games__season__year=2011).distinct().count(), 2
1189 )
1190 self.assertEqual(
1191 Player.objects.filter(games__season__year__exact=2011).distinct().count(), 2
1192 )
1193 self.assertEqual(
1194 Player.objects.filter(games__season__gt=333).distinct().count(), 2
1195 )
1196 self.assertEqual(
1197 Player.objects.filter(games__season__year__gt=2010).distinct().count(), 2
1198 )
1199 self.assertEqual(
1200 Player.objects.filter(games__season__gt__gt=222).distinct().count(), 2
1201 )
1202
1203 def test_chain_date_time_lookups(self):
1204 self.assertCountEqual(
1205 Article.objects.filter(pub_date__month__gt=7),
1206 [self.a5, self.a6],
1207 )
1208 self.assertCountEqual(
1209 Article.objects.filter(pub_date__day__gte=27),
1210 [self.a2, self.a3, self.a4, self.a7],
1211 )
1212 self.assertCountEqual(
1213 Article.objects.filter(pub_date__hour__lt=8),
1214 [self.a1, self.a2, self.a3, self.a4, self.a7],
1215 )
1216 self.assertCountEqual(
1217 Article.objects.filter(pub_date__minute__lte=0),
1218 [self.a1, self.a2, self.a3, self.a4, self.a5, self.a6, self.a7],
1219 )
1220
1221 def test_exact_none_transform(self):
1222 """Transforms are used for __exact=None."""
1223 Season.objects.create(year=1, nulled_text_field="not null")
1224 self.assertFalse(Season.objects.filter(nulled_text_field__isnull=True))
1225 self.assertTrue(Season.objects.filter(nulled_text_field__nulled__isnull=True))
1226 self.assertTrue(Season.objects.filter(nulled_text_field__nulled__exact=None))
1227 self.assertTrue(Season.objects.filter(nulled_text_field__nulled=None))
1228
1229 def test_exact_sliced_queryset_limit_one(self):
1230 self.assertCountEqual(
1231 Article.objects.filter(author=Author.objects.all()[:1]),
1232 [self.a1, self.a2, self.a3, self.a4],
1233 )
1234
1235 def test_exact_sliced_queryset_limit_one_offset(self):
1236 self.assertCountEqual(
1237 Article.objects.filter(author=Author.objects.all()[1:2]),
1238 [self.a5, self.a6, self.a7],
1239 )
1240
1241 def test_exact_sliced_queryset_not_limited_to_one(self):
1242 msg = (
1243 "The QuerySet value for an exact lookup must be limited to one "
1244 "result using slicing."
1245 )
1246 with self.assertRaisesMessage(ValueError, msg):
1247 list(Article.objects.filter(author=Author.objects.all()[:2]))
1248 with self.assertRaisesMessage(ValueError, msg):
1249 list(Article.objects.filter(author=Author.objects.all()[1:]))
1250
1251 @skipUnless(connection.vendor == "mysql", "MySQL-specific workaround.")
1252 def test_exact_booleanfield(self):
1253 # MySQL ignores indexes with boolean fields unless they're compared
1254 # directly to a boolean value.
1255 product = Product.objects.create(name="Paper", qty_target=5000)
1256 Stock.objects.create(product=product, short=False, qty_available=5100)
1257 stock_1 = Stock.objects.create(product=product, short=True, qty_available=180)
1258 qs = Stock.objects.filter(short=True)
1259 self.assertSequenceEqual(qs, [stock_1])
1260 self.assertIn(
1261 "%s = True" % connection.ops.quote_name("short"),
1262 str(qs.query),
1263 )
1264
1265 @skipUnless(connection.vendor == "mysql", "MySQL-specific workaround.")
1266 def test_exact_booleanfield_annotation(self):
1267 # MySQL ignores indexes with boolean fields unless they're compared
1268 # directly to a boolean value.
1269 qs = Author.objects.annotate(
1270 case=Case(
1271 When(alias="a1", then=True),
1272 default=False,
1273 output_field=BooleanField(),
1274 )
1275 ).filter(case=True)
1276 self.assertSequenceEqual(qs, [self.au1])
1277 self.assertIn(" = True", str(qs.query))
1278
1279 qs = Author.objects.annotate(
1280 wrapped=ExpressionWrapper(Q(alias="a1"), output_field=BooleanField()),
1281 ).filter(wrapped=True)
1282 self.assertSequenceEqual(qs, [self.au1])
1283 self.assertIn(" = True", str(qs.query))
1284 # EXISTS(...) shouldn't be compared to a boolean value.
1285 qs = Author.objects.annotate(
1286 exists=Exists(Author.objects.filter(alias="a1", pk=OuterRef("pk"))),
1287 ).filter(exists=True)
1288 self.assertSequenceEqual(qs, [self.au1])
1289 self.assertNotIn(" = True", str(qs.query))
1290
1291 def test_custom_field_none_rhs(self):
1292 """
1293 __exact=value is transformed to __isnull=True if Field.get_prep_value()
1294 converts value to None.
1295 """
1296 season = Season.objects.create(year=2012, nulled_text_field=None)
1297 self.assertTrue(
1298 Season.objects.filter(pk=season.pk, nulled_text_field__isnull=True)
1299 )
1300 self.assertTrue(Season.objects.filter(pk=season.pk, nulled_text_field=""))
1301
1302 def test_pattern_lookups_with_substr(self):
1303 a = Author.objects.create(name="John Smith", alias="Johx")
1304 b = Author.objects.create(name="Rhonda Simpson", alias="sonx")
1305 tests = (
1306 ("startswith", [a]),
1307 ("istartswith", [a]),
1308 ("contains", [a, b]),
1309 ("icontains", [a, b]),
1310 ("endswith", [b]),
1311 ("iendswith", [b]),
1312 )
1313 for lookup, result in tests:
1314 with self.subTest(lookup=lookup):
1315 authors = Author.objects.filter(
1316 **{"name__%s" % lookup: Substr("alias", 1, 3)}
1317 )
1318 self.assertCountEqual(authors, result)
1319
1320 def test_custom_lookup_none_rhs(self):
1321 """Lookup.can_use_none_as_rhs=True allows None as a lookup value."""
1322 season = Season.objects.create(year=2012, nulled_text_field=None)
1323 query = Season.objects.get_queryset().query
1324 field = query.model._meta.get_field("nulled_text_field")
1325 self.assertIsInstance(
1326 query.build_lookup(["isnull_none_rhs"], field, None), IsNullWithNoneAsRHS
1327 )
1328 self.assertTrue(
1329 Season.objects.filter(pk=season.pk, nulled_text_field__isnull_none_rhs=True)
1330 )
1331
1332 def test_exact_exists(self):
1333 qs = Article.objects.filter(pk=OuterRef("pk"))
1334 seasons = Season.objects.annotate(pk_exists=Exists(qs)).filter(
1335 pk_exists=Exists(qs),
1336 )
1337 self.assertCountEqual(seasons, Season.objects.all())
1338
1339 def test_nested_outerref_lhs(self):
1340 tag = Tag.objects.create(name=self.au1.alias)
1341 tag.articles.add(self.a1)
1342 qs = Tag.objects.annotate(
1343 has_author_alias_match=Exists(
1344 Article.objects.annotate(
1345 author_exists=Exists(
1346 Author.objects.filter(alias=OuterRef(OuterRef("name")))
1347 ),
1348 ).filter(author_exists=True)
1349 ),
1350 )
1351 self.assertEqual(qs.get(has_author_alias_match=True), tag)
1352
1353 def test_exact_query_rhs_with_selected_columns(self):
1354 newest_author = Author.objects.create(name="Author 2")
1355 authors_max_ids = (
1356 Author.objects.filter(
1357 name="Author 2",
1358 )
1359 .values(
1360 "name",
1361 )
1362 .annotate(
1363 max_id=Max("id"),
1364 )
1365 .values("max_id")
1366 )
1367 authors = Author.objects.filter(id=authors_max_ids[:1])
1368 self.assertEqual(authors.get(), newest_author)
1369
1370 def test_exact_query_rhs_with_selected_columns_mismatch(self):
1371 msg = (
1372 "The QuerySet value for the exact lookup must have 1 "
1373 "selected fields (received 2)"
1374 )
1375 with self.assertRaisesMessage(ValueError, msg):
1376 Author.objects.filter(id=Author.objects.values("id", "name")[:1])
1377
1378 def test_isnull_non_boolean_value(self):
1379 msg = "The QuerySet value for an isnull lookup must be True or False."
1380 tests = [
1381 Author.objects.filter(alias__isnull=1),
1382 Article.objects.filter(author__isnull=1),
1383 Season.objects.filter(games__isnull=1),
1384 Freebie.objects.filter(stock__isnull=1),
1385 ]
1386 for qs in tests:
1387 with self.subTest(qs=qs):
1388 with self.assertRaisesMessage(ValueError, msg):
1389 qs.exists()
1390
1391 def test_isnull_textfield(self):
1392 self.assertSequenceEqual(
1393 Author.objects.filter(bio__isnull=True),
1394 [self.au2],
1395 )
1396 self.assertSequenceEqual(
1397 Author.objects.filter(bio__isnull=False),
1398 [self.au1],
1399 )
1400
1401 def test_lookup_rhs(self):
1402 product = Product.objects.create(name="GME", qty_target=5000)
1403 stock_1 = Stock.objects.create(product=product, short=True, qty_available=180)
1404 stock_2 = Stock.objects.create(product=product, short=False, qty_available=5100)
1405 Stock.objects.create(product=product, short=False, qty_available=4000)
1406 self.assertCountEqual(
1407 Stock.objects.filter(short=Q(qty_available__lt=F("product__qty_target"))),
1408 [stock_1, stock_2],
1409 )
1410 self.assertCountEqual(
1411 Stock.objects.filter(
1412 short=ExpressionWrapper(
1413 Q(qty_available__lt=F("product__qty_target")),
1414 output_field=BooleanField(),
1415 )
1416 ),
1417 [stock_1, stock_2],
1418 )
1419
1420 def test_lookup_direct_value_rhs_unwrapped(self):
1421 with self.assertNumQueries(1) as ctx:
1422 self.assertIs(Author.objects.filter(GreaterThan(2, 1)).exists(), True)
1423 # Direct values on RHS are not wrapped.
1424 self.assertIn("2 > 1", ctx.captured_queries[0]["sql"])
1425
1426
1427 class LookupQueryingTests(TestCase):
1428 @classmethod
1429 def setUpTestData(cls):
1430 cls.s1 = Season.objects.create(year=1942, gt=1942)
1431 cls.s2 = Season.objects.create(year=1842, gt=1942, nulled_text_field="text")
1432 cls.s3 = Season.objects.create(year=2042, gt=1942)
1433 Game.objects.create(season=cls.s1, home="NY", away="Boston")
1434 Game.objects.create(season=cls.s1, home="NY", away="Tampa")
1435 Game.objects.create(season=cls.s3, home="Boston", away="Tampa")
1436
1437 def test_annotate(self):
1438 qs = Season.objects.annotate(equal=Exact(F("year"), 1942))
1439 self.assertCountEqual(
1440 qs.values_list("year", "equal"),
1441 ((1942, True), (1842, False), (2042, False)),
1442 )
1443
1444 def test_alias(self):
1445 qs = Season.objects.alias(greater=GreaterThan(F("year"), 1910))
1446 self.assertCountEqual(qs.filter(greater=True), [self.s1, self.s3])
1447
1448 def test_annotate_value_greater_than_value(self):
1449 qs = Season.objects.annotate(greater=GreaterThan(Value(40), Value(30)))
1450 self.assertCountEqual(
1451 qs.values_list("year", "greater"),
1452 ((1942, True), (1842, True), (2042, True)),
1453 )
1454
1455 def test_annotate_field_greater_than_field(self):
1456 qs = Season.objects.annotate(greater=GreaterThan(F("year"), F("gt")))
1457 self.assertCountEqual(
1458 qs.values_list("year", "greater"),
1459 ((1942, False), (1842, False), (2042, True)),
1460 )
1461
1462 def test_annotate_field_greater_than_value(self):
1463 qs = Season.objects.annotate(greater=GreaterThan(F("year"), Value(1930)))
1464 self.assertCountEqual(
1465 qs.values_list("year", "greater"),
1466 ((1942, True), (1842, False), (2042, True)),
1467 )
1468
1469 def test_annotate_field_greater_than_literal(self):
1470 qs = Season.objects.annotate(greater=GreaterThan(F("year"), 1930))
1471 self.assertCountEqual(
1472 qs.values_list("year", "greater"),
1473 ((1942, True), (1842, False), (2042, True)),
1474 )
1475
1476 def test_annotate_literal_greater_than_field(self):
1477 qs = Season.objects.annotate(greater=GreaterThan(1930, F("year")))
1478 self.assertCountEqual(
1479 qs.values_list("year", "greater"),
1480 ((1942, False), (1842, True), (2042, False)),
1481 )
1482
1483 def test_annotate_less_than_float(self):
1484 qs = Season.objects.annotate(lesser=LessThan(F("year"), 1942.1))
1485 self.assertCountEqual(
1486 qs.values_list("year", "lesser"),
1487 ((1942, True), (1842, True), (2042, False)),
1488 )
1489
1490 def test_annotate_greater_than_or_equal(self):
1491 qs = Season.objects.annotate(greater=GreaterThanOrEqual(F("year"), 1942))
1492 self.assertCountEqual(
1493 qs.values_list("year", "greater"),
1494 ((1942, True), (1842, False), (2042, True)),
1495 )
1496
1497 def test_annotate_greater_than_or_equal_float(self):
1498 qs = Season.objects.annotate(greater=GreaterThanOrEqual(F("year"), 1942.1))
1499 self.assertCountEqual(
1500 qs.values_list("year", "greater"),
1501 ((1942, False), (1842, False), (2042, True)),
1502 )
1503
1504 def test_combined_lookups(self):
1505 expression = Exact(F("year"), 1942) | GreaterThan(F("year"), 1942)
1506 qs = Season.objects.annotate(gte=expression)
1507 self.assertCountEqual(
1508 qs.values_list("year", "gte"),
1509 ((1942, True), (1842, False), (2042, True)),
1510 )
1511
1512 def test_lookup_in_filter(self):
1513 qs = Season.objects.filter(GreaterThan(F("year"), 1910))
1514 self.assertCountEqual(qs, [self.s1, self.s3])
1515
1516 def test_isnull_lookup_in_filter(self):
1517 self.assertSequenceEqual(
1518 Season.objects.filter(IsNull(F("nulled_text_field"), False)),
1519 [self.s2],
1520 )
1521 self.assertCountEqual(
1522 Season.objects.filter(IsNull(F("nulled_text_field"), True)),
1523 [self.s1, self.s3],
1524 )
1525
1526 def test_in_lookup_in_filter(self):
1527 test_cases = [
1528 ((), ()),
1529 ((1942,), (self.s1,)),
1530 ((1842,), (self.s2,)),
1531 ((2042,), (self.s3,)),
1532 ((1942, 1842), (self.s1, self.s2)),
1533 ((1942, 2042), (self.s1, self.s3)),
1534 ((1842, 2042), (self.s2, self.s3)),
1535 ((1942, 1942, 1942), (self.s1,)),
1536 ((1942, 2042, 1842), (self.s1, self.s2, self.s3)),
1537 ]
1538
1539 for years, seasons in test_cases:
1540 with self.subTest(years=years, seasons=seasons):
1541 self.assertSequenceEqual(
1542 Season.objects.filter(In(F("year"), years)).order_by("pk"), seasons
1543 )
1544
1545 def test_filter_lookup_lhs(self):
1546 qs = Season.objects.annotate(before_20=LessThan(F("year"), 2000)).filter(
1547 before_20=LessThan(F("year"), 1900),
1548 )
1549 self.assertCountEqual(qs, [self.s2, self.s3])
1550
1551 def test_filter_wrapped_lookup_lhs(self):
1552 qs = (
1553 Season.objects.annotate(
1554 before_20=ExpressionWrapper(
1555 Q(year__lt=2000),
1556 output_field=BooleanField(),
1557 )
1558 )
1559 .filter(before_20=LessThan(F("year"), 1900))
1560 .values_list("year", flat=True)
1561 )
1562 self.assertCountEqual(qs, [1842, 2042])
1563
1564 def test_filter_exists_lhs(self):
1565 qs = Season.objects.annotate(
1566 before_20=Exists(
1567 Season.objects.filter(pk=OuterRef("pk"), year__lt=2000),
1568 )
1569 ).filter(before_20=LessThan(F("year"), 1900))
1570 self.assertCountEqual(qs, [self.s2, self.s3])
1571
1572 def test_filter_subquery_lhs(self):
1573 qs = Season.objects.annotate(
1574 before_20=Subquery(
1575 Season.objects.filter(pk=OuterRef("pk")).values(
1576 lesser=LessThan(F("year"), 2000),
1577 ),
1578 )
1579 ).filter(before_20=LessThan(F("year"), 1900))
1580 self.assertCountEqual(qs, [self.s2, self.s3])
1581
1582 def test_combined_lookups_in_filter(self):
1583 expression = Exact(F("year"), 1942) | GreaterThan(F("year"), 1942)
1584 qs = Season.objects.filter(expression)
1585 self.assertCountEqual(qs, [self.s1, self.s3])
1586
1587 def test_combined_annotated_lookups_in_filter(self):
1588 expression = Exact(F("year"), 1942) | GreaterThan(F("year"), 1942)
1589 qs = Season.objects.annotate(gte=expression).filter(gte=True)
1590 self.assertCountEqual(qs, [self.s1, self.s3])
1591
1592 def test_combined_annotated_lookups_in_filter_false(self):
1593 expression = Exact(F("year"), 1942) | GreaterThan(F("year"), 1942)
1594 qs = Season.objects.annotate(gte=expression).filter(gte=False)
1595 self.assertSequenceEqual(qs, [self.s2])
1596
1597 def test_lookup_in_order_by(self):
1598 qs = Season.objects.order_by(LessThan(F("year"), 1910), F("year"))
1599 self.assertSequenceEqual(qs, [self.s1, self.s3, self.s2])
1600
1601 def test_aggregate_combined_lookup(self):
1602 expression = Cast(GreaterThan(F("year"), 1900), models.IntegerField())
1603 qs = Season.objects.aggregate(modern=models.Sum(expression))
1604 self.assertEqual(qs["modern"], 2)
1605
1606 def test_conditional_expression(self):
1607 qs = Season.objects.annotate(
1608 century=Case(
1609 When(
1610 GreaterThan(F("year"), 1900) & LessThanOrEqual(F("year"), 2000),
1611 then=Value("20th"),
1612 ),
1613 default=Value("other"),
1614 )
1615 ).values("year", "century")
1616 self.assertCountEqual(
1617 qs,
1618 [
1619 {"year": 1942, "century": "20th"},
1620 {"year": 1842, "century": "other"},
1621 {"year": 2042, "century": "other"},
1622 ],
1623 )
1624
1625 def test_multivalued_join_reuse(self):
1626 self.assertEqual(
1627 Season.objects.get(Exact(F("games__home"), "NY"), games__away="Boston"),
1628 self.s1,
1629 )
1630 self.assertEqual(
1631 Season.objects.get(Exact(F("games__home"), "NY") & Q(games__away="Boston")),
1632 self.s1,
1633 )
1634 self.assertEqual(
1635 Season.objects.get(
1636 Exact(F("games__home"), "NY") & Exact(F("games__away"), "Boston")
1637 ),
1638 self.s1,
1639 )