排除用户从注释查询中。

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

Exclude user from an annotation query

问题

I'm trying to query the users presents in a certain conversation. I finally got it working with this annotation:

我正在尝试查询某个对话中出现的用户。我最终通过以下注释使其工作：

  1. products = products.annotate(
  2.     conversation_users=Subquery(
  3.         Conversation.objects.filter(
  4.             product=OuterRef('pk')
  5.         ).annotate(
  6.             users_array=ArrayAgg('users__username')
  7.         ).values_list('users_array', flat=True)[:1]
  8.     )
  9. )

This is returning a list with the user present in the conversation (in most cases only two). I need to exclude from there the request.user so I get always the value of the user who I am having the conversation with.

这返回一个包含在对话中的用户列表（在大多数情况下只有两个）。我需要从中排除request.user，以便始终获取我正在进行对话的用户的值。

I tried using this Q query trying to exclude the request.user:

我尝试使用以下的 Q 查询来排除request.user：

  1. .filter(~Q(users=user)).values_list('users_array', flat=True)[:1]

but it is making the field now return None. How can I accomplish that?

但它导致该字段现在返回 None。我该如何实现这一点？

Edit 1:

Those are the relevant models, Product model:

以下是相关的模型，Product 模型：

  1. class Product(models.Model):
  2.     creator = models.ForeignKey(
  3.         User, on_delete=models.CASCADE, related_name='anken')
  4.     content = models.TextField(blank=True, null=True)
  5.     date = models.DateTimeField(auto_now_add=True)
  6.     active = models.BooleanField(default=True)
  7.     intouch = models.ManyToManyField(
  8.         User, related_name='product_intouch', blank=True)

And this is the Conversation model:

这是 Conversation 模型：

  1. class Conversation(models.Model):
  2.     product = models.ForeignKey(
  3.         Product, on_delete=models.CASCADE, related_name='conversations')
  4.     users = models.ManyToManyField(User, related_name='conversations')
  5.     
  6.     def validate_unique(self, *args, **kwargs):
  7.         super().validate_unique(*args, **kwargs)
  8.         if Conversation.objects.filter(product=self.product, users__in=self.users.all()).exists():
  9.             raise ValidationError(
  10.                 'A conversation with the same Product and user already exists.')

Is there anything else you need assistance with?

英文:

I'm trying to query the users presents in a certain conversation. I finally got it working with this annotation:

  1.     products = products.annotate(
  2.         conversation_users=Subquery(
  3.             Conversation.objects.filter(
  4.                 product=OuterRef('pk')
  5.             ).annotate(
  6.                 users_array=ArrayAgg('users__username')
  7.             ).values_list('users_array', flat=True)[:1]
  8.         )
  9.     )

This is returning a list with the user present in the conversation (in most cases only two). I need to exclude from there the request.user so I get always the value of the user who I am having the conversation with.

I tried using this Q query trying to exclude the request.user:

  1. .filter(~Q(users=user)).values_list('users_array', flat=True)[:1]
  2.         )

but it is making the field now return None. How can I accomplish that?

Edit 1:

Those are the relevant models, Product model:

  1. class Product(models.Model):
  2.     creator = models.ForeignKey(
  3.         User, on_delete=models.CASCADE, related_name='anken')
  4.     content = models.TextField(blank=True, null=True)
  5.     date = models.DateTimeField(auto_now_add=True)
  6.     active = models.BooleanField(default=True)
  7.     intouch = models.ManyToManyField(
  8.         User, related_name='product_intouch', blank=True)

And this is the Conversation model:

  1. class Conversation(models.Model):
  2.     product = models.ForeignKey(
  3.         Product, on_delete=models.CASCADE, related_name='conversations')
  4.     users = models.ManyToManyField(User, related_name='conversations')
  5.     
  7.     def validate_unique(self, *args, **kwargs):
  8.         super().validate_unique(*args, **kwargs)
  9.         if Conversation.objects.filter(product=self.product, users__in=self.users.all()).exists():
  10.             raise ValidationError(
  11.                 'A conversation with the same Product and user already exists.')

答案1

得分: 1

你可以在聚合函数中过滤掉这部分内容：

  1. from django.contrib.postgres.aggregates import ArrayAgg
  2. from django.db.models import OuterRef, Q, Subquery
  4. products = products.annotate(
  5.     conversation_users=Subquery(
  6.         Conversation.objects.filter(
  7.             product=OuterRef('pk'),
  8.         )
  9.         .annotate(users_array=ArrayAgg('users__username', filter=~Q(users=user)))
  10.         .values_list('users_array', flat=True)[:1]
  11.     )
  12. )
英文:

You filter this out in the aggregate:
<pre><code>from django.contrib.postgres.aggregates import ArrayAgg
from django.db.models import OuterRef, Q, Subquery

products = products.annotate(
conversation_users=Subquery(
Conversation.objects.filter(
product=OuterRef('pk'),
)
.annotate(users_array=ArrayAgg('users__username', <b>filter=~Q(users=user)</b>))
.values_list('users_array', flat=True)[:1]
)
)</code></pre>

huangapple
  • 本文由 发表于 2023年5月10日 18:56:10
  • 转载请务必保留本文链接：https://go.coder-hub.com/76217575.html
  • django
  • django-orm
  • django-queryset
  • python
匿名

发表评论

匿名网友

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

确定