选择所有具有匹配标签的项目 [英] Select all items that have matching tags
问题描述
假设我有以下Django模型:
Suppose I have the following Django models:
class Article(models.Model):
title = models.CharField(max_length=200)
blog = models.CharField(max_length=255)
rating = models.IntegerField(default=0)
class ArticleTag(models.Model):
article = models.ForeignKey(Article)
tag = models.CharField(max_length=200)
添加一些数据:
ArticleID Rating Blog
-----------------------------------------
article1 -> 1 3 CNN
article2 -> 2 2 BBC
article3 -> 3 5 BBC
article4 -> 4 9 NTV
ArticleID tag
-------------------
1 tag1
1 tag2
1 tag3
2 tag1
2 tag4
3 tag5
4 tag6
4 tag7
假设我们有一个用户喜欢 tag1
, tag2
, tag6
和 BBC
。所有文章均符合要求,因为article1具有 tag1
和 tag2
,article4具有 tag1
,article2和article3来自 BBC
。
Suppose we have a user that likes tag1
, tag2
, tag6
and BBC
. All the articles match the requirements, because article1 has tag1
and tag2
, article4 has tag1
, article2 and article3 are from BBC
.
如果我们通过评分对其进行排序: article4
, article3
, article1
, article2
。
If we order them by rating: article4
, article3
, article1
, article2
.
但是,我需要先根据项目具有的匹配标签数+博客来对其进行排序,然后再对第二项进行评分订购参数。因此,我期望结果按以下顺序进行:
However, I need to order items by the number of matching tags they have + blog first, and then by rating as the second ordering parameter. So I expect the results in the following order:
- article1-
tag1
和tag2
,等级= 3 - article2-
tag1
和BBC
,等级= 2 - 第4条-
tag6
,等级= 9 - article3-
BBC
,等级= 5
- article1 -
tag1
andtag2
, rating=3 - article2 -
tag1
andBBC
, rating=2 - article4 -
tag6
, rating=9 - article3 -
BBC
, rating=5
是可能在Django中做到这一点?如果没有,那么PostgreSQL呢?
Is it possible to do this in Django? If not, what about PostgreSQL?
推荐答案
SQL查询看起来像这样:
The SQL query could look like this:
SELECT *
FROM Article a
LEFT JOIN (
SELECT ArticleID, count(*) AS ct
FROM ArticleTag
WHERE tag IN ('tag1', 'tag2', 'tag6') -- your tags here
GROUP BY ArticleID
) t ON t.ArticleID = a.ID
ORDER BY t.ct DESC NULLS LAST
, (a.blog = 'BBC') DESC NULLS LAST -- your blog here
, rating DESC NULLS LAST;
基本上:
- 计算子查询
t
中每个ArticleID
的匹配标签。 -
向左联接
到主表,其中包含二级数据(blog
)和三级数据( -
ORDER BY
这三个条件是ct
首先,博客
接下来,评分
最后。它们全部降序(最高值在前)。这也适用于布尔表达式(a.blog ='BBC')
,因为TRUE
(1)排序在FALSE
(0)之前按降序排列。
- Count the matching tags per
ArticleID
in subqueryt
. LEFT JOIN
the main table to it with data for secondary (blog
) and tertiary (rating
) sort criteria.ORDER BY
the three criteria,ct
first,blog
next,rating
last. All of them descending (highest value first). That works for the boolean expression(a.blog = 'BBC')
as well, becauseTRUE
(1) sorts beforeFALSE
(0) in descending order.
重要提示: 以降序排列,NULL值将首先排序,因此如果可以为NULL值,则需要 NULLS LAST
(如果
Important: In descending order NULL values would sort first, so NULLS LAST
is needed if there can be NULL values (and does not hurt if there cannot).
- PostgreSQL sort by datetime asc, null first?
即使您所有的列都定义为 NOT NULL
,由于 LEFT JOIN,
。 ct
仍然可以为NULL
Even if all your columns are defined NOT NULL
, ct
can still be NULL due to the LEFT JOIN
.
如果Django保留带有双引号的大小写混合名称,那么您也必须在SQL中执行此操作。否则,所有标识符都将强制转换为小写。
If Django preserves mixed case names with double-quotes, you have to do that in SQL, too. Otherwise all identifiers are cast to lower case.
这篇关于选择所有具有匹配标签的项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!