I have this table structure:
// QandA
+----+---------------------+----------------------------------------+------+---------+
| Id | title | content | type | related |
+----+---------------------+----------------------------------------+------+---------+
| 1 | title of question 1 | content of question1 | 0 | 1 |
| 2 | | content of first answer for question1 | 1 | 1 |
| 3 | title of question 2 | content of question2 | 0 | 3 |
| 4 | | content of second answer for question1 | 1 | 1 |
| 5 | | content of first answer for question2 | 1 | 3 |
+----+---------------------+----------------------------------------+------+---------+
type
column: 0
means it is a question and 1
means it is a answer.
related
column: for question this column is containing the id of itself and for answer this column is containing the id of its question.
Also there is three other tables:
// Votes
+----+---------+---------+-------+
| id | post_id | user_id | value |
+----+---------+---------+-------+
| 1 | 1 | 1234 | 1 |
| 2 | 2 | 1234 | -1 |
| 3 | 1 | 4321 | 1 |
+----+---------+---------+-------+
// Favorites
+----+---------+---------+
| id | post_id | user_id |
+----+---------+---------+
| 1 | 1 | 1234 |
| 2 | 1 | 4321 |
+----+---------+---------+
Ok well, This is the main note in my question: Favorites
table is only belong to the questions (not answers).
Also here is my query:
SELECT
p.title, p.content,
vv.value AS cuvv -- cuvv is stand for current_user_vote_value,
CASE WHEN ff.id IS NOT NULL THEN '2' ELSE '3' END AS cuf -- current_user_favorite
(SELECT SUM(v.value) FROM Votes v WHERE p.id = v.post_id) AS total_votes,
(SELECT COUNT(1) FROM Favorites f WHERE p.id = f.post_id) AS total_favorites,
FROM QandA p
LEFT JOIN Votes vv ON p.id = vv.post_id AND vv.user_id = :user_id_1
LEFT JOIN favorites ff ON p.id = ff.post_id AND f.user_id = :user_id_2
WHERE p.related = :id
Note: For cuf
, 2
means current user has marked this question as favorite and 3
means he didn't have (in other word, 3
means this question isn't favorite for current user).
Ok, let me pass some parameters to query and execute it: (as an example)
$user_id = 1234;
$id = 1;
$sth->bindValue(":user_id_1", $user_id, PDO::PARAM_INT);
$sth->bindValue(":user_id_2", $user_id, PDO::PARAM_INT);
$sth->bindValue(":id", $id, PDO::PARAM_INT);
$sth->execute();
And here is the output:
-- cuvv is stand for current_user_vote_value
-- cuf is stand for current_user_favorite
+--------------+----------------------+------+-----+-------------+-----------------+
| title | content | cuvv | cuf | total_votes | total_favorites |
+--------------+----------------------+------+-----+-------------+-----------------+
| title of ... | content of que ... | 1 | 2 | 2 | 2 |
| | content of fir ... | -1 | 3 | -1 | 0 |
| | content of sec ... | NULL | 3 | 0 | 0 |
+--------------+----------------------+------+-----+-------------+-----------------+
Ok So, What's my question?
These two columns cuf
and total_favorites
are just belong to questions (type = 0
). But my query doesn't know it. I mean my query calculates the number of total favorites for all rows, and I want to know, how can tell it: calculate cuf
and total_favorites
only for questions, not both questions and answers?
In other word, I need to put a IF
condition to check if p.type = 0
then execute these two lines:
(SELECT COUNT(1) FROM Favorites f WHERE p.id = f.post_id) AS total_favorites,
and
LEFT JOIN favorites ff ON p.id = ff.post_id AND f.user_id = :user_id_2
Otherwise doesn't execute those two lines, because if p.type = 1
, then those two lines are waste and useless.
How can I implement that condition and improve that query?
Aucun commentaire:
Enregistrer un commentaire