本文介绍了Django加权查询(带注释的值)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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加权查询(带注释的值)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 23:25