上下文
我正在尝试在我的网站上创建一个“feed”系统,用户可以访问他们的feed,查看他们在网站上可以做的不同事情的所有新通知。例如,在“feed”部分,用户可以看到他们关注的用户是否创建了文章,以及用户是否对文章发表了评论。我当前的feed系统只是使用两个单独的查询来获取这些信息。但是,我想将这两个查询合并为一个查询,这样用户就可以按时间顺序查看他们所关注的那些查询的活动。按照我的系统现在的工作方式,我从用户关注的每个人那里得到五篇文章,放在“feed”部分,然后得到五篇文章评论,并在“feed”部分的同一区域发布。我不想把这些查询分开,而是想把它们组合起来,这样,它们就不会看到一行五篇文章,然后一行五条评论,而是会看到按时间顺序出现的提要,不管其他用户是先创建一篇文章,然后评论,然后创建另一篇文章,还是按什么顺序,而不是总是看到相同顺序的通知。
问题
首先,如果您想重新创建表,让我向您展示我的表创建代码。首先要做的是创建一个 users
我的table articles
以及 articlecomments
表格参考:
CREATE TABLE users (
idUsers int(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
uidUsers TINYTEXT NOT NULL,
emailUsers VARCHAR(100) NOT NULL,
pwdUsers LONGTEXT NOT NULL,
created DATETIME NOT NULL,
UNIQUE (emailUsers),
FULLTEXT(uidUsers)
) ENGINE=InnoDB;
接下来,让我们创建 articles
表格:
CREATE TABLE articles (
article_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
article TEXT NOT NULL,
date DATETIME NOT NULL,
idUsers int(11) NOT NULL,
topic VARCHAR(50) NOT NULL,
published VARCHAR(50) NOT NULL,
PRIMARY KEY (article_id),
FULLTEXT(title, article),
FOREIGN KEY (idUsers) REFERENCES users (idUsers) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB;
最后,我们需要 articlecomments
表格:
CREATE TABLE articlecomments (
comment_id INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL,
message TEXT NOT NULL,
date DATETIME NOT NULL,
article_id INT(11) UNSIGNED NOT NULL,
idUsers INT(11) NOT NULL,
seen TINYTEXT NOT NULL,
FOREIGN KEY (article_id) REFERENCES articles (article_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (idUsers) REFERENCES users (idUsers) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
为了充分填充本例中的表,我们将使用以下语句:
INSERT INTO users (uidUsers, emailUsers, pwdUsers, created) VALUES ('genericUser', 'genericUser@hotmail.com', 'password', NOW());
INSERT INTO articles (title, article, date, idUsers, topic, published) VALUES ('first article', 'first article contents', NOW(), '1', 'other', 'yes');
INSERT INTO articles (title, article, date, idUsers, topic, published) VALUES ('second article', 'second article contents', NOW(), '1', 'other', 'yes');
INSERT INTO articles (title, article, date, idUsers, topic, published) VALUES ('third article', 'third article contents', NOW(), '1', 'other', 'yes');
INSERT INTO articlecomments (message, date, article_id, idUsers, seen) VALUES ('first message', NOW(), '1', '1', 'false');
INSERT INTO articlecomments (message, date, article_id, idUsers, seen) VALUES ('second message', NOW(), '1', '1', 'false');
INSERT INTO articlecomments (message, date, article_id, idUsers, seen) VALUES ('third message', NOW(), '1', '1', 'false');
我用来从 articles
以及 articlecomments
表格如下:
查询1:
SELECT
articles.article_id, articles.title, articles.date,
articles.idUsers, users.uidUsers
FROM articles
JOIN users ON articles.idUsers = users.idUsers
WHERE articles.idUsers = '1' AND articles.published = 'yes'
ORDER BY articles.date DESC
LIMIT 5
问题2:
SELECT
articlecomments.comment_id, articlecomments.message,
articlecomments.date, articlecomments.article_id, users.uidUsers
FROM articlecomments
JOIN users ON articlecomments.idUsers = users.idUsers
WHERE articlecomments.idUsers = '1'
ORDER BY articlecomments.date DESC
LIMIT 5
如何组合这两个包含不同信息和列的查询,以便根据创建日期对它们进行排序( articles.date
以及 articlecomments.date
,分别是)?我希望他们分开,而不是同一排。所以,应该像我分别查询它们,然后简单地将结果行组合在一起。如果有三篇文章和三条评论,我希望总共返回六行。
这是我想要的样子。考虑到有三篇文章和三篇评论,并且评论是在文章之后创建的,这就是综合上述查询后的结果(我不确定如果列名称不同,这种描述是否可行,但我想知道是否可以实现类似的效果):
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
| id (article_id or comment_id) | title/message | date | article_id (because it is referenced in articlecomments table) | idUsers | uidUsers |
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
| 1 | first message | 2020-07-07 11:27:15 | 1 | 1 | genericUser |
| 2 | second message | 2020-07-07 11:27:15 | 1 | 1 | genericUser |
| 3 | third message | 2020-07-07 11:27:15 | 1 | 1 | genericUser |
| 2 | second article | 2020-07-07 10:47:35 | 2 | 1 | genericUser |
| 3 | third article | 2020-07-07 10:47:35 | 3 | 1 | genericUser |
| 1 | first article | 2020-07-07 10:46:51 | 1 | 1 | genericUser |
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
我试过的东西
我读到这可能涉及 JOIN
或者 UNION
但我不确定在这种情况下如何实现它们。我尝试通过简单地使用 (Query 1) UNION (Query 2)
,它首先告诉我两个查询中的列数不同,所以我必须删除 idUsers
我的专栏 articlecomments
查询。这实际上让我有点接近,但它的格式不正确:
+------------+-------------------+---------------------+---------+-------------+
| article_id | title | date | idUsers | uidUsers |
+------------+-------------------+---------------------+---------+-------------+
| 2 | first message | 2020-07-07 10:47:35 | 1 | genericUser |
| 3 | third article | 2020-07-07 10:47:35 | 1 | genericUser |
| 1 | first article | 2020-07-07 10:46:51 | 1 | genericUser |
| 1 | second article | 2020-07-07 11:27:15 | 1 | genericUser |
| 2 | third article | 2020-07-07 11:27:15 | 1 | genericUser |
| 3 | first article | 2020-07-07 11:27:15 | 1 | genericUser |
+------------+-------------------+---------------------+---------+-------------+
有什么想法吗?如果有任何困惑,请告诉我。谢谢。
Server type: MariaDB Server version: 10.4.8-MariaDB - mariadb.org binary distribution
3条答案
按热度按时间zlwx9yxi1#
这看起来像mysql。你可以这样做:
参见此处示例:https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=26280a9c1c5f62fc33d00d93ab84adf3
结果如下:
解释
既然我们想用
order by
以及limit
,我们将在第一行之外创建一个子查询,并从第一个子查询中选择所有列。我们将在输出中按所需的方式命名每个字段。我们对第二个查询执行相同的操作,并添加一个
union all
他们之间的条款。然后,我们根据created date(这是第一个查询中的别名)应用排序,以获得所需顺序的结果。如果你使用
union
,将从结果中消除重复行。如果你使用union all
,将保留重复行(如果存在)。union all
速度更快,因为它结合了两个数据集(只要查询中的列相同)。union
此外,还必须查找重复行并将其从查询中删除。mhd8tkvw2#
你没有提到你正在使用的mysql版本,所以我假设它是一个现代版本(MySQL8.x)。可以在每个子集上使用
ROW_NUMBER()
然后是平原UNION ALL
会成功的。我不明白你要的确切顺序,第四栏是什么
article_id (because it is referenced in articlecomments table)
手段。如果你详细说明,我可以相应地调整这个答案。生成所需结果集的查询是:
结果:
请参见db<>fiddle中的运行示例。
34gzjxbg3#
你可以这样交叉连接=