dimanche 27 mars 2016

How can I put a condition on the way of join?

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