我怎样才能在加入的路上加上条件？ [英] How can I put a condition on the way of join?
问题描述
我有这个表结构：
// 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 表示这是一个问题而 1 表示这是一个答案。
type column: 0 means it is a question and 1 means it is a answer.
相关 column：对于问题，此列包含其自身的ID，对于答案，此列包含其问题的ID。
related column: for question this column is containing the id of itself and for answer this column is containing the id of its question.
还有其他依赖表：
// 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). Answers can never be favorite (just questions can be)
这里也是我的查询：
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
注意：对于 cuf ， 2 表示当前用户已将此问题标记为收藏， 3 表示他没有（在其他单词， 3 表示此问题不是当前用户的最爱）。
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).
好的，让我传递一些参数来查询并执行它：（as例如）
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();
这是输出：
-- 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 |
+--------------+----------------------+------+-----+-------------+-----------------+
好的，我的问题是什么？
这两列 cuf 和 total_favorites 只属于问题（ type = 0 ）。但我的查询不知道。我的意思是我的查询计算所有行的总收藏数，我想知道，怎么知道呢：计算 cuf 和 total_favorites 仅用于问题，而不是问题和答案？
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?
换句话说，我需要输入 IF 条件检查 p.type = 0 然后执行以下两行：
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,
和
LEFT JOIN favorites ff ON p.id = ff.post_id AND f.user_id = :user_id_2
否则不会执行这两行，因为如果 p.type = 1 ，那么这两行就是浪费而无用。
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?
推荐答案
您可能想要尝试的一种方法是在子查询中仅查询一次收藏夹和投票表，并计算bo用户和所有值一次。
One way you may want to try is to query the favorite and votes table only once in subqueries, and calculate both the user and all values at once.
SELECT
q.title, q.content,
IFNULL(vv.user_val, 0) cuvv, IFNULL(vv.all_val, 0) total_votes,
IFNULL(ff.user_fav, 0) cuf, IFNULL(ff.all_fav, 0) total_favorites
FROM QandA q
LEFT JOIN (
SELECT post_id,
SUM(value) all_val, SUM(CASE WHEN user_id=1234 THEN value END) user_val
FROM votes GROUP BY post_id
) vv
ON vv.post_id = q.id
LEFT JOIN (
SELECT post_id,
COUNT(1) all_fav, COUNT(CASE WHEN user_id=1234 THEN 1 END) user_fav
FROM favorites GROUP BY post_id
) ff
ON q.type=0 AND ff.post_id = q.id
WHERE q.related = 1;
这篇关于我怎样才能在加入的路上加上条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
