Comment combiner deux requêtes SQL dans MySQL avec des colonnes différentes sans combiner leurs lignes résultantes
Le contexte
J'essaie de créer un système de «flux» sur mon site Web où les utilisateurs peuvent accéder à leur flux et voir toutes leurs nouvelles notifications à travers les différentes choses qu'ils peuvent faire sur le site. Par exemple, dans la section "flux", les utilisateurs peuvent voir si les utilisateurs qu'ils suivent ont créé des articles et si les utilisateurs ont commenté des articles. Mon système d'alimentation actuel utilise simplement deux requêtes distinctes pour obtenir ces informations. Cependant, je souhaite combiner ces deux requêtes en une seule afin que l'utilisateur puisse voir l'activité de ceux qu'il suit chronologiquement. La façon dont mon système fonctionne maintenant, je reçois cinq articles de chaque personne que l'utilisateur suit et je les mets dans la section "flux", puis je reçois cinq commentaires d'article et je les publie dans la même zone dans la section "flux". Au lieu que les requêtes soient séparées, je souhaite les combiner de sorte que,
Question
Tout d'abord, laissez-moi vous montrer mon code pour la création de table si vous souhaitez recréer cela. La première chose à faire est de créer une users
table, à laquelle my articles
et articlecomments
tables font référence:
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;
Ensuite, créons la articles
table:
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;
Enfin, nous avons besoin du articlecomments
tableau:
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;
Pour remplir suffisamment les tables pour cet exemple, nous utiliserons ces instructions:
INSERT INTO users (uidUsers, emailUsers, pwdUsers, created) VALUES ('genericUser', '[email protected]', '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');
Les deux requêtes que j'utilise pour obtenir des données à partir des tables articles
et articlecomments
sont ci-dessous:
Requête 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
Requête 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
Comment combiner ces deux requêtes contenant des informations et des colonnes différentes afin qu'elles soient classées en fonction de la date de création ( articles.date
et articlecomments.date
, respectivement)? Je veux qu'ils soient sur des lignes séparées, pas sur la même ligne. Donc, ce devrait être comme si je les ai interrogés séparément et j'ai simplement combiné les lignes résultantes ensemble. S'il y a trois articles et trois commentaires d'article, je veux qu'il y ait six lignes renvoyées au total.
Voici à quoi je veux que cela ressemble. Étant donné qu'il y a trois articles et trois commentaires d'article, et que les commentaires ont été créés après les articles, voici à quoi devrait ressembler le résultat après avoir combiné les requêtes ci-dessus (je ne sais pas si cette représentation est possible étant donné les différents noms de colonnes, mais je je me demande si quelque chose de similaire pourrait être accompli):
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
| 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 |
+-------------------------------+-------------------+---------------------+----------------------------------------------------------------+---------+-------------+
Les choses que j'ai essayées
J'ai lu que cela pourrait impliquer JOIN
ou des UNION
opérateurs, mais je ne sais pas comment les implémenter dans cette situation. J'ai essayé de combiner les deux requêtes en utilisant simplement (Query 1) UNION (Query 2)
, ce qui m'a d'abord indiqué que le nombre de colonnes était différent dans mes deux requêtes, j'ai donc dû supprimer la idUsers
colonne de ma articlecomments
requête. Cela m'a en fait un peu rapproché, mais ce n'était pas formaté correctement:
+------------+-------------------+---------------------+---------+-------------+
| 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 |
+------------+-------------------+---------------------+---------+-------------+
Des idées? Faites-moi savoir s'il y a une confusion. Merci.
Server type: MariaDB
Server version: 10.4.8-MariaDB - mariadb.org binary distribution
Cela ressemble à MySQL. Vous pouvez faire quelque chose comme ceci:
select * from (SELECT articles.article_id as id_article_comment, articles.title as title_message, articles.date as created, 'article' AS contenttype, articles.article_id as article_id, 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) a
union all
select * from (SELECT articlecomments.comment_id, articlecomments.message, articlecomments.date, 'article comment' AS contenttype, articlecomments.article_id, articlecomments.idUsers, users.uidUsers FROM articlecomments JOIN users ON articlecomments.idUsers = users.idUsers WHERE articlecomments.idUsers = '1' ORDER BY articlecomments.date DESC LIMIT 5) b
order by created DESC
Voir l'exemple ici: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=26280a9c1c5f62fc33d00d93ab84adf3
Résultat comme ceci:
id_article_comment | title_message | créé | article_id | uidUsers -----------------: | : ------------- | : ------------------ | ---------: | : ---------- 1 | premier article | 09/07/2020 05:59:18 | 1 | genericUser 2 | deuxième article | 09/07/2020 05:59:18 | 1 | genericUser 3 | troisième article | 09/07/2020 05:59:18 | 1 | genericUser 1 | premier message | 09/07/2020 05:59:18 | 1 | genericUser 2 | deuxième message | 09/07/2020 05:59:18 | 1 | genericUser 3 | troisième message | 09/07/2020 05:59:18 | 1 | genericUser
Explication
Puisque nous voulons utiliser order by
et limit
, nous allons créer une sous-requête à partir de la première ligne et sélectionner toutes les colonnes de cette première sous-requête. Nous nommerons chaque champ comme nous le voulons dans la sortie.
Nous faisons la même chose avec la 2ème requête et ajoutons une union all
clause entre elles. Ensuite, nous appliquons la commande en fonction de la date de création (qui était un alias dans la première requête) pour obtenir les résultats souhaités dans l'ordre souhaité.
Si vous utilisez union
, les lignes en double seront éliminées du résultat. Si vous utilisez union all
, les lignes en double - si elles existent - seront conservées. union all
est plus rapide car il combine 2 ensembles de données (tant que les colonnes sont identiques dans les requêtes. union
doit, en outre, rechercher les lignes en double et les supprimer de la requête.