Debian Codegraph @

tests/ordering/tests.py

raw
1 from datetime import datetime
2 from operator import attrgetter
3
4 from django.core.exceptions import FieldError
5 from django.db.models import (
6 CharField,
7 Count,
8 DateTimeField,
9 F,
10 FilteredRelation,
11 Max,
12 OrderBy,
13 OuterRef,
14 Subquery,
15 Value,
16 )
17 from django.db.models.functions import Length, Upper
18 from django.test import TestCase
19
20 from .models import (
21 Article,
22 Author,
23 ChildArticle,
24 OrderedByExpression,
25 OrderedByExpressionChild,
26 OrderedByExpressionGrandChild,
27 OrderedByFArticle,
28 Reference,
29 )
30
31
32 class OrderingTests(TestCase):
33 @classmethod
34 def setUpTestData(cls):
35 cls.a1 = Article.objects.create(
36 headline="Article 1", pub_date=datetime(2005, 7, 26)
37 )
38 cls.a2 = Article.objects.create(
39 headline="Article 2", pub_date=datetime(2005, 7, 27)
40 )
41 cls.a3 = Article.objects.create(
42 headline="Article 3", pub_date=datetime(2005, 7, 27)
43 )
44 cls.a4 = Article.objects.create(
45 headline="Article 4", pub_date=datetime(2005, 7, 28)
46 )
47 cls.author_1 = Author.objects.create(name="Name 1")
48 cls.author_2 = Author.objects.create(name="Name 2")
49 for i in range(2):
50 Author.objects.create()
51
52 def test_default_ordering(self):
53 """
54 By default, Article.objects.all() orders by pub_date descending, then
55 headline ascending.
56 """
57 self.assertQuerySetEqual(
58 Article.objects.all(),
59 [
60 "Article 4",
61 "Article 2",
62 "Article 3",
63 "Article 1",
64 ],
65 attrgetter("headline"),
66 )
67
68 # Getting a single item should work too:
69 self.assertEqual(Article.objects.all()[0], self.a4)
70
71 def test_default_ordering_override(self):
72 """
73 Override ordering with order_by, which is in the same format as the
74 ordering attribute in models.
75 """
76 self.assertQuerySetEqual(
77 Article.objects.order_by("headline"),
78 [
79 "Article 1",
80 "Article 2",
81 "Article 3",
82 "Article 4",
83 ],
84 attrgetter("headline"),
85 )
86 self.assertQuerySetEqual(
87 Article.objects.order_by("pub_date", "-headline"),
88 [
89 "Article 1",
90 "Article 3",
91 "Article 2",
92 "Article 4",
93 ],
94 attrgetter("headline"),
95 )
96
97 def test_default_ordering_override_unknown_field(self):
98 """
99 Attempts to override default ordering on related models with an unknown
100 field should result in an error.
101 """
102 msg = (
103 "Cannot resolve keyword 'unknown_field' into field. Choices are: "
104 "article, author, editor, editor_id, id, name"
105 )
106 with self.assertRaisesMessage(FieldError, msg):
107 list(Article.objects.order_by("author__unknown_field"))
108
109 def test_order_by_override(self):
110 """
111 Only the last order_by has any effect (since they each override any
112 previous ordering).
113 """
114 self.assertQuerySetEqual(
115 Article.objects.order_by("id"),
116 [
117 "Article 1",
118 "Article 2",
119 "Article 3",
120 "Article 4",
121 ],
122 attrgetter("headline"),
123 )
124 self.assertQuerySetEqual(
125 Article.objects.order_by("id").order_by("-headline"),
126 [
127 "Article 4",
128 "Article 3",
129 "Article 2",
130 "Article 1",
131 ],
132 attrgetter("headline"),
133 )
134
135 def test_order_by_nulls_first_and_last(self):
136 msg = "nulls_first and nulls_last are mutually exclusive"
137 with self.assertRaisesMessage(ValueError, msg):
138 Article.objects.order_by(
139 F("author").desc(nulls_last=True, nulls_first=True)
140 )
141
142 def assertQuerySetEqualReversible(self, queryset, sequence):
143 self.assertSequenceEqual(queryset, sequence)
144 self.assertSequenceEqual(queryset.reverse(), list(reversed(sequence)))
145
146 def test_order_by_nulls_last(self):
147 Article.objects.filter(headline="Article 3").update(author=self.author_1)
148 Article.objects.filter(headline="Article 4").update(author=self.author_2)
149 # asc and desc are chainable with nulls_last.
150 self.assertQuerySetEqualReversible(
151 Article.objects.order_by(F("author").desc(nulls_last=True), "headline"),
152 [self.a4, self.a3, self.a1, self.a2],
153 )
154 self.assertQuerySetEqualReversible(
155 Article.objects.order_by(F("author").asc(nulls_last=True), "headline"),
156 [self.a3, self.a4, self.a1, self.a2],
157 )
158 self.assertQuerySetEqualReversible(
159 Article.objects.order_by(
160 Upper("author__name").desc(nulls_last=True), "headline"
161 ),
162 [self.a4, self.a3, self.a1, self.a2],
163 )
164 self.assertQuerySetEqualReversible(
165 Article.objects.order_by(
166 Upper("author__name").asc(nulls_last=True), "headline"
167 ),
168 [self.a3, self.a4, self.a1, self.a2],
169 )
170 self.assertQuerySetEqualReversible(
171 Article.objects.annotate(upper_name=Upper("author__name")).order_by(
172 F("upper_name").asc(nulls_last=True), "headline"
173 ),
174 [self.a3, self.a4, self.a1, self.a2],
175 )
176
177 def test_order_by_nulls_first(self):
178 Article.objects.filter(headline="Article 3").update(author=self.author_1)
179 Article.objects.filter(headline="Article 4").update(author=self.author_2)
180 # asc and desc are chainable with nulls_first.
181 self.assertQuerySetEqualReversible(
182 Article.objects.order_by(F("author").asc(nulls_first=True), "headline"),
183 [self.a1, self.a2, self.a3, self.a4],
184 )
185 self.assertQuerySetEqualReversible(
186 Article.objects.order_by(F("author").desc(nulls_first=True), "headline"),
187 [self.a1, self.a2, self.a4, self.a3],
188 )
189 self.assertQuerySetEqualReversible(
190 Article.objects.order_by(
191 Upper("author__name").asc(nulls_first=True), "headline"
192 ),
193 [self.a1, self.a2, self.a3, self.a4],
194 )
195 self.assertQuerySetEqualReversible(
196 Article.objects.order_by(
197 Upper("author__name").desc(nulls_first=True), "headline"
198 ),
199 [self.a1, self.a2, self.a4, self.a3],
200 )
201 self.assertQuerySetEqualReversible(
202 Article.objects.annotate(upper_name=Upper("author__name")).order_by(
203 F("upper_name").desc(nulls_first=True), "headline"
204 ),
205 [self.a1, self.a2, self.a4, self.a3],
206 )
207
208 def test_orders_nulls_first_on_filtered_subquery(self):
209 Article.objects.filter(headline="Article 1").update(author=self.author_1)
210 Article.objects.filter(headline="Article 2").update(author=self.author_1)
211 Article.objects.filter(headline="Article 4").update(author=self.author_2)
212 Author.objects.filter(name__isnull=True).delete()
213 author_3 = Author.objects.create(name="Name 3")
214 article_subquery = (
215 Article.objects.filter(
216 author=OuterRef("pk"),
217 headline__icontains="Article",
218 )
219 .order_by()
220 .values("author")
221 .annotate(
222 last_date=Max("pub_date"),
223 )
224 .values("last_date")
225 )
226 self.assertQuerySetEqualReversible(
227 Author.objects.annotate(
228 last_date=Subquery(article_subquery, output_field=DateTimeField())
229 )
230 .order_by(F("last_date").asc(nulls_first=True))
231 .distinct(),
232 [author_3, self.author_1, self.author_2],
233 )
234
235 def test_stop_slicing(self):
236 """
237 Use the 'stop' part of slicing notation to limit the results.
238 """
239 self.assertQuerySetEqual(
240 Article.objects.order_by("headline")[:2],
241 [
242 "Article 1",
243 "Article 2",
244 ],
245 attrgetter("headline"),
246 )
247
248 def test_stop_start_slicing(self):
249 """
250 Use the 'stop' and 'start' parts of slicing notation to offset the
251 result list.
252 """
253 self.assertQuerySetEqual(
254 Article.objects.order_by("headline")[1:3],
255 [
256 "Article 2",
257 "Article 3",
258 ],
259 attrgetter("headline"),
260 )
261
262 def test_random_ordering(self):
263 """
264 Use '?' to order randomly.
265 """
266 self.assertEqual(len(list(Article.objects.order_by("?"))), 4)
267
268 def test_reversed_ordering(self):
269 """
270 Ordering can be reversed using the reverse() method on a queryset.
271 This allows you to extract things like "the last two items" (reverse
272 and then take the first two).
273 """
274 self.assertQuerySetEqual(
275 Article.objects.reverse()[:2],
276 [
277 "Article 1",
278 "Article 3",
279 ],
280 attrgetter("headline"),
281 )
282
283 def test_reverse_ordering_pure(self):
284 qs1 = Article.objects.order_by(F("headline").asc())
285 qs2 = qs1.reverse()
286 self.assertQuerySetEqual(
287 qs2,
288 [
289 "Article 4",
290 "Article 3",
291 "Article 2",
292 "Article 1",
293 ],
294 attrgetter("headline"),
295 )
296 self.assertQuerySetEqual(
297 qs1,
298 [
299 "Article 1",
300 "Article 2",
301 "Article 3",
302 "Article 4",
303 ],
304 attrgetter("headline"),
305 )
306
307 def test_reverse_meta_ordering_pure(self):
308 Article.objects.create(
309 headline="Article 5",
310 pub_date=datetime(2005, 7, 30),
311 author=self.author_1,
312 second_author=self.author_2,
313 )
314 Article.objects.create(
315 headline="Article 5",
316 pub_date=datetime(2005, 7, 30),
317 author=self.author_2,
318 second_author=self.author_1,
319 )
320 self.assertQuerySetEqual(
321 Article.objects.filter(headline="Article 5").reverse(),
322 ["Name 2", "Name 1"],
323 attrgetter("author.name"),
324 )
325 self.assertQuerySetEqual(
326 Article.objects.filter(headline="Article 5"),
327 ["Name 1", "Name 2"],
328 attrgetter("author.name"),
329 )
330
331 def test_no_reordering_after_slicing(self):
332 msg = "Cannot reverse a query once a slice has been taken."
333 qs = Article.objects.all()[0:2]
334 with self.assertRaisesMessage(TypeError, msg):
335 qs.reverse()
336 with self.assertRaisesMessage(TypeError, msg):
337 qs.last()
338
339 def test_extra_ordering(self):
340 """
341 Ordering can be based on fields included from an 'extra' clause
342 """
343 self.assertQuerySetEqual(
344 Article.objects.extra(
345 select={"foo": "pub_date"}, order_by=["foo", "headline"]
346 ),
347 [
348 "Article 1",
349 "Article 2",
350 "Article 3",
351 "Article 4",
352 ],
353 attrgetter("headline"),
354 )
355
356 def test_extra_ordering_quoting(self):
357 """
358 If the extra clause uses an SQL keyword for a name, it will be
359 protected by quoting.
360 """
361 self.assertQuerySetEqual(
362 Article.objects.extra(
363 select={"order": "pub_date"}, order_by=["order", "headline"]
364 ),
365 [
366 "Article 1",
367 "Article 2",
368 "Article 3",
369 "Article 4",
370 ],
371 attrgetter("headline"),
372 )
373
374 def test_extra_ordering_with_table_name(self):
375 self.assertQuerySetEqual(
376 Article.objects.extra(order_by=["ordering_article.headline"]),
377 [
378 "Article 1",
379 "Article 2",
380 "Article 3",
381 "Article 4",
382 ],
383 attrgetter("headline"),
384 )
385 self.assertQuerySetEqual(
386 Article.objects.extra(order_by=["-ordering_article.headline"]),
387 [
388 "Article 4",
389 "Article 3",
390 "Article 2",
391 "Article 1",
392 ],
393 attrgetter("headline"),
394 )
395
396 def test_alias_with_period_shadows_table_name(self):
397 """
398 Aliases with periods are not confused for table names from extra().
399 """
400 Article.objects.update(author=self.author_2)
401 Article.objects.create(
402 headline="Backdated", pub_date=datetime(1900, 1, 1), author=self.author_1
403 )
404 crafted = "ordering_article.pub_date"
405
406 qs = Article.objects.annotate(**{crafted: F("author")}).order_by("-" + crafted)
407 self.assertNotEqual(qs[0].headline, "Backdated")
408
409 relation = FilteredRelation("author")
410 msg = (
411 "FilteredRelation doesn't support aliases with periods "
412 "(got 'ordering_article.pub_date')."
413 )
414 with self.assertRaisesMessage(ValueError, msg):
415 qs2 = Article.objects.annotate(**{crafted: relation}).order_by(crafted)
416 # Before, unlike F(), which causes ordering expressions to be
417 # replaced by ordinals like n in ORDER BY n, these were ordered by
418 # pub_date instead of author.
419 # The Article model orders by -pk, so sorting on author will place
420 # first any article by author2 instead of the backdated one.
421 # This assertion is reachable if FilteredRelation.__init__() starts
422 # supporting periods in aliases in the future.
423 self.assertNotEqual(qs2[0].headline, "Backdated")
424
425 def test_order_by_pk(self):
426 """
427 'pk' works as an ordering option in Meta.
428 """
429 self.assertEqual(
430 [a.pk for a in Author.objects.all()],
431 [a.pk for a in Author.objects.order_by("-pk")],
432 )
433
434 def test_order_by_fk_attname(self):
435 """
436 ordering by a foreign key by its attribute name prevents the query
437 from inheriting its related model ordering option (#19195).
438 """
439 authors = list(Author.objects.order_by("id"))
440 for i in range(1, 5):
441 author = authors[i - 1]
442 article = getattr(self, "a%d" % (5 - i))
443 article.author = author
444 article.save(update_fields={"author"})
445
446 self.assertQuerySetEqual(
447 Article.objects.order_by("author_id"),
448 [
449 "Article 4",
450 "Article 3",
451 "Article 2",
452 "Article 1",
453 ],
454 attrgetter("headline"),
455 )
456
457 def test_order_by_self_referential_fk(self):
458 self.a1.author = Author.objects.create(editor=self.author_1)
459 self.a1.save()
460 self.a2.author = Author.objects.create(editor=self.author_2)
461 self.a2.save()
462 self.assertQuerySetEqual(
463 Article.objects.filter(author__isnull=False).order_by("author__editor"),
464 ["Article 2", "Article 1"],
465 attrgetter("headline"),
466 )
467 self.assertQuerySetEqual(
468 Article.objects.filter(author__isnull=False).order_by("author__editor_id"),
469 ["Article 1", "Article 2"],
470 attrgetter("headline"),
471 )
472
473 def test_order_by_f_expression(self):
474 self.assertQuerySetEqual(
475 Article.objects.order_by(F("headline")),
476 [
477 "Article 1",
478 "Article 2",
479 "Article 3",
480 "Article 4",
481 ],
482 attrgetter("headline"),
483 )
484 self.assertQuerySetEqual(
485 Article.objects.order_by(F("headline").asc()),
486 [
487 "Article 1",
488 "Article 2",
489 "Article 3",
490 "Article 4",
491 ],
492 attrgetter("headline"),
493 )
494 self.assertQuerySetEqual(
495 Article.objects.order_by(F("headline").desc()),
496 [
497 "Article 4",
498 "Article 3",
499 "Article 2",
500 "Article 1",
501 ],
502 attrgetter("headline"),
503 )
504
505 def test_order_by_f_expression_duplicates(self):
506 """
507 A column may only be included once (the first occurrence) so we check
508 to ensure there are no duplicates by inspecting the SQL.
509 """
510 qs = Article.objects.order_by(F("headline").asc(), F("headline").desc())
511 sql = str(qs.query).upper()
512 fragment = sql[sql.find("ORDER BY") :]
513 self.assertEqual(fragment.count("HEADLINE"), 1)
514 self.assertQuerySetEqual(
515 qs,
516 [
517 "Article 1",
518 "Article 2",
519 "Article 3",
520 "Article 4",
521 ],
522 attrgetter("headline"),
523 )
524 qs = Article.objects.order_by(F("headline").desc(), F("headline").asc())
525 sql = str(qs.query).upper()
526 fragment = sql[sql.find("ORDER BY") :]
527 self.assertEqual(fragment.count("HEADLINE"), 1)
528 self.assertQuerySetEqual(
529 qs,
530 [
531 "Article 4",
532 "Article 3",
533 "Article 2",
534 "Article 1",
535 ],
536 attrgetter("headline"),
537 )
538
539 def test_order_by_constant_value(self):
540 # Order by annotated constant from selected columns.
541 qs = Article.objects.annotate(
542 constant=Value("1", output_field=CharField()),
543 ).order_by("constant", "-headline")
544 self.assertSequenceEqual(qs, [self.a4, self.a3, self.a2, self.a1])
545 # Order by annotated constant which is out of selected columns.
546 self.assertSequenceEqual(
547 qs.values_list("headline", flat=True),
548 [
549 "Article 4",
550 "Article 3",
551 "Article 2",
552 "Article 1",
553 ],
554 )
555 # Order by constant.
556 qs = Article.objects.order_by(Value("1", output_field=CharField()), "-headline")
557 self.assertSequenceEqual(qs, [self.a4, self.a3, self.a2, self.a1])
558
559 def test_related_ordering_duplicate_table_reference(self):
560 """
561 An ordering referencing a model with an ordering referencing a model
562 multiple time no circular reference should be detected (#24654).
563 """
564 first_author = Author.objects.create()
565 second_author = Author.objects.create()
566 self.a1.author = first_author
567 self.a1.second_author = second_author
568 self.a1.save()
569 self.a2.author = second_author
570 self.a2.second_author = first_author
571 self.a2.save()
572 r1 = Reference.objects.create(article_id=self.a1.pk)
573 r2 = Reference.objects.create(article_id=self.a2.pk)
574 self.assertSequenceEqual(Reference.objects.all(), [r2, r1])
575
576 def test_default_ordering_by_f_expression(self):
577 """F expressions can be used in Meta.ordering."""
578 articles = OrderedByFArticle.objects.all()
579 articles.filter(headline="Article 2").update(author=self.author_2)
580 articles.filter(headline="Article 3").update(author=self.author_1)
581 self.assertQuerySetEqual(
582 articles,
583 ["Article 1", "Article 4", "Article 3", "Article 2"],
584 attrgetter("headline"),
585 )
586
587 def test_order_by_ptr_field_with_default_ordering_by_expression(self):
588 ca1 = ChildArticle.objects.create(
589 headline="h2",
590 pub_date=datetime(2005, 7, 27),
591 author=self.author_2,
592 )
593 ca2 = ChildArticle.objects.create(
594 headline="h2",
595 pub_date=datetime(2005, 7, 27),
596 author=self.author_1,
597 )
598 ca3 = ChildArticle.objects.create(
599 headline="h3",
600 pub_date=datetime(2005, 7, 27),
601 author=self.author_1,
602 )
603 ca4 = ChildArticle.objects.create(headline="h1", pub_date=datetime(2005, 7, 28))
604 articles = ChildArticle.objects.order_by("article_ptr")
605 self.assertSequenceEqual(articles, [ca4, ca2, ca1, ca3])
606
607 def test_default_ordering_does_not_affect_group_by(self):
608 Article.objects.exclude(headline="Article 4").update(author=self.author_1)
609 Article.objects.filter(headline="Article 4").update(author=self.author_2)
610 articles = Article.objects.values("author").annotate(count=Count("author"))
611 self.assertCountEqual(
612 articles,
613 [
614 {"author": self.author_1.pk, "count": 3},
615 {"author": self.author_2.pk, "count": 1},
616 ],
617 )
618
619 def test_order_by_parent_fk_with_expression_in_default_ordering(self):
620 p3 = OrderedByExpression.objects.create(name="oBJ 3")
621 p2 = OrderedByExpression.objects.create(name="OBJ 2")
622 p1 = OrderedByExpression.objects.create(name="obj 1")
623 c3 = OrderedByExpressionChild.objects.create(parent=p3)
624 c2 = OrderedByExpressionChild.objects.create(parent=p2)
625 c1 = OrderedByExpressionChild.objects.create(parent=p1)
626 self.assertSequenceEqual(
627 OrderedByExpressionChild.objects.order_by("parent"),
628 [c1, c2, c3],
629 )
630
631 def test_order_by_grandparent_fk_with_expression_in_default_ordering(self):
632 p3 = OrderedByExpression.objects.create(name="oBJ 3")
633 p2 = OrderedByExpression.objects.create(name="OBJ 2")
634 p1 = OrderedByExpression.objects.create(name="obj 1")
635 c3 = OrderedByExpressionChild.objects.create(parent=p3)
636 c2 = OrderedByExpressionChild.objects.create(parent=p2)
637 c1 = OrderedByExpressionChild.objects.create(parent=p1)
638 g3 = OrderedByExpressionGrandChild.objects.create(parent=c3)
639 g2 = OrderedByExpressionGrandChild.objects.create(parent=c2)
640 g1 = OrderedByExpressionGrandChild.objects.create(parent=c1)
641 self.assertSequenceEqual(
642 OrderedByExpressionGrandChild.objects.order_by("parent"),
643 [g1, g2, g3],
644 )
645
646 def test_order_by_expression_ref(self):
647 self.assertQuerySetEqual(
648 Author.objects.annotate(upper_name=Upper("name")).order_by(
649 Length("upper_name")
650 ),
651 Author.objects.order_by(Length(Upper("name"))),
652 )
653
654 def test_ordering_select_related_collision(self):
655 self.assertEqual(
656 Article.objects.select_related("author")
657 .annotate(name=Upper("author__name"))
658 .filter(pk=self.a1.pk)
659 .order_by(OrderBy(F("name")))
660 .first(),
661 self.a1,
662 )
663 self.assertEqual(
664 Article.objects.select_related("author")
665 .annotate(name=Upper("author__name"))
666 .filter(pk=self.a1.pk)
667 .order_by("name")
668 .first(),
669 self.a1,
670 )
671
672 def test_order_by_expr_query_reuse(self):
673 qs = Author.objects.annotate(num=Count("article")).order_by(
674 F("num").desc(), "pk"
675 )
676 self.assertCountEqual(qs, qs.iterator())