CODEWITHSUNDEEP
mysql
Darwin Luague
Darwin Luague

Reputation: 173

Social Networking posts comment algorithm for mysql

$query = $this->db->query("SELECT a.fullname AS fullname_post , a.picture, a.user_id, c.post_id, c.date_posted, c.total_comments, c.content AS post, UNIX_TIMESTAMP() - c.date_posted AS p_time_spent, c.photo, d.comment_id, d.comment_date, d.content AS comments, UNIX_TIMESTAMP() - d.comment_date AS c_time_spent, d.post_id_fk, e.fullname AS fullname_comments 
     from $post_table c
     LEFT JOIN $comments_table d ON d.post_id_fk = c.post_id
     LEFT JOIN $user_table a on a.user_id=c.post_owner_fk
     LEFT JOIN $user_table e on e.user_id=d.comment_owner_id_fk
     LEFT JOIN $contact_table f on f.user_id_fk='$user_id' OR f.user_id_fk2='$user_id'
     WHERE c.post_owner_fk = '$user_id' OR c.post_owner_fk = f.friend_id_fk OR c.post_owner_fk = f.friend_id_fk2
     ORDER BY c.post_id DESC, d.comment_id ASC"
    );

The mysql query above works fine if I just want to retrieve all comments to a specific post but I have no idea how to limit the number of comments to display.

I try to put select in one of the left join and put some limit.

LEFT JOIN (SELECT * FROM $comments_table LIMIT 4) d ON d.post_id_fk = c.post_id

but it only display 4 comments and the other posts has no comments displayed even if there are comments in the database. I think it only retrieve 4 comments in the comment table.

So please any idea how to solve this problem thanks!

Upvotes: 0

Views: 214

Answers (1)

nvuono
nvuono

Reputation: 3363

Can you try something like this in MySQL?

SELECT a.fullname AS fullname_post , a.picture, a.user_id, c.post_id, c.date_posted,
   c.total_comments, c.content AS post, UNIX_TIMESTAMP() - c.date_posted AS p_time_spent,
   c.photo, d.comment_id, d.comment_date, d.content AS comments, 
   UNIX_TIMESTAMP() - d.comment_date AS c_time_spent, d.post_id_fk, 
   e.fullname AS fullname_comments  
     FROM $post_table c 
     LEFT JOIN $comments_table d ON d.post_id_fk = c.post_id 
      LEFT JOIN
           (SELECT tmpc.post_id_fk 
                 FROM $comments_table tmpc 
                 WHERE tmpc.post_id_fk = c.post_id 
                 ORDER BY tmpc.comment_date DESC
                 LIMIT 4) as climited
            ON climited.post_id_fk = c.post_id                 
     LEFT JOIN $user_table a on a.user_id=c.post_owner_fk 
     LEFT JOIN $user_table e on e.user_id=d.comment_owner_id_fk 
     LEFT JOIN $contact_table f on f.user_id_fk='$user_id' 
               OR f.user_id_fk2='$user_id' 
     WHERE c.post_owner_fk = '$user_id' 
           OR c.post_owner_fk = f.friend_id_fk 
           OR c.post_owner_fk = f.friend_id_fk2 
     ORDER BY c.post_id DESC, d.comment_id ASC

Edit: Replaced the IN clause with another join to a 'limited' SELECT query

Upvotes: 0

Related Questions