如何在Django中进行过滤之前应用开窗功能 [英] How to apply windowing function before filter in Django

问题描述

我有以下模型:

class Customer(models.Model):
    ....

class Job(models.Model):
    customer = models.ForeignKey('Customer')
    payment_status = models.ForeignKey('PaymentStatus')
    cleaner = models.ForeignKey(settings.AUTH_USER_MODEL,...)

class PaymentStatus(models.Model): 
    is_owing = models.NullBooleanField()

我需要为每个工作找出父客户拥有的总欠下工作数量，但仅显示属于当前用户的那些工作.queryset应该是这样的:

I need to find out, for each job, how many total owed jobs the parent customer has, but only display those jobs belonging to the current user. The queryset should be something like this:

user = self.request.user
queryset = Job.objects.select_related('customer'
    ).filter(payment_status__is_owing=True).annotate(
                num_owings=RawSQL('count(jobs_job.id) over (partition by customer_id)', ())
                                       ).filter(cleaner=user)

我正在使用"select_related"来显示来自客户的与工作相关的字段.

I am using 'select_related' to display fields from the customer related to the job.

首先，我没有找到没有窗口函数/原始SQL的方法.其次，无论我将.filter(window_cleaner = user)放在何处(在annotate()之前或之后)，最终结果始终是在总数中排除不属于当前用户的作业.我需要将作业从显示中排除，而不要从窗口功能中的计数中排除.我可以将整个事情作为原始SQL进行，但是我希望在Django中有更好的方法.谢谢！

Firstly I haven't found a way to do this without the windowing function/raw SQL. Secondly, regardless of where I place the .filter(window_cleaner=user) (before or afer the annotate()), the final result is always to exclude the jobs that do not belong to the current user in the total count. I need to exclude the jobs from displaying, but not from the count in the windowing function. I could do the whole thing as raw SQL, but I was hoping there was a nicer way of doing it in Django. Thanks!

推荐答案

我不知道这是否有帮助，这实际上取决于您希望如何向用户显示结果.但是，如果是我在设计方面有空手的话，我可能会分开我的窗户.也许将顶部客户的欠工总数放在顶部，并在下面将属于当前用户的工作单独列出.然后，我将像常规查询那样拆分数据结构，以查找与当前用户相关的工作，然后使用自定义模板标记来计算父客户的工作总数.

I don't know if this helps and it really depends on how you are wanting to display the results to your user. However if it were me with a free hand to the design aspect I would probably split my window. Perhaps having the total of owed jobs for the parent customer at the top and a separate list for the jobs that belong to the current user below. Then I would split the construction of the data doing a normal query, as you have, for the jobs relating to the current user but then use a custom template tag to calculate the total number of jobs for the parent customer.

我经常使用自定义模板标签.我发现对于我们都希望向用户显示的那些快速快照总数，它们非常酷.例如....累计的总点数，未完成的任务数等等.

I use custom template tags quite a bit. I find they are very cool for those quick snapshot totals that we all want to display to our users. For example....the total number of points accumulated, the number of outstanding tasks, etc etc.

如果您以前没有看过它们，请查看

If you've not looked at them previously check out the docs at https://docs.djangoproject.com/en/1.11/howto/custom-template-tags/

它们真的很容易使用.

这篇关于如何在Django中进行过滤之前应用开窗功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！