testingRequests.sql

-- Requêtes les plus pertinentes et fréquentes à réaliser 
-- sur les données de l'application.
-- Ce script permet de les tester sur les données initiales

-- Obtenir le pseudo du compte qui a créé un fil (ici : id 2)
\echo 1) Le pseudo du compte qui a créé le thread à l id 2 : 
SELECT username FROM userAccount INNER JOIN thread 
ON userAccount.userid = thread.userid_thread 
WHERE thread.userid_thread = 2;

-- Obtenir tous les fils créés par un utilisateur (ici : titi)
\echo  2)Les threads créés par titi :
SELECT threadid, threadname FROM thread AS t INNER JOIN userAccount AS u
ON t.userid_thread = u.userid 
WHERE u.username = 'titi';

-- Voir les messages ayant reçu une réaction d'un utilisateur (ici : titi)
\echo 3) Messages auxquels titi a réagi :
SELECT m.msgid, u.username, m.msg, r.reaction FROM msg AS m 
INNER JOIN reactions AS r 
ON m.userid_msg = r.userid_reactions
INNER JOIN userAccount AS u
ON r.userid_reactions = u.userid
WHERE u.username = 'titi' AND r.reaction = true;

-- Voir les threads suivis par un utilisateur (ici tata) 
\echo 4) Threads suivis par tata :

SELECT threadid, threadname FROM thread AS t INNER JOIN userAccount AS u 
ON t.userid_thread = u.userid 
WHERE u.username = 'tata';

-- Voir les threads suivis par un utilisateur (ici titi) dans l'ordre ascendant
\echo 5) Threads suivis par titi dans l ordre croissant

SELECT threadid_follow, userID_follow, followDate FROM follow as f
INNER JOIN thread AS t ON t.threadid = f.threadID_follow
INNER JOIN userAccount AS u ON u.userid = f.userID_follow
WHERE u.username = 'titi'
ORDER BY f.followDate ASC;

-- Voir tous les messages d'un fil (ici : id 1)

\echo 6) Tous les messages du fil à id 1 et leur expéditeur
SELECT m.threadID, m.msg, u.username FROM msg AS m 
INNER JOIN userAccount AS U 
ON m.userid_msg = u.userid 
WHERE m.threadID = 1;

-- Voir le thread qui a le plus de messages

\echo 7) Le thread qui a le plus de messages

SELECT t.threadid, t.threadName, MAX(m.msgid) 
FROM msg AS m INNER JOIN thread AS t 
ON t.threadid = m.threadid
GROUP BY t.threadID
LIMIT 1;

-- Voir le thread qui a le plus d'abonnés

\echo 8) Le thread qui a le plus d abonnés 

SELECT t.threadID, t.threadName, COUNT(f.userID_follow) AS nb_followers
FROM follow f
INNER JOIN thread t ON f.threadID_follow = t.threadID
GROUP BY t.threadID, t.threadName
ORDER BY nb_followers DESC
LIMIT 1;