Django加权查询（带注释的值） [英] Django weighted query (annotated values)

问题描述

我正在尝试创建一个查询，并根据自定义的权重计算对它进行排序。

I am attempting to created a query and sort it based on a custom calculation of weights.

我需要一些帮助，因为我所采用的解决方案确实可以工作，但性能却是不是我想要的地方

I require some help as the solution I have come to does indeed work but the performance is not where I'd like it to be

我拥有的是Media对象。它具有相关的注释，喜欢和命令。

What I have is a Media object. It has related Comments, Likes and Orders.

当前有效但完全乱成一团的是以下查询：

What currently works but is a complete hacky mess is the following query:

    products = (Media.objects
        .select_related(
            'image',
            'currency',
            'user',
            'user__image',
        )
        .prefetch_related('category', 'tags')
        .exclude(is_deleted=1)
        .filter(Q(category__category__in=categories) | Q(tags__tag__title=query))
        .annotate(order_count = Count('orders', distinct=True))
        .annotate(comment_count = Count('comments', distinct=True))
        .annotate(like_count = Count('likes', distinct=True))
        .annotate(weight = Count(0))
        .distinct())
    
    for m in products.iterator():
        initial_weight  = int(m.order_count)*40 + int(m.comment_count)*4 + int(m.like_count)*4 + int(m.clicks)
        m.weight        = float(float(initial_weight) - float(m.views/50))

如您所见，我在分别注释所有参数会一直使用，然后对查询集中的每个项目都进行一次愚蠢的迭代，其中充满了算术运算，这是次优的。

As you can see I am separately annotating all the parameters I'll be using and then doing a stupid iteration full of arithmetic operations for EVERY item in the queryset which is very sub optimal.

我尝试做的一件事是：

    products = (Media.objects
        .select_related(
            'image',
            'currency',
            'user',
            'user__image',
        )
        .prefetch_related('category', 'tags')
        .exclude(is_deleted=1)
        .filter(Q(category__category__in=categories) | Q(tags__tag__title=query))
        .annotate(weight = Count('orders', distinct=True) * 40 + Count('comments', distinct=True) * 4 + Count('likes', distinct=True) - F('views')/50 + F('clicks')))

但是注释是不可能的（在有和没有Sum（）的情况下尝试了几种变体-Django一直抱怨被注释的值是不同类型的。

But similar operations in the annotation were impossible (tried a few variations with and without Sum() - Django always complained that the annotated values were of different type.

通过这种方式，我们在本项目中使用django 1.8 。

By the way we're using django 1.8 for this project.

是否有很好的单查询方法来获取所需的排序权重？

Is there a good single-query aproach to getting my desired sorting weights?

推荐答案

首先，您需要确保除法运算会产生浮点数（不取整）。您需要类似这样的内容（在这里可耻地被盗了）：

First, you would need to make sure that division would yield floats (without rounding). You would need something like this (disgracefully stolen here):

ExpressionWrapper(
    (F('views') / Decimal(50.0), 
    output_field=FloatField()),
)

因此，查询看起来像这样：

So, query would look like this:

products = (Media.objects
    .exclude(is_deleted=1)
    .filter(Q(category__category__in=categories) | Q(tags__tag__title=query))
    .annotate(order_count = Count('orders', distinct=True))
    .annotate(comment_count = Count('comments', distinct=True))
    .annotate(like_count = Count('likes', distinct=True))
    .annotate(weight = Count(0))
    .annotate(
        initial_weight=ExpressionWrapper(
            F('order_count') * 40 + F('comment_count') * 4 +
            F('like_count') * 4 + F('clicks'),
            output_field=FloatField()
        )
     )
    .annotate(
        views_divided=ExpressionWrapper((F('views') / Decimal(50.0), 
                                        output_field=FloatField()))
     )
    .annotate(weight=F('initial_weight') - F('views_divided'))
    .distinct())

看起来很丑，但应该可以工作（我认为）。

Looks ugly, but should work (I think).

边注-如果您只需要计算权重，实际上您不必使用 prefetch_related ， select_realted ，django会自己处理这些东西（但是，这只是我的猜测-如果您稍后在代码中实际使用那些外键，则是合理的）。

On the side note - if you only need to calculate weight, you don't actually have to use prefetch_related and select_realted, django will take care of that stuff itself (however, that's just my speculation - if you actually use those foreign keys later in the code, then it's justified).

这篇关于Django加权查询（带注释的值）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！