I still do not find the right way to do it the way i want it. What I try to achieve is a comment post section that will show the 2 last reply of the post
my tables structure are like this
table post --------------------- post_id | comment | post_user | post_date post_reply --------------------- reply_id | parent_id | reply_user | reply_date
actually i am able to fetch all the post and all the reply but the problem that i face is i want to limit the number of post that i fetch and the number of reply for each post that i fetch and i also want to display the post if there is no reply then result should look like this
result ---------------------------------------------- post 1 reply post 1 reply post 1 post 2 post 3 reply post 3 reply post 3
of course I want to display this till I reach 10 post and the 2 reply max attach to each post with the less query possible of course. actually i am using a while that query for each post the 2 last reply but that use 11 query every time maybe you guys know a better way to do it in less query
I assume that your ID columns are of type INT. If not, you might have to use variables to count the replies per post, described here.
So try this
SELECT 'POST' AS type, p1.post_id, p1.comment, p1.post_user, p1.post_date, '-' AS reply_id
FROM post p1
LIMIT 10
UNION
SELECT 'REPLY' AS type, pr.parent_id AS post_id,
'-' AS comment, pr.reply_user AS post_user, pr.reply_date AS post_date, pr.reply_id
FROM post_reply pr
JOIN (
SELECT p2.post_id
FROM post p2
LIMIT 10
) AS tmpPost ON pr.parent_id = tmpPost.post_id
JOIN (
SELECT pr.reply_id, COUNT(*) AS row_number
FROM post_reply pr
JOIN post_reply pr2 ON pr.parent_id = pr2.parent_id AND pr.reply_id >= pr2.reply_id
GROUP BY pr.reply_id
) AS tmpPostRN ON tmpPostRN.reply_id = pr.reply_id
WHERE tmpPostRN.row_number <= 2
ORDER BY post_id ASC, type ASC, reply_id ASC
The Result will be something like
+-------+---------+-----------+-----------------+------------+----------+
| type | post_id | comment | post_user | post_date | reply_id |
+-------+---------+-----------+-----------------+------------+----------+
| POST | 1 | comment1 | user1 | 0000-00-00 | -1 |
| REPLY | 1 | - | post 1 reply1 | 0000-00-00 | 1 |
| REPLY | 1 | - | post 1 reply 2 | 0000-00-00 | 2 |
| POST | 2 | comment2 | user2 | 0000-00-00 | -1 |
| POST | 3 | comment3 | user3 | 0000-00-00 | -1 |
| REPLY | 3 | - | post 3 reply 1 | 0000-00-00 | 3 |
| REPLY | 3 | - | post 3 reply 2 | 0000-00-00 | 4 |
| POST | 4 | comment4 | user 4 | 0000-00-00 | -1 |
| POST | 5 | comment5 | user 5 | 0000-00-00 | -1 |
| POST | 6 | comment6 | user6 | 0000-00-00 | -1 |
| POST | 7 | comment7 | user7 | 0000-00-00 | -1 |
| POST | 8 | comment8 | user8 | 0000-00-00 | -1 |
| POST | 9 | comment9 | user9 | 0000-00-00 | -1 |
| POST | 10 | comment10 | user10 | 0000-00-00 | -1 |
| REPLY | 10 | - | post 10 reply 1 | 0000-00-00 | 5 |
| REPLY | 10 | - | post 10 reply 2 | 0000-00-00 | 6 |
+-------+---------+-----------+-----------------+------------+----------+
Instead of '-' AS comment in the second query you can insert your comment column in your post_reply table (provided that you have one). You can also set your reply_id for posts to a default value of your choice (rather than -1).