selecting the latest mysql comments and posts from a single table with a parent and child comments
( SELECT n.* , u.username, u.picture
FROM post n
LEFT JOIN user u ON u.id=n.wuserid
WHERE n.userId=22 and n.parentid=0
ORDER BY n.timeevent DESC
LIMIT 5
)
UNION all
(SELECT n.* , u.username, u.picture
FROM post n
LEFT JOIN user u ON u.id=n.wuserid
WHERE n.userId=22 and n.parentid<>0 AND
parentid IN ( SELECT n.id FROM post n
WHERE n.userId=22 and n.parentid=0 LIMIT 5
)
ORDER BY n.timeevent DESC
)
parentid IN ( SELECT n.id FROM post n
WHERE n.userId=22 and n.parentid=0 LIMIT 5
)
mysql complains that you can't do LIMIT in a subquery... but I specifically need to select the latest sub-comments
how can this be done?
replace the subquery with a JOIN
(
SELECT n.* , u.username, u.picture
FROM post n
LEFT JOIN user u ON u.id=n.wuserid
WHERE n.userId=? and n.parentid=0 $EvP $EvL
ORDER BY n.timeevent DESC
LIMIT ?,?
)
UNION all
(
SELECT n.* , u.username, u.picture
FROM post n
LEFT JOIN user u ON u.id=n.wuserid
JOIN
(
SELECT n1.id FROM post n1
WHERE n1.userId=?i and n1.parentid=0 and n1.id >? and n1.id
ORDER BY n1.timeevent DESC
LIMIT ?i,?i
) pid
ON parentid= pid.id
thank you very much for the answer
Comments