php - 如何使用 join 子句仅检查行是否存在？

Question

我有两个这样的表:

// Posts
+----+---------+-----------------+----------+
| id |  title  |    content      |  amount  |
+----+---------+-----------------+----------+
| 1  | title1  | content1        | NULL     |
| 2  | title2  | content2        | 1000     |
| 3  | title3  | content3        | 5000     |
| 4  | title4  | content4        | NULL     |
| 5  | title5  | content5        | 2000     |
+----+---------+-----------------+----------+
//                                 ^ NULL means that question is free


// Money_Paid
+----+---------+---------+
| id | user_id | post_id |
+----+---------+---------+
| 1  | 123     | 2       |
| 2  | 345     | 5       |
| 3  | 123     | 5       |
+----+---------+---------+

我正在尝试做的事情:我的一些帖子不是免费的，任何想要看到它们的人都应该支付费用。现在我需要一个查询来检查当前用户是否支付了该帖子的费用？ (仅适用于非免费帖子)

SELECT * FROM Posts p WHERE id = :post_id
LEFT JOIN Money_Paid mp ON p.amount IS NOT NULL AND ...

<小时/>

以下是一些示例:(基于当前表格)

$stm->bindValue(":post_id", 2, PDO::PARAM_INT);
$stm->bindValue(":user_id", 123, PDO::PARAM_INT);
//=> TRUE (the user of '123' can see this post because he paid post's cost)

$stm->bindValue(":post_id", 2, PDO::PARAM_INT);
$stm->bindValue(":user_id", 345, PDO::PARAM_INT);
//=> FALSE (the user of '345' cannot see this post because he didn't pay post's cost)

$stm->bindValue(":post_id", 5, PDO::PARAM_INT);
$stm->bindValue(":user_id", 345, PDO::PARAM_INT);
//=> TRUE

<小时/>

我想要的查询:我想要一列包含 true或false (仅适用于非免费帖子)告诉我当前用户是否支付了当前帖子的费用。换句话说:

Money_paid中有一行表其中user_id = :user_id => true

没有任何行 -> false

Answer 1

不能 100% 确定我理解您想要的结果，但这里有一种添加新列的方法，该列可以根据用户是否已付款返回 true 或 false:

select *, 
    case when mp.id is not null then 'true' else 'false' end paid
from Posts p 
   left join Money_Paid mp ON mp.post_id = p.id and mp.user_id = :user_id
where p.id = :post_id