Django查询 - 按标签共同排序 [英] Django Query - Sort by Tags in common

问题描述

作为一个简单的例子，假设我有一个产品类

As a quick example, let's say I have a Product class

class Product(models.Model):
    tags = models.ManyToManyField('Tag',blank=True,null=True)

我的标签类看起来像这样

My Tag class looks like this

class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True, db_index=True)

给定一个产品，我如何排序所有的结果集其他产品最常见的标签？

Given one product, how would I sort a result set of all other products by most common tags?

例如我有以下内容：

P1与标签A，B ，和C

P2与标签B，C

P3与标签B

P4与标签A，B和C

P1 with tags A, B, and C
P2 with tags B, C
P3 with tags B
P4 with tags A, B, and C

假设我们从结果集中排除P1，我希望P1的结果集是P4，P2，P3。

I would want my result set for P1 to be P4, P2, P3 in that order, assuming we are excluding P1 from the result set.

推荐答案

这是一个典型的自连接使用，SQL看起来像：

It's a typical self-join usage, the SQL looks like:

SELECT t3.*, count(t2.tag_id) as similar_tags_count
FROM m2m_tbl t1 INNER JOIN m2m_tbl t2 
     ON (t1.tag_id = t2.tag_id and t1.product_id != t2.product_id and t1.product_id = pk_of_the_given_product)
     INNER JOIN product_tbl t3 ON (t2.product_id = t3.id)
GROUP BY t3.id, t3.name
ORDER BY similar_tags_count DESC;

然后查询可以提供给 .raw（） ：

Then the query could be feed to .raw():

Product.objects.raw("""
SELECT t3.*, count(t2.tag_id) as similar_tags_count
FROM {m2m_tbl} t1 INNER JOIN {m2m_tbl} t2 
     ON (t1.tag_id = t2.tag_id and t1.product_id != t2.product_id and t1.product_id = %s)
     INNER JOIN {product_tbl} t3 ON (t2.product_id = t3.id)
GROUP BY t3.id, t3.name
ORDER BY similar_tags_count DESC;
""".format(m2m_tbl=Product.tags.through._meta.db_table, product_tbl=Product._meta.db_table),
    [the_given_product.pk])

或使用UNDOCUMENTED query.join（） （也 QuerySet，则可以在 query.join（） 的文档列表中使用l =nofollow ：

Or use UNDOCUMENTED query.join() (also in the docstring of the query.join()) to handle the join if you REALLY need a QuerySet:

m2m_tbl = Product.tags.through._meta.db_table
qs = Product.objects.exclude(pk=the_given_product.pk)
alias_1 = qs.query.get_initial_alias()
alias_2 = qs.query.join((alias_1, m2m_tbl, 'id', 'product_id'))
alias_3 = qs.query.join((alias_2, m2m_tbl, 'tag_id', 'tag_id'))
qs = qs.annotate(similar_tags_count=models.Count('tags__id')).extra(where=[
    '{alias_2}.product_id != {alias_3}.product_id'.format(alias_2=alias_2, alias_3=alias_3),
    '{alias_3}.product_id = %s'.format(alias_3=alias_3)
], params=[the_given_product.pk])

这篇关于Django查询 - 按标签共同排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！