查询一个相关模型上的精确匹配。

huangapple
go评论70阅读模式
英文:

Query an exact match on a related model

问题

  1. q1 = Chat.objects.filter(users=1)
  2. q2 = Chat.objects.filter(users=2)
  3. result = q1.intersection(q2)
  4. for c in result:
  5.     print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')
英文:

I have the following Django models

  1. class User(models.Model):
  2.     name = models.CharField(max_length=10)
  4.     def __str__(self):
  5.         return f'User {self.id} - {self.name}'
  8. class Chat(models.Model):
  9.     users = models.ManyToManyField(User, related_name='chats')
  10.     title = models.CharField(max_length=10)

The Chat model has a many-to-many relationship to the User model.

How can I find all the Chats for a specific group of users? Let's say we have the following Users

  1. User 1 - Adam
  2. User 2 - Bob
  3. User 3 - Carl
  4. User 4 - Dave

and they have the following Chats

  1. Chat 1 - AB1 - Adam, Bob
  2. Chat 2 - AB2 - Adam, Bob
  3. Chat 3 - AC1 - Adam, Carl
  4. Chat 4 - AC2 - Adam, Carl
  5. Chat 5 - AD1 - Adam, Dave
  6. Chat 6 - AD2 - Adam, Dave
  7. Chat 7 - BC1 - Bob, Carl
  8. Chat 8 - BC2 - Bob, Carl
  9. Chat 9 - BD1 - Bob, Dave
  10. Chat 10 - BD2 - Bob, Dave
  11. Chat 11 - CD1 - Carl, Dave
  12. Chat 12 - CD2 - Carl, Dave
  13. Chat 13 - ABC1 - Adam, Bob, Carl
  14. Chat 14 - ABC2 - Adam, Bob, Carl
  15. Chat 15 - ABD1 - Adam, Bob, Dave
  16. Chat 16 - ABD2 - Adam, Bob, Dave
  17. Chat 17 - ACD1 - Adam, Carl, Dave
  18. Chat 18 - ACD2 - Adam, Carl, Dave
  19. Chat 19 - BCD1 - Bob, Carl, Dave
  20. Chat 20 - BCD2 - Bob, Carl, Dave
  21. Chat 21 - ABCD1 - Adam, Bob, Carl, Dave
  22. Chat 22 - ABCD2 - Adam, Bob, Carl, Dave

And let's say we want to get all the Chats between User 1 - Adam and User 2 - Bob.

  1. This
  1.     for c in Chat.objects.prefetch_related('users').filter(users__in=[1, 2]):
  2.         print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will result into the following SQL queries

  1. SELECT "app_chat"."id", "app_chat"."title" FROM "app_chat" INNER JOIN "app_chat_users" ON ("app_chat"."id" = "app_chat_users"."chat_id") WHERE "app_chat_users"."user_id" IN (1, 2)
  3. SELECT ("app_chat_users"."chat_id") AS "_prefetch_related_val_chat_id", "app_user"."id", "app_user"."name" FROM "app_user" INNER JOIN "app_chat_users" ON ("app_user"."id" = "app_chat_users"."user_id") WHERE "app_chat_users"."chat_id" IN (1, 2, 3, 4, 5, 6, 13, 14, 15, 16, 17, 18, 21, 22, 7, 8, 9, 10, 19, 20)

and the result is all the Chats in which either User 1 - Adam or User 2 - Bob is (or both are) involved (with duplicates)

  1. This (and Q)
  1.     q = Q(users=1) & Q(users=2)
  2.     for c in Chat.objects.annotate(user_count=Count('users')).prefetch_related('users').filter(q, user_count=2):
  3.         print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will result into the following SQL query

  1. SELECT "app_chat"."id", "app_chat"."title", COUNT("app_chat_users"."user_id") AS "user_count" FROM "app_chat" LEFT OUTER JOIN "app_chat_users" ON ("app_chat"."id" = "app_chat_users"."chat_id") INNER JOIN "app_chat_users" T4 ON ("app_chat"."id" = T4."chat_id") WHERE (T4."user_id" = 1 AND T4."user_id" = 2) GROUP BY "app_chat"."id", "app_chat"."title" HAVING COUNT("app_chat_users"."user_id") = 2

and the result is simply empty

  1. This (or Q)
  1.     q = Q(users=1) | Q(users=2)
  2.     for c in Chat.objects.annotate(user_count=Count('users')).prefetch_related('users').filter(q, user_count=2):
  3.         print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will result into the following SQL queries

  1. SELECT "app_chat"."id", "app_chat"."title", COUNT("app_chat_users"."user_id") AS "user_count" FROM "app_chat" LEFT OUTER JOIN "app_chat_users" ON ("app_chat"."id" = "app_chat_users"."chat_id") INNER JOIN "app_chat_users" T4 ON ("app_chat"."id" = T4."chat_id") WHERE (T4."user_id" = 1 OR T4."user_id" = 2) GROUP BY "app_chat"."id", "app_chat"."title" HAVING COUNT("app_chat_users"."user_id") = 2
  3. SELECT ("app_chat_users"."chat_id") AS "_prefetch_related_val_chat_id", "app_user"."id", "app_user"."name" FROM "app_user" INNER JOIN "app_chat_users" ON ("app_user"."id" = "app_chat_users"."user_id") WHERE "app_chat_users"."chat_id" IN (3, 4, 5, 6, 7, 8, 9, 10)
  4. end current

and the result is all the Chats where either User 1 - Adam or User 2 - Bob is (but not both are) involved (without duplicates). This surprised me a bit as I would have expected that everything from Chat 1 to Chat 10 should be returned

  1. This (and of 3 querysets)
  1.     q1 = Chat.objects.prefetch_related('users').filter(users=1)
  2.     q2 = Chat.objects.prefetch_related('users').filter(users=2)
  3.     q3 = Chat.objects.annotate(user_count=Count('users')).filter(user_count=2)
  4.     for c in q3 & q1 & q2:
  5.         print(f'Chat {c.id} - {", ".join([u.name for u in c.users.all()])}')

will finally return the correct result, but I am wondering whether there is a better (more django-ish) way to achieve this

答案1

得分: 1

以下是翻译好的部分：

你可以使用以下方式进行筛选：

  1. from django.db.models import Count, Q
  3. Chat.objects.alias(
  4.     user_count=Count('users'), match=Count('users', filter=Q(users__in=[1, 2]))
  5. ).filter(user_count=2, match=2)

这将确保在聊天中涉及两名用户，这两名用户分别是用户1和用户2。

英文:

You can filter with:

<pre><code>from django.db.models import Count, Q

Chat.objects.alias(
user_count=Count('users'), match=Count('users', filter=<b>Q(users__in=[1, 2]</b>))
).filter(user_count=2, match=2)</code></pre>

This will ensure that there are two users involved in the chat, and that these two users are user 1 and user 2.

huangapple
  • 本文由 发表于 2023年5月13日 23:43:06
  • 转载请务必保留本文链接：https://go.coder-hub.com/76243602.html
  • django-models
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定