制作一个查询来构建具有挑战性的Django查询 [英] Crafting a query to construct a challenging Django queryset

问题描述

我有一个Django网站，用户可以让用户组和其他用户访问组来发布内容。团体可以向公众开放，也可以保密。这些模型是简单的：

  class Group（models.Model）：
 topic = models.TextField（validators = MaxLengthValidator（200）]）
 owner = models.ForeignKey（User）
 private = models.CharField（max_length = 5，default = 0）
 created_at = models.DateTimeField（auto_now_add = True） 
 
 class GroupTraffic（models.Model）：
 visitor = models.ForeignKey（User）
 which_group = models.ForeignKey（Group）
 time = models.DateTimeField db_index = True，auto_now_add = True）
  

您能帮助我构建网站上所有组的列表满足以下规则？

1）我想仅显示所有公共组（即 group.private ='0'）

2）对于每个组，我想注释总共有

3）最后，团体列表应该按照过去1小时内的唯一身份访问者数量排序

为了达到上述目的，这是我试过的（请滚动）：

  date = datetime.now（） -  timedelta（hours = 1）
 groups = Group.objects.filter（private ='0'）。annotate（total_views = Count（'grouptraffic__visitor'） ）.extra（select = {'uniques'：'SELECT COUNT（DISTINCT visitor）FROM links_grouptraffic AS grptraffic WHERE（links_grouptraffic.time> ％s AND links_grouptraffic.which_group_id = grptraffic.which_group_id）'}，select_params =（date，），）。order_by（' -  uniques'）
  

请注意，链接是应用程序的名称。为了满足（1）我使用过滤器，以满足（2）我注释计数聚合，并满足（3）我使用额外的一些SQL里面。它不行;我收到错误：列访客不存在。 但是列访问者存在 - 看模型 - 因此我不确定这个错误是什么意思。这是一个屏幕截图它有一些更多的信息：

< ？我的数据库是postgres，高效的表现是我的关键。

---

注意：我也尝试这个，这很有趣，但解决方案并不满足（1），也不能修改它。

解决方案

据了解，postgres表中的vistor字段的名称将为visitor_id，因为它是ForeignKey 。它作为整数存储在数据库中。

I have a Django website where users can make groups and other users then visit the groups to post content. Groups can be open to public, or kept private. The models are simply:

class Group(models.Model):
    topic = models.TextField(validators=[MaxLengthValidator(200)])
    owner = models.ForeignKey(User)
    private = models.CharField(max_length=5, default=0)
    created_at = models.DateTimeField(auto_now_add=True)

class GroupTraffic(models.Model):
    visitor = models.ForeignKey(User)
    which_group = models.ForeignKey(Group)
    time = models.DateTimeField(db_index=True, auto_now_add=True)

Can you help me frame a listing of all groups on the website, satisfying the following rules?

1) I want to display only and all public groups (i.e. group.private='0')

2) To each group, I want to annotate the total views it has ever had

3) Finally, groups listing should be sorted by number of unique visitors in the past 1 hour

To achieve the above, this is what I have tried (please scroll):

date = datetime.now()-timedelta(hours=1)
groups = Group.objects.filter(private='0').annotate(total_views=Count('grouptraffic__visitor')).extra(select={'uniques':'SELECT COUNT (DISTINCT visitor) FROM links_grouptraffic AS grptraffic WHERE (links_grouptraffic.time > %s AND links_grouptraffic.which_group_id=grptraffic.which_group_id)'},select_params=(date,),).order_by('-uniques')

Note that links is the name of the app. To satisfy (1) I use filter, to satisfy (2) I annotate a Count aggregation, and to satisfy (3) I use extra with some SQL inside it. It doesn't work; I get the error: column "visitor" does not exist. But column visitor DOES exist - see models - hence I'm unsure what this error means. Here's a screenshot; it has some more information:

Can you help me frame queries that satisfy 1, 2 and 3? My DB is postgres, and efficient performance is key for me.

---

Note: I also tried this, it was interesting but the solution didn't satisfy (1), nor could I modify it to do so.

解决方案

As far as I know the name of your vistor field in the postgres table will be "visitor_id" as it is a ForeignKey. It is stored as an integer in the database.

这篇关于制作一个查询来构建具有挑战性的Django查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！