Bonjour.

J'ai :

      AND posts.id NOT IN (posts_votes.id_post_winner, posts_votes.id_post_loser)

Si posts_vote.id_post_winner et posts_votes.id_post_loser sont null, il ne me selectionnera jamais rien !!

Je suis obligé de feinter :

      AND posts.id NOT IN (posts_votes.id_post_winner, posts_votes.id_post_loser) OR "-1"

Ca me parait horrible... c'est normale ?

Bonjour,

Cela me paraît étrange que tu utilises une colonne dans le NOT IN, normalement on utilise ici des valeurs en dur ou le résultat d'une sous-requête ...

A mon avis il te manque une jointure quelque part ... ou du moins des conditions dans ta jointure externe avec la table posts_votes ...

Peux-tu poster la requête complète en question ?

-
Edité par Benzouye 22 septembre 2020 à 11:30:14

      $req = $this->dao->prepare('
      SELECT posts.post, posts.id, posts.embed, posts.property, posts.link, posts.ranked, posts.elo, posts.subcategory, posts.country, COUNT(DISTINCT calculation2.id) AS total, COUNT(DISTINCT calculation.id) AS rank, DATE_FORMAT(posts.date, "%d/%m/%y %H:%i") AS date, posts_votes.id_vote AS idVote, members.id AS idMember, members.username, members.avatar, members.property AS avatarProperty
      FROM 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_votes AS targeted ON targeted.id_post_winner=posts.id OR targeted.id_post_loser=posts.id
      INNER JOIN members ON members.id = posts.id_member 
      LEFT JOIN posts_votes ON posts_votes.id_member = members.id
      WHERE
      posts.id_member != :sessionId
      AND posts.ranked="1"
      AND posts.id NOT IN (posts_votes.id_post_winner, posts_votes.id_post_loser) OR "-1"
      AND posts.country=:country
      AND posts.category=:category
      AND posts.date > DATE_SUB(:actualDate, INTERVAL :interval DAY)
      GROUP BY posts.id 
      HAVING COUNT(targeted.id_vote) < 10
      ORDER BY posts.elo DESC LIMIT 10
      ');
      $req->execute(['category' => $category, 'country' => $country, 'sessionId' => $idSession, 'actualDate' => $date->format('Y-m-d H:i:s'), 'interval' => $interval]);
      $posts = $req->fetchAll();

C'est pour chopper des posts pour lesquel le membre n'a encore jamais voté et qui ont moins de 10 vote a leur actif.

Je vais t'aider un peu :

premier left join : nombre de posts mieux classé
deuxième left joint : nombre de post total dans la category
=> (left join 2/left join 1 = ratio perfermance)

troisième left join : les posts qui ont moins de 10 vote a leur actif
dernier left join : les posts pour lesquel le membre a déja voté

-
Edité par -Crixus- 22 septembre 2020 à 15:00:56

SELECT
	P.post,
	P.id,
	P.embed,
	P.property,
	P.link,
	P.ranked,
	P.elo,
	P.subcategory,
	P.country,
	COUNT(DISTINCT C2.id) AS total,
	COUNT(DISTINCT C1.id) AS rank,
	DATE_FORMAT(P.date, "%d/%m/%y %H:%i") AS date,
	PV.id_vote AS idVote,
	M.id AS idMember,
	M.username,
	M.avatar,
	M.property AS avatarProperty
FROM
	posts P
		INNER JOIN members AS M
			ON M.id = P.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"
		LEFT JOIN posts_votes AS T
			ON T.id_post_winner=P.id
			OR T.id_post_loser=P.id
		LEFT JOIN posts_votes PV
			ON PV.id_member = M.id
			AND (
				P.id = PV.id_post_winner
				OR P.id = PV.id_post_loser
			)
WHERE
	P.id_member != :sessionId
	AND P.ranked="1"
	AND P.country=:country
	AND P.category=:category
	AND P.date > DATE_SUB(:actualDate, INTERVAL :interval DAY)
	AND PV.id_member IS NULL
GROUP BY P.id
HAVING COUNT(T.id_vote) < 10
ORDER BY P.elo DESC LIMIT 10

        LEFT JOIN posts_votes PV
            ON PV.id_member = M.id
            AND (
                P.id = PV.id_post_winner
                OR P.id = PV.id_post_loser
            )

AND PV.id_member IS NULL