How to combine these MySQL queries and then access the data correctly

Question

I am building a nested comment reply system within my app.

Everything currently works as intended however I have found myself having to use several MySQL queries in order to retrieve the data required. And even worse the query for the 'replies' is within a foreach loop. Meaning that although it performs admirably for now, it is far from optimal and will cause problems as the dataset grows.

Therefore I wish to solve this before I go any deeper into development.

Since the tables for the app share the same as the wordpress blog for the site I am using the wordpress shorthand for the queries.

The current way the page is generated is as follows:

A comments table is queried and all results relating to a projectid are retrieved:-

    $commentquery = "select projects_comments.*, users.user_url, users.display_name
                 from ".$wpdb->prefix."projects_comments projects_comments
                 left join ".$wpdb->prefix."users users on users.ID=projects_comments.userid
                where projectid = '$projectid'
                order by projects_comments.commentid desc
                ";  

$comments = $wpdb->get_results($commentquery);

I then perform a foreach loop as below:-

   if($comments) {
            foreach ( $comments as $c ) 
                { 


                $replyquery = "select project_replies.*, users.user_url, users.display_name

                 from ".$wpdb->prefix."project_replies project_replies
                 left join ".$wpdb->prefix."users users on users.ID=project_replies.uid
                where project_replies.cid = '$c->commentid' 
                order by project_replies.id desc        
                limit 2         
                ";      

                $replies = $wpdb->get_results($replyquery);

                asort($replies);

                $countquery = "select count(*)

                    from ".$wpdb->prefix."project_replies project_replies
                    where project_replies.cid='".$c->commentid."'
                    ";

                $replycount = $wpdb->get_var($countquery);  

    //generate html here
   }
}

Inside this loop is two further queries. The first gets the replies for each comment but limits the results to 2 (I wish to do this in order to have a "see all replies" button which then queries the DB for the rest if the user requires them), the second query counts the total number of replies.

The html is then also generated for each reply within the loop using a second nested foreach within the above loop (where it says generate html code here) as below:-

if ($replies){

    foreach ( $replies as $r ){

     // generate each reply
    }
}

All data is pulled from these arrays in the following manner:

$c->userid, $c->body etc... For the comments
$r->userid, $r->body etc... For the replies.

I wish to keep this format if at all possible.

So as stated at the beginning of the question, this all works perfectly however I am aware that by nesting the reply and count queries I am performing many more queries than necessary. 100 comments will generate 100 replies queries and 100 count replies queries etc.

Thanks to some helpful people on this site I have considered using a join to get all the original data in one go for the comments and replies. Like so...

$commentquery2 = "SELECT c.commentid, c.userid, c.body as cbody, c.projectid, c.posttime, cu.user_url AS cu_url, cu.display_name AS cu_name,
                    r.*, ru.user_url AS ru_url, ru.display_name AS ru_name
                FROM ".$wpdb->prefix."projects_comments AS c
                LEFT JOIN ".$wpdb->prefix."users        AS cu ON cu.ID = c.userid
                LEFT JOIN ".$wpdb->prefix."project_replies   AS r  ON r.cid = c.commentid 
                LEFT JOIN ".$wpdb->prefix."users        AS ru ON ru.ID = r.uid
                WHERE c.projectid = $projectid
                ORDER BY c.commentid DESC, r.id DESC";   

Although this does indeed work (and was enough for me to mark that question as answered), when putting it into practice I have had several difficulties.

Firstly this retrieves all data as separate rows meaning that if I have 5 comments each with 3 replies, I actually get 15 rows returned and not a nested data object with the replies being nested within each comment row.

To tackle this I have tried some array manipulation as shown:

$old_id=NULL;
$comments=array();

foreach($getcomments as $c){

    if($c->commentid !== $old_id){

        $comments[$old_id] = $c;
        $old_id = $c->commentid;

    }


    $comments[$old_id]['replies'][] = $c;


} 

Doing this gives me a nested data object as required. However it does not include the reply counting query and it does not limit each set of replies to 2 as intended, it retrieves all of them.

And finally with my current html generation code within the foreach loops:

foreach($comments){

    //generate comment html

    foreach($replies) {

         //generate replies html

    }

}

I cannot seem to get it to work correctly with the nested data object. Accessing the correct deep replies seems to have me baffled.

So to summarise, I wish to be able to remove the looped queries, combine them into one large and more efficient query or at worst in to a data query and a separate counting query, then create a neatly nested data object, with the comments as rows and any replies nested within under the heading 'replies'

Then I need to be able to iterate through these correctly within my php code in order to generate the required html.

I apologise for the length of this question and realise it may well put many of you off answering, But I have been battling with this for 19 hours straight now and really need the help.

Many thanks to anyone who offers any suggestions.

Answer 1

If you like to keep the comments and replies in two different tables (see crafter's comment), you can remove the loop with a simple trick: Collect the comment IDs from the first query and use a

WHERE (cid IN (1,2,3,4,...))

instead of the loop. If you need to limit the replies per comment, that should be possible with additional WHERE- or HAVING clauses.

BurninLeo