>Schema: http://pastebin.com/jU3HmmNM
$mainfetch = "SELECT * FROM status ORDER BY status_id DESC LIMIT 10,0";
while($mainfetch -> $result) {
$subquery = SELECT COUNT(*) FROM status s, likes l WHERE s.status_id = l.status_id AND l.member_id = 1;
$liked = $query->row();
if($liked > 0) {
//liked
}else {
//not liked
}
}
所以这不是最佳的,因为$subquery每次都会执行 10 次,我希望在$mainfetch中添加$subquery,我怎样才能将它们连接起来?
你只需要在s.status_id = l.status_id
上添加一个左连接...然后你也得到l.status_id
:如果它是空的,那么成员 1 不喜欢它,否则当然是的。
使用您提供的架构:
SELECT s.status_id, s.text_id, count(l.status_id) as like_count
FROM status AS s LEFT JOIN likes AS l ON (s.status_id = l.status_id AND l.member_id = 1)
group by s.status_id, s.text_id
ORDER BY status_id DESC
LIMIT 10,0