Debian Codegraph @

tests/composite_pk/test_filter.py

raw
1 from unittest.mock import patch
2
3 from django.db import connection
4 from django.db.models import (
5 Case,
6 F,
7 FilteredRelation,
8 OuterRef,
9 Q,
10 Subquery,
11 TextField,
12 Value,
13 When,
14 )
15 from django.db.models.functions import Cast
16 from django.db.models.lookups import Exact
17 from django.test import TestCase, skipUnlessDBFeature
18
19 from .models import Comment, Tenant, User
20
21
22 class CompositePKFilterTests(TestCase):
23 maxDiff = None
24
25 @classmethod
26 def setUpTestData(cls):
27 cls.tenant_1 = Tenant.objects.create()
28 cls.tenant_2 = Tenant.objects.create()
29 cls.tenant_3 = Tenant.objects.create()
30 cls.user_1 = User.objects.create(
31 tenant=cls.tenant_1,
32 id=1,
33 email="[email protected]",
34 )
35 cls.user_2 = User.objects.create(
36 tenant=cls.tenant_1,
37 id=2,
38 email="[email protected]",
39 )
40 cls.user_3 = User.objects.create(
41 tenant=cls.tenant_2,
42 id=3,
43 email="[email protected]",
44 )
45 cls.user_4 = User.objects.create(
46 tenant=cls.tenant_3,
47 id=4,
48 email="[email protected]",
49 )
50 cls.comment_1 = Comment.objects.create(id=1, user=cls.user_1)
51 cls.comment_2 = Comment.objects.create(id=2, user=cls.user_1)
52 cls.comment_3 = Comment.objects.create(id=3, user=cls.user_2)
53 cls.comment_4 = Comment.objects.create(id=4, user=cls.user_3)
54 cls.comment_5 = Comment.objects.create(id=5, user=cls.user_1)
55
56 def test_filter_and_count_user_by_pk(self):
57 test_cases = (
58 ({"pk": self.user_1.pk}, 1),
59 ({"pk": self.user_2.pk}, 1),
60 ({"pk": self.user_3.pk}, 1),
61 ({"pk": (self.tenant_1.id, self.user_1.id)}, 1),
62 ({"pk": (self.tenant_1.id, self.user_2.id)}, 1),
63 ({"pk": (self.tenant_2.id, self.user_3.id)}, 1),
64 ({"pk": (self.tenant_1.id, self.user_3.id)}, 0),
65 ({"pk": (self.tenant_2.id, self.user_1.id)}, 0),
66 ({"pk": (self.tenant_2.id, self.user_2.id)}, 0),
67 )
68
69 for lookup, count in test_cases:
70 with self.subTest(lookup=lookup, count=count):
71 self.assertEqual(User.objects.filter(**lookup).count(), count)
72
73 def test_rhs_pk(self):
74 msg = "CompositePrimaryKey cannot be used as a lookup value."
75 with self.assertRaisesMessage(ValueError, msg):
76 Comment.objects.filter(text__gt=F("pk")).count()
77
78 def test_rhs_combinable(self):
79 msg = "CombinedExpression expression does not support composite primary keys."
80 for expr in [F("pk") + (1, 1), (1, 1) + F("pk")]:
81 with (
82 self.subTest(expression=expr),
83 self.assertRaisesMessage(ValueError, msg),
84 ):
85 Comment.objects.filter(text__gt=expr).count()
86
87 def test_filter_comments_by_pk_gt(self):
88 c11, c12, c13, c24, c15 = (
89 self.comment_1,
90 self.comment_2,
91 self.comment_3,
92 self.comment_4,
93 self.comment_5,
94 )
95 test_cases = (
96 (c11, (c12, c13, c15, c24)),
97 (c12, (c13, c15, c24)),
98 (c13, (c15, c24)),
99 (c15, (c24,)),
100 (c24, ()),
101 )
102
103 for obj, objs in test_cases:
104 with self.subTest(obj=obj, objs=objs):
105 self.assertSequenceEqual(
106 Comment.objects.filter(pk__gt=obj.pk).order_by("pk"), objs
107 )
108
109 def test_filter_comments_by_pk_gte(self):
110 c11, c12, c13, c24, c15 = (
111 self.comment_1,
112 self.comment_2,
113 self.comment_3,
114 self.comment_4,
115 self.comment_5,
116 )
117 test_cases = (
118 (c11, (c11, c12, c13, c15, c24)),
119 (c12, (c12, c13, c15, c24)),
120 (c13, (c13, c15, c24)),
121 (c15, (c15, c24)),
122 (c24, (c24,)),
123 )
124
125 for obj, objs in test_cases:
126 with self.subTest(obj=obj, objs=objs):
127 self.assertSequenceEqual(
128 Comment.objects.filter(pk__gte=obj.pk).order_by("pk"), objs
129 )
130
131 def test_filter_comments_by_pk_lt(self):
132 c11, c12, c13, c24, c15 = (
133 self.comment_1,
134 self.comment_2,
135 self.comment_3,
136 self.comment_4,
137 self.comment_5,
138 )
139 test_cases = (
140 (c24, (c11, c12, c13, c15)),
141 (c15, (c11, c12, c13)),
142 (c13, (c11, c12)),
143 (c12, (c11,)),
144 (c11, ()),
145 )
146
147 for obj, objs in test_cases:
148 with self.subTest(obj=obj, objs=objs):
149 self.assertSequenceEqual(
150 Comment.objects.filter(pk__lt=obj.pk).order_by("pk"), objs
151 )
152
153 def test_filter_comments_by_pk_lte(self):
154 c11, c12, c13, c24, c15 = (
155 self.comment_1,
156 self.comment_2,
157 self.comment_3,
158 self.comment_4,
159 self.comment_5,
160 )
161 test_cases = (
162 (c24, (c11, c12, c13, c15, c24)),
163 (c15, (c11, c12, c13, c15)),
164 (c13, (c11, c12, c13)),
165 (c12, (c11, c12)),
166 (c11, (c11,)),
167 )
168
169 for obj, objs in test_cases:
170 with self.subTest(obj=obj, objs=objs):
171 self.assertSequenceEqual(
172 Comment.objects.filter(pk__lte=obj.pk).order_by("pk"), objs
173 )
174
175 def test_filter_comments_by_pk_in(self):
176 test_cases = (
177 (),
178 (self.comment_1,),
179 (self.comment_1, self.comment_4),
180 )
181
182 for objs in test_cases:
183 with self.subTest(objs=objs):
184 pks = [obj.pk for obj in objs]
185 self.assertSequenceEqual(
186 Comment.objects.filter(pk__in=pks).order_by("pk"), objs
187 )
188
189 def test_filter_comments_by_pk_in_subquery(self):
190 self.assertSequenceEqual(
191 Comment.objects.filter(
192 pk__in=Comment.objects.filter(pk=self.comment_1.pk),
193 ),
194 [self.comment_1],
195 )
196 self.assertSequenceEqual(
197 Comment.objects.filter(
198 pk__in=Comment.objects.filter(pk=self.comment_1.pk).values(
199 "tenant_id", "id"
200 ),
201 ),
202 [self.comment_1],
203 )
204 self.comment_2.integer = self.comment_1.id
205 self.comment_2.save()
206 self.assertSequenceEqual(
207 Comment.objects.filter(
208 pk__in=Comment.objects.values("tenant_id", "integer"),
209 ),
210 [self.comment_1],
211 )
212
213 def test_filter_by_pk_in_subquery_invalid_selected_columns(self):
214 msg = (
215 "The QuerySet value for the 'in' lookup must have 2 selected "
216 "fields (received 3)"
217 )
218 with self.assertRaisesMessage(ValueError, msg):
219 Comment.objects.filter(pk__in=Comment.objects.values("pk", "text"))
220
221 def test_filter_by_pk_in_none(self):
222 with self.assertNumQueries(0):
223 self.assertSequenceEqual(
224 Comment.objects.filter(pk__in=[(None, 1), (1, None)]),
225 [],
226 )
227
228 def test_filter_comments_by_user_and_order_by_pk_asc(self):
229 self.assertSequenceEqual(
230 Comment.objects.filter(user=self.user_1).order_by("pk"),
231 (self.comment_1, self.comment_2, self.comment_5),
232 )
233
234 def test_filter_comments_by_user_and_order_by_pk_desc(self):
235 self.assertSequenceEqual(
236 Comment.objects.filter(user=self.user_1).order_by("-pk"),
237 (self.comment_5, self.comment_2, self.comment_1),
238 )
239
240 def test_filter_comments_by_user_and_exclude_by_pk(self):
241 self.assertSequenceEqual(
242 Comment.objects.filter(user=self.user_1)
243 .exclude(pk=self.comment_1.pk)
244 .order_by("pk"),
245 (self.comment_2, self.comment_5),
246 )
247
248 def test_filter_comments_by_user_and_contains(self):
249 self.assertIs(
250 Comment.objects.filter(user=self.user_1).contains(self.comment_1), True
251 )
252
253 def test_filter_query_does_not_mutate(self):
254 queryset = User.objects.filter(comments__in=Comment.objects.all())
255 self.assertEqual(str(queryset.query), str(queryset.query))
256
257 def test_filter_users_by_comments_in(self):
258 c1, c2, c3, c4, c5 = (
259 self.comment_1,
260 self.comment_2,
261 self.comment_3,
262 self.comment_4,
263 self.comment_5,
264 )
265 u1, u2, u3 = (
266 self.user_1,
267 self.user_2,
268 self.user_3,
269 )
270 test_cases = (
271 ((), ()),
272 ((c1,), (u1,)),
273 ((c1, c2), (u1, u1)),
274 ((c1, c2, c3), (u1, u1, u2)),
275 ((c1, c2, c3, c4), (u1, u1, u2, u3)),
276 ((c1, c2, c3, c4, c5), (u1, u1, u1, u2, u3)),
277 )
278
279 for comments, users in test_cases:
280 with self.subTest(comments=comments, users=users):
281 self.assertSequenceEqual(
282 User.objects.filter(comments__in=comments).order_by("pk"), users
283 )
284
285 def test_filter_users_by_comments_lt(self):
286 c11, c12, c13, c24, c15 = (
287 self.comment_1,
288 self.comment_2,
289 self.comment_3,
290 self.comment_4,
291 self.comment_5,
292 )
293 u1, u2 = (
294 self.user_1,
295 self.user_2,
296 )
297 test_cases = (
298 (c11, ()),
299 (c12, (u1,)),
300 (c13, (u1, u1)),
301 (c15, (u1, u1, u2)),
302 (c24, (u1, u1, u1, u2)),
303 )
304
305 for comment, users in test_cases:
306 with self.subTest(comment=comment, users=users):
307 self.assertSequenceEqual(
308 User.objects.filter(comments__lt=comment).order_by("pk"), users
309 )
310
311 def test_filter_users_by_comments_lte(self):
312 c11, c12, c13, c24, c15 = (
313 self.comment_1,
314 self.comment_2,
315 self.comment_3,
316 self.comment_4,
317 self.comment_5,
318 )
319 u1, u2, u3 = (
320 self.user_1,
321 self.user_2,
322 self.user_3,
323 )
324 test_cases = (
325 (c11, (u1,)),
326 (c12, (u1, u1)),
327 (c13, (u1, u1, u2)),
328 (c15, (u1, u1, u1, u2)),
329 (c24, (u1, u1, u1, u2, u3)),
330 )
331
332 for comment, users in test_cases:
333 with self.subTest(comment=comment, users=users):
334 self.assertSequenceEqual(
335 User.objects.filter(comments__lte=comment).order_by("pk"), users
336 )
337
338 def test_filter_users_by_comments_gt(self):
339 c11, c12, c13, c24, c15 = (
340 self.comment_1,
341 self.comment_2,
342 self.comment_3,
343 self.comment_4,
344 self.comment_5,
345 )
346 u1, u2, u3 = (
347 self.user_1,
348 self.user_2,
349 self.user_3,
350 )
351 test_cases = (
352 (c11, (u1, u1, u2, u3)),
353 (c12, (u1, u2, u3)),
354 (c13, (u1, u3)),
355 (c15, (u3,)),
356 (c24, ()),
357 )
358
359 for comment, users in test_cases:
360 with self.subTest(comment=comment, users=users):
361 self.assertSequenceEqual(
362 User.objects.filter(comments__gt=comment).order_by("pk"), users
363 )
364
365 def test_filter_users_by_comments_gte(self):
366 c11, c12, c13, c24, c15 = (
367 self.comment_1,
368 self.comment_2,
369 self.comment_3,
370 self.comment_4,
371 self.comment_5,
372 )
373 u1, u2, u3 = (
374 self.user_1,
375 self.user_2,
376 self.user_3,
377 )
378 test_cases = (
379 (c11, (u1, u1, u1, u2, u3)),
380 (c12, (u1, u1, u2, u3)),
381 (c13, (u1, u2, u3)),
382 (c15, (u1, u3)),
383 (c24, (u3,)),
384 )
385
386 for comment, users in test_cases:
387 with self.subTest(comment=comment, users=users):
388 self.assertSequenceEqual(
389 User.objects.filter(comments__gte=comment).order_by("pk"), users
390 )
391
392 def test_filter_users_by_comments_exact(self):
393 c11, c12, c13, c24, c15 = (
394 self.comment_1,
395 self.comment_2,
396 self.comment_3,
397 self.comment_4,
398 self.comment_5,
399 )
400 u1, u2, u3 = (
401 self.user_1,
402 self.user_2,
403 self.user_3,
404 )
405 test_cases = (
406 (c11, (u1,)),
407 (c12, (u1,)),
408 (c13, (u2,)),
409 (c15, (u1,)),
410 (c24, (u3,)),
411 )
412
413 for comment, users in test_cases:
414 with self.subTest(comment=comment, users=users):
415 self.assertSequenceEqual(
416 User.objects.filter(comments=comment).order_by("pk"), users
417 )
418
419 def test_filter_users_by_comments_isnull(self):
420 u1, u2, u3, u4 = (
421 self.user_1,
422 self.user_2,
423 self.user_3,
424 self.user_4,
425 )
426
427 with self.subTest("comments__isnull=True"):
428 self.assertSequenceEqual(
429 User.objects.filter(comments__isnull=True).order_by("pk"),
430 (u4,),
431 )
432 with self.subTest("comments__isnull=False"):
433 self.assertSequenceEqual(
434 User.objects.filter(comments__isnull=False).order_by("pk"),
435 (u1, u1, u1, u2, u3),
436 )
437
438 def test_filter_comments_by_pk_isnull(self):
439 c11, c12, c13, c24, c15 = (
440 self.comment_1,
441 self.comment_2,
442 self.comment_3,
443 self.comment_4,
444 self.comment_5,
445 )
446
447 with self.subTest("pk__isnull=True"):
448 self.assertSequenceEqual(
449 Comment.objects.filter(pk__isnull=True).order_by("pk"),
450 (),
451 )
452 with self.subTest("pk__isnull=False"):
453 self.assertSequenceEqual(
454 Comment.objects.filter(pk__isnull=False).order_by("pk"),
455 (c11, c12, c13, c15, c24),
456 )
457
458 def test_filter_users_by_comments_subquery(self):
459 subquery = Comment.objects.filter(id=3).only("pk")
460 queryset = User.objects.filter(comments__in=subquery)
461 self.assertSequenceEqual(queryset, (self.user_2,))
462
463 def test_cannot_cast_pk(self):
464 msg = "Cast expression does not support composite primary keys."
465 with self.assertRaisesMessage(ValueError, msg):
466 Comment.objects.filter(text__gt=Cast(F("pk"), TextField())).count()
467
468 def test_explicit_subquery(self):
469 subquery = Subquery(User.objects.values("pk"))
470 self.assertEqual(User.objects.filter(pk__in=subquery).count(), 4)
471 self.assertEqual(Comment.objects.filter(user__in=subquery).count(), 5)
472
473 def test_filter_case_when(self):
474 msg = "When expression does not support composite primary keys."
475 with self.assertRaisesMessage(ValueError, msg):
476 Comment.objects.filter(text=Case(When(text="", then="pk")))
477 msg = "Case expression does not support composite primary keys."
478 with self.assertRaisesMessage(ValueError, msg):
479 Comment.objects.filter(text=Case(When(text="", then="text"), default="pk"))
480
481 def test_outer_ref_pk(self):
482 subquery = Subquery(Comment.objects.filter(pk=OuterRef("pk")).values("id"))
483 tests = [
484 ("", 5),
485 ("__gt", 0),
486 ("__gte", 5),
487 ("__lt", 0),
488 ("__lte", 5),
489 ]
490 for lookup, expected_count in tests:
491 with self.subTest(f"id{lookup}"):
492 queryset = Comment.objects.filter(**{f"id{lookup}": subquery})
493 self.assertEqual(queryset.count(), expected_count)
494
495 def test_unsupported_rhs(self):
496 pk = Exact(F("tenant_id"), 1)
497 msg = (
498 "'exact' subquery lookup of 'pk' only supports OuterRef "
499 "and QuerySet objects (received 'Exact')"
500 )
501 with self.assertRaisesMessage(ValueError, msg):
502 Comment.objects.filter(pk=pk)
503
504 @skipUnlessDBFeature("allow_sliced_subqueries_with_in")
505 def test_filter_comments_by_pk_exact_subquery(self):
506 self.assertSequenceEqual(
507 Comment.objects.filter(
508 pk=Comment.objects.filter(pk=self.comment_1.pk)[:1],
509 ),
510 [self.comment_1],
511 )
512 self.assertSequenceEqual(
513 Comment.objects.filter(
514 pk__in=Comment.objects.filter(pk=self.comment_1.pk).values(
515 "tenant_id", "id"
516 )[:1],
517 ),
518 [self.comment_1],
519 )
520 self.comment_2.integer = self.comment_1.id
521 self.comment_2.save()
522 self.assertSequenceEqual(
523 Comment.objects.filter(
524 pk__in=Comment.objects.values("tenant_id", "integer"),
525 )[:1],
526 [self.comment_1],
527 )
528
529 def test_outer_ref_not_composite_pk(self):
530 subquery = Comment.objects.filter(pk=OuterRef("id")).values("id")
531 queryset = Comment.objects.filter(id=Subquery(subquery))
532
533 msg = "Composite field lookups only work with composite expressions."
534 with self.assertRaisesMessage(ValueError, msg):
535 self.assertEqual(queryset.count(), 5)
536
537 def test_outer_ref_in_filtered_relation(self):
538 msg = (
539 "This queryset contains a reference to an outer query and may only be used "
540 "in a subquery."
541 )
542 with self.assertRaisesMessage(ValueError, msg):
543 self.assertSequenceEqual(
544 Tenant.objects.annotate(
545 filtered_tokens=FilteredRelation(
546 "tokens",
547 condition=Q(tokens__pk__gte=OuterRef("tokens")),
548 )
549 ).filter(filtered_tokens=(1, 1)),
550 [self.tenant_1],
551 )
552
553 def test_filter_by_tuple_containing_expression(self):
554 pk_lookup = (self.comment_1.tenant.id, (Value(self.comment_1.id) + 1) - 1)
555 for lookup in ({"pk": pk_lookup}, {"pk__in": [pk_lookup]}):
556 with self.subTest(lookup=lookup):
557 qs = Comment.objects.filter(**lookup)
558 self.assertEqual(qs.get(), self.comment_1)
559
560
561 @skipUnlessDBFeature("supports_tuple_lookups")
562 class CompositePKFilterTupleLookupFallbackTests(CompositePKFilterTests):
563 def setUp(self):
564 feature_patch = patch.object(
565 connection.features, "supports_tuple_lookups", False
566 )
567 self.enterContext(feature_patch)