Django Manytomany查询奇怪的行为 [英] Django manytomany query weird behavior

问题描述

我有以下模型：

  class Post（Model）：
 word = TextField（）
订阅者= ManyToManyField（User，related_name ='subscribed'，through ='Subscription'）
 
类订阅（模型）：
 post = ForeignKey（Post）
 subscriber = ForeignKey（User）
 date_subscribed = DateTimeField（default = timezone.now）
 
 class Meta：
 order =（'-date_subscribed'，）
 unique_together =（（ 'post'，'subscriber'））
  

我想做的是选择所有帖子，订单他们按订阅者数量，如果订阅者数量相等，则按最后一个订单 date_subscribed 。

我的输入数据：

  post1 = Post（text =post1）
 post2 = Post（text =post2） 
 post3 = Post（text =post3）
 post4 = Post（text =post4）
 
 user1 =用户（username =user1）
 user2 =用户（username =user2）
 user3 =用户（username =user3）
 user4 =用户（username =user4）
 
 Subscription.objects.create（post = post1，user = user1）
订阅。 object.create（post = post2，user = user1）
 Subscription.objects.create（post = post3，user = user1）
 Subscription.objects.create（post = post3，user = user2）
 Subscription.objects.create（post = post3，user = user3）
 Subscription.objects.create（post = post3，user = user4）
 Subscription.objects.create（post = post4，user = user1）
 Subscription.objects.create（post = post4，user = user2）
 Subscription.objects.create（post = post4，user = user3）
  / pre> 
 
 
此查询按预期工作，但不按 date_subscribed 排序：
  Post.objects.annotate（s_count = Count（'subscribers'））。order_by（' -  s_count'）
  
当我写：
 发布。 objects.annotate（s_count = Count（'subscribers'））。order_by（' -  s_count'，'-subscription__date_subscribed'） 
  
我有很奇怪的结果，我不太明白这个行为。对于上述数据，它完全输出所有帖子，其中 s_count = 1 。
 
 
 
为什么 s_count 是1？还有，如何正确地订购最后一个 date_subscribed ？
 
 
 
 UPD：
还有一个问题。为什么 Post.objects.annotate（s_count = Count（'subscribers'））。order_by（'-s_count'，'-subscription__date_subscribed'）。count（）订阅中的行数？
解决方案
由于订阅在 Post 和订阅者之间的m2m关系表，当您在订阅字段模型本身，所有帖子在结果集中显示为单独的行，这就是为什么您得到 s_count = 1 ，因为每个具有特定订阅者的帖子
 
 
 
您需要使用最新的 date_subscribed  Post  c $ c>所有订阅者，然后在注释字段上订单：
  posts = Post.objects.annotate（
 s_count = Count（'subscribers'），
 s_date_max = Max（'subscription__date_subscribed'）
）.order_by '-s_count'，'-s_date_max'）
  
 
 
 
 
 
 
 下一个问题的更新： 
 
 
 
如果您使用 count（）方法，它将返回帖子数量。您可以看到它与您从 len（queryset.values_list（'s_count'，'subscription__date_subscribed'）））获得的计数有所不同，因为此时的日期的个别值已经在结果集中提取。
 
I have a following models:
class Post(Model):
    word = TextField()
    subscribers = ManyToManyField(User, related_name='subscribed',    through='Subscription')

class Subscription(Model):
    post = ForeignKey(Post)
    subscriber = ForeignKey(User)
    date_subscribed = DateTimeField(default=timezone.now)

    class Meta:
        ordering = ('-date_subscribed', )
        unique_together = (('post', 'subscriber'))
What I want to do is to select all posts, order them by number of subscribers, and if the number of subscribers is equal, order by last date_subscribed.

My input data:
post1 = Post(text="post1")
post2 = Post(text="post2")
post3 = Post(text="post3")
post4 = Post(text="post4")

user1 = User(username="user1")
user2 = User(username="user2")
user3 = User(username="user3")
user4 = User(username="user4")

Subscription.objects.create(post=post1, user=user1)
Subscription.objects.create(post=post2, user=user1)
Subscription.objects.create(post=post3, user=user1)
Subscription.objects.create(post=post3, user=user2)
Subscription.objects.create(post=post3, user=user3)
Subscription.objects.create(post=post3, user=user4)
Subscription.objects.create(post=post4, user=user1)
Subscription.objects.create(post=post4, user=user2)
Subscription.objects.create(post=post4, user=user3)
This query works as expected but it doesn't order by date_subscribed:
Post.objects.annotate(s_count=Count('subscribers')).order_by('-s_count')
When I write:
Post.objects.annotate(s_count=Count('subscribers')).order_by('-s_count', '-subscription__date_subscribed') 
I got weird results and I don't really understand this behavior. For above data it outputs exactly all posts with s_count=1.

Why s_count is 1? And, also how to order correctly by last date_subscribed?

UPD:
One more question. Why Post.objects.annotate(s_count=Count('subscribers')).order_by‌​('-s_count', '-subscription__date_subscribed').count() gives 4 instead of number of rows in Subscription?
解决方案
Since Subscription is a through table for m2m relation between the Post and Subscriber, when you order on a field of Subscription model itself, all the posts appear as individual rows in the result set and that's why you get s_count=1 because each post with a particular subscriber is unique.

You need to annotate the Post objects with the latest date_subscribed of all the subscribers and then order on annotated field:
posts = Post.objects.annotate(
            s_count=Count('subscribers'),
            s_date_max=Max('subscription__date_subscribed')
        ).order_by('-s_count', '-s_date_max')




UPDATE for next question:

If you use count() method it will return the number of Posts. You can see that it will differ from the count you get from len(queryset.values_list('s_count', 'subscription__date_subscribed')) because at this time the individual values for dates have been fetched in the result set.

                        
这篇关于Django Manytomany查询奇怪的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！