I've recently began a comments/replies section for an app.
My initial approach has been to query the database for the comments linked to a particular item as below:-
$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";
I then began a foreach
loop with the following query inside in order to retrieve the replies:
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";
}
}
In practice this is all working fine, however it strikes me that this is terribly inefficient and that there must be a way to retrieve all the data in one query. My concern is that with 100 comments on a page I will be performing 100 queries for the comments alone.
What is the best method to tackle this and how would I go about combining these queries to produce one large object/array with which to pull the data from.
Or alternatively, how can I at least prevent the 'query-within-the-loop'.