问题描述
我正在尝试创建一个查询,并根据自定义的权重计算对它进行排序。
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加权查询(带注释的值)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!