制作一个查询来构建具有挑战性的Django查询

本文介绍了制作一个查询来构建具有挑战性的Django查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有一个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里面。它不行;我收到错误：列访客不存在。 但是列访问者存在 - 看模型 - 因此我不确定这个错误是什么意思。这是一个屏幕截图它有一些更多的信息： 注意：我也尝试这个，这很有趣，但解决方案并不满足（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 had3) Finally, groups listing should be sorted by number of unique visitors in the past 1 hourTo 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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！