mysql中基于加权标签的文章排序

Question

我使用以下结构：

Table Article:
id
title

Table ArticleTags:
id
articleid
tagid

Table Tags:
id
tagname
preferenece [0-5]

现在我需要根据多个标签对文章进行排序。是否可以使用mysql单一查询

示例：

article A is tagged by following tags and preference
pc 2
mobile 3
tech 1

article B is tagged by following tags and preference
tech 3
php 2
grails 4
mobile 2

现在，如果我想按标签排序-移动和科技，文章B将排在第一位。如果我只按手机排序，文章A将排在第一位。

Answer 1

因为标签对不同的文章有不同的权重，所以在articletags表而不是tags表中有一个偏好(权重)列是很自然的。否则，您将在具有相同名称但不同权重的标记中使用多个条目。

因此，我建议您更改您的模式

CREATE TABLE articletags
  (`id` int, `articleid` int, `tagid` int, `preference` int);
CREATE TABLE tags
  (`id` int, `tagname` varchar(32));

现在，获取每篇文章的权重和并应用顺序的所有其他规则都是相同的

SELECT a.* 
  FROM article a JOIN
(
  SELECT articleid, SUM(at.preference) total_preference
    FROM articletags at JOIN tags t
      ON at.tagid = t.id
   WHERE t.tagname IN ('mobile', 'tech')
   GROUP BY articleid
) q ON a.id = q.articleid
 ORDER BY q.total_preference DESC

这是演示

Answer 2

表之间的关系不是很清楚，我是这样想的：

Article Table (id) <= (articleid) ArticleTags Table (tagid) => (id) Tags Table

根据您给定的条件，看起来preference的SUM用于对输出进行排序。

因此，我以这个查询结束：

SELECT A.title
    , SUM(T.preference) AS preferenceSum
    FROM Tags T
    INNER JOIN ArticleTags AT ON T.tagid = T.id
    INNER JOIN Article A ON A.id = AT.articleid
    WHERE T.tagname IN ('mobile', 'tech')
    GROUP BY A.id
    ORDER BY preferenceSum DESC;

而且也不清楚如何存在具有不同preference的tags。从你的问题可以看出，mobile和tech具有不同的preference。还是我们这里少了几张桌子。

Answer 3

如果你想要它们按照最高的标签优先级排序，那么如下所示：-

SELECT Article.id, Article.title, Sub1.MaxPreference
FROM Article
INNER JOIN
(
    SELECT Article.id, MAX(Tags.preference) AS MaxPreference
    FROM Article
    INNER JOIN ArticleTags ON Article.id = ArticleTags.articleid
    INNER JOIN Tags ON ArticleTags.tagid = Tags.id
    GROUP BY Article.id
) Sub1
ON Article.id = Sub1.id
ORDER BY Sub1.MaxPreference DESC

将其限制为仅考虑几个标记：-

SELECT Article.id, Article.title, Sub1.MaxPreference
FROM Article
LEFT OUTER JOIN
(
    SELECT Article.id, MAX(Tags.preference) AS MaxPreference
    FROM Article
    INNER JOIN ArticleTags ON Article.id = ArticleTags.articleid
    INNER JOIN Tags ON ArticleTags.tagid = Tags.id
    WHERE Tags.tagname IN ('mobile','tech')
    GROUP BY Article.id
) Sub1
ON Article.id = Sub1.id
ORDER BY Sub1.MaxPreference DESC

如果您希望它们按偏好的总和排序，则如下所示：-

SELECT Article.id, Article.title, Sub1.SumPreference
FROM Article
INNER JOIN
(
    SELECT Article.id, SUM(Tags.preference) AS SumPreference
    FROM Article
    INNER JOIN ArticleTags ON Article.id = ArticleTags.articleid
    INNER JOIN Tags ON ArticleTags.tagid = Tags.id
    GROUP BY Article.id
) Sub1
ON Article.id = Sub1.id
ORDER BY Sub1.SumPreference DESC