Des fois je m'énerve devant mon pc xD. Je suis dsl, je vous pompe votre temps (@Benzouye)
J'ai deux requête... enfin une seul avec un union.
Ca donne ca :
$select = '
posts.post,
posts.id,
posts.embed,
posts.property,
posts.link,
posts.ranked,
posts.elo,
posts.subcategory,
posts.country,
COUNT(DISTINCT calculation.id) AS rank,
COUNT(DISTINCT calculation2.id) AS total,
members.id AS idMember,
members.username,
members.avatar,
members.property AS avatarProperty,
memberReposted.username AS usernameReposter,
memberReposted.id AS idReposter, ';
$friend = $this->areFriends($idSession, $idProfil);
if ($idSession == $idProfil || ((isset($friend['status']) && $friend['status'] == 1))) {
//session and target are the same or are friends so display all post.
$req = $this->dao->prepare('
SELECT ' . $select . '
DATE_FORMAT(posts.date, "%d/%m/%y %H:%i") AS date
FROM
members, posts
LEFT JOIN posts AS calculation
ON calculation.ranked="1"
AND calculation.elo > posts.elo
AND calculation.subcategory = posts.subcategory
AND calculation.country=posts.country
LEFT JOIN posts AS calculation2
ON calculation2.subcategory = posts.subcategory
AND calculation2.country = posts.country
AND calculation2.ranked="1"
LEFT JOIN posts_repost AS repost
ON repost.id_member IS NULL
LEFT JOIN members AS memberReposted
ON memberReposted.id IS NULL
WHERE
posts.id_member=:idProfil
AND members.id=posts.id_member
UNION
SELECT ' . $select . '
DATE_FORMAT(posts_repost.date, "%d/%m/%y %H:%i") AS date
FROM
members, posts_repost, members AS memberReposted, posts
LEFT JOIN posts AS calculation
ON calculation.ranked="1"
AND calculation.elo > posts.elo
AND calculation.subcategory = posts.subcategory
AND calculation.country=posts.country
LEFT JOIN posts AS calculation2
ON calculation2.subcategory = posts.subcategory
AND calculation2.country = posts.country
AND calculation2.ranked="1"
WHERE
posts.id=posts_repost.id_post
AND posts_repost.id_member = :idProfil
AND memberReposted.id=posts_repost.id_member
AND members.id=posts.id_member
GROUP BY posts.id
ORDER BY date DESC LIMIT 20');
----
EDIT :
Group by doit être placé 2 fois. Il dépend des unions. Chaque union doit avoir son propre group by !
Par contre je suis bloqué sur order by... Sa me les ordonne bien mais sur 2 paquets.
Je réécris les jointures externes de la première partie (post_repost et members) car cela ne devais pas fonctionner ...
SELECT
P.post,
P.id,
P.embed,
P.property,
P.link,
P.ranked,
P.elo,
P.subcategory,
P.country,
COUNT(DISTINCT C.id) AS rank,
COUNT(DISTINCT C2.id) AS total,
M.id AS idMember,
M.username,
M.avatar,
M.property AS avatarProperty,
MR.username AS usernameReposter,
MR.id AS idReposter,
DATE_FORMAT(P.date, "%d/%m/%y %H:%i") AS date
FROM
members M
INNER JOIN posts P
ON M.id = P.id_member
LEFT JOIN posts AS C
ON C.ranked="1"
AND C.elo > P.elo
AND C.subcategory = P.subcategory
AND C.country=P.country
LEFT JOIN posts AS C2
ON C2.subcategory = P.subcategory
AND C2.country = P.country
AND C2.ranked="1"
LEFT JOIN posts_repost AS PR
ON P.id = PR.id_post
LEFT JOIN members AS MR
ON MR.id=PR.id_member
WHERE
P.id_member=:idProfil
AND PR.id_member IS NULL
AND MR.id IS NULL
GROUP BY P.id
UNION
SELECT
P.post,
P.id,
P.embed,
P.property,
P.link,
P.ranked,
P.elo,
P.subcategory,
P.country,
COUNT(DISTINCT C.id) AS rank,
COUNT(DISTINCT C2.id) AS total,
M.id AS idMember,
M.username,
M.avatar,
M.property AS avatarProperty,
MR.username AS usernameReposter,
MR.id AS idReposter,
DATE_FORMAT( PR.date, "%d/%m/%y %H:%i" ) AS date
FROM
members M
INNER JOIN posts P
ON M.id = P.id_member
INNER JOIN posts_repost PR
ON P.id = PR.id_post
INNER JOIN members MR
ON MR.id=PR.id_member
LEFT JOIN posts AS C
ON C.ranked="1"
AND C.elo > P.elo
AND C.subcategory = P.subcategory
AND C.country=P.country
LEFT JOIN posts AS C2
ON C2.subcategory = P.subcategory
AND C2.country = P.country
AND C2.ranked="1"
WHERE
PR.id_member = :idProfil
GROUP BY P.id
ORDER BY date DESC
LIMIT 20
Après, je ne comprends pas ta dernière remarque :
Adrien Supra a écrit:
je suis bloqué sur order by... Sa me les ordonne bien mais sur 2 paquets
Le ORDER BY s'applique à l'UNION complète, que souhaites-tu obtenir ? Peux-tu donner un exemple ?
J'ai le même soucis ici que sur ce que tu as corrigé. Pourtant j'ai bien appliqué ta technique. Le tri ne fonctionne pas
/**
* recovery post for index (session posts, friends posts, and followed public posts)
* @param $idSession is the id of the session
*/
public function getIndexPosts($idSession)
{
$select = '
posts.post,
posts.id,
posts.embed,
posts.property,
posts.link,
posts.ranked,
posts.elo,
posts.subcategory,
posts.country,
COUNT(DISTINCT calculation.id) AS rank,
COUNT(DISTINCT calculation2.id) AS total,
members.id AS idMember,
members.username,
members.avatar,
members.property AS avatarProperty,
memberReposter.username AS usernameReposter,
memberReposter.id AS idReposter, ';
//we get all friends posts
$req = $this->dao->prepare('
SELECT ' . $select . '
DATE_FORMAT(posts.date, "%d/%m/%y %H:%i") AS date
FROM
members, posts
LEFT JOIN posts AS calculation
ON calculation.ranked="1"
AND calculation.elo > posts.elo
AND calculation.subcategory = posts.subcategory
AND calculation.country=posts.country
LEFT JOIN posts AS calculation2
ON calculation2.subcategory = posts.subcategory
AND calculation2.country = posts.country
AND calculation2.ranked="1"
LEFT JOIN posts_repost AS repost
ON repost.id_post = posts.id
LEFT JOIN members AS memberReposter
ON memberReposter.id=repost.id_member
WHERE
posts.id_member IN
(SELECT id_applicant FROM friends WHERE id_recipient=:idSession AND status="1"
UNION
SELECT id_recipient FROM friends WHERE id_applicant=:idSession AND status="1"
UNION
SELECT id FROM members WHERE id=:idSession
UNION
SELECT id_followed FROM followers WHERE id_follower=:idSession)
AND members.id=posts.id_member
AND memberReposter.id IS NULL
AND repost.id_post IS NULL
GROUP BY posts.id
UNION
SELECT ' . $select . '
DATE_FORMAT(repost.date, "%d/%m/%y %H:%i") AS date
FROM
members, posts_repost AS repost, members AS memberReposter, posts
LEFT JOIN posts AS calculation
ON calculation.ranked="1"
AND calculation.elo > posts.elo
AND calculation.subcategory = posts.subcategory
AND calculation.country=posts.country
LEFT JOIN posts AS calculation2
ON calculation2.subcategory = posts.subcategory
AND calculation2.country = posts.country
AND calculation2.ranked="1"
WHERE
repost.id_member IN (
SELECT id_applicant FROM friends WHERE id_recipient=:idSession AND status="1"
UNION
SELECT id_recipient FROM friends WHERE id_applicant=:idSession AND status="1"
UNION
SELECT id FROM members WHERE id=:idSession
UNION
SELECT id_followed FROM followers WHERE id_follower=:idSession
)
AND memberReposter.id=repost.id_member
AND members.id=posts.id_member
AND posts.id=repost.id_post
GROUP BY posts.id
ORDER BY date DESC LIMIT 20
');
Tu vois l'erreur ?
EDIT : J'ai retiré le date_format et il semble que ca fonctionne. Pas l'ideal mais sa me va.
Mouai ... comparaison peut-être un peu trop flatteuse
Adrien Supra a écrit:
J'ai le même soucis ici que sur ce que tu as corrigé. Pourtant j'ai bien appliqué ta technique
Alors, je ne vois pas le rapport avec le DATE_FORMAT ...
Si je réécris "proprement" ta requête, j'obtiens :
SELECT
P.post,
P.id,
P.embed,
P.property,
P.link,
P.ranked,
P.elo,
P.subcategory,
P.country,
COUNT(DISTINCT C1.id) AS rank,
COUNT(DISTINCT C2.id) AS total,
M.id AS idMember,
M.username,
M.avatar,
M.property AS avatarProperty,
MR.username AS usernameReposter,
MR.id AS idReposter,
DATE_FORMAT(P.date, "%d/%m/%y %H:%i") AS date
FROM
posts AS P
INNER JOIN members AS M
ON P.id_member = M.id
LEFT JOIN posts AS C1
ON C1.ranked="1"
AND C1.elo > P.elo
AND C1.subcategory = P.subcategory
AND C1.country=P.country
LEFT JOIN posts AS C2
ON C2.subcategory = P.subcategory
AND C2.country = P.country
AND C2.ranked="1"
LEFT JOIN posts_repost AS PR
ON PR.id_post = P.id
LEFT JOIN members AS MR
ON MR.id = PR.id_member
WHERE
P.id_member IN (
SELECT id_applicant FROM friends WHERE id_recipient=:idSession AND status="1"
UNION
SELECT id_recipient FROM friends WHERE id_applicant=:idSession AND status="1"
UNION
SELECT id FROM members WHERE id=:idSession
UNION
SELECT id_followed FROM followers WHERE id_follower=:idSession
)
AND M.id=P.id_member
AND MR.id IS NULL
AND PR.id_post IS NULL
GROUP BY P.id
UNION
SELECT
P.post,
P.id,
P.embed,
P.property,
P.link,
P.ranked,
P.elo,
P.subcategory,
P.country,
COUNT(DISTINCT C1.id) AS rank,
COUNT(DISTINCT C2.id) AS total,
M.id AS idMember,
M.username,
M.avatar,
M.property AS avatarProperty,
MR.username AS usernameReposter,
MR.id AS idReposter,
DATE_FORMAT(PR.date, "%d/%m/%y %H:%i") AS date
FROM
posts
INNER JOIN members AS M
ON P.id_member = M.id
INNER JOIN posts_repost AS PR
ON PR.id_post = P.id
INNER JOIN members AS MR
ON MR.id = PR.id_member
LEFT JOIN posts AS C1
ON C1.ranked="1"
AND C1.elo > P.elo
AND C1.subcategory = P.subcategory
AND C1.country=P.country
LEFT JOIN posts AS C2
ON C2.subcategory = P.subcategory
AND C2.country = P.country
AND C2.ranked="1"
WHERE
PR.id_member IN (
SELECT id_applicant FROM friends WHERE id_recipient=:idSession AND status="1"
UNION
SELECT id_recipient FROM friends WHERE id_applicant=:idSession AND status="1"
UNION
SELECT id FROM members WHERE id=:idSession
UNION
SELECT id_followed FROM followers WHERE id_follower=:idSession
)
GROUP BY P.id
ORDER BY date DESC
LIMIT 20
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
"Etre vrai, peu le peuvent."
Friedrich Nietzsche
"Etre vrai, peu le peuvent."
Friedrich Nietzsche