How to JOIN multiple tables and COUNT joined records for each parent record using CodeIgniter

I am currently getting details from my 'social_posts' table, and then adding it's tags, the number of likes, and the number of answers it has to the resulting object. Is there a way I can be doing this without having to do extra queries in a loop?

$query = "SELECT * FROM social_posts JOIN users ON social_posts.user_id = users.id";
$posts = $this->db->query($query);

if ($posts->num_rows() > 0) {        
    foreach ($posts->result() as $p => $post) {                     
        // Get the question's tags
        $tags = $this->db->query("SELECT * FROM social_tags 
            WHERE post_id = ?", $post->post_id);
        
        // Get the number of likes
        $likes = $this->db->query("SELECT id FROM social_likes 
            WHERE post_id = ?", $post->post_id);
        
        // Get the number of answers
        $answers = $this->db->query("SELECT id FROM social_responses 
            WHERE post_id = ?", $post->post_id);
        
        $post->tags = $tags->result();
        $post->likes = $likes->num_rows();
        $post->answers = $answers->num_rows();
        $post->author = array(
            "firstname" => $post->firstname,
            "thumbnail" => $post->thumbnail,
        );
    }
    
    return $posts->result();
} else {
    return FALSE;
}

Solution

You may try this SQL:

SELECT 
    social_posts.*,
    users.*,
    GROUP_CONCAT(social_tags.name) AS tags,
    COUNT(social_likes.id) AS likes,
    COUNT(social_responses.id) AS answers
FROM 
    social_posts 
JOIN
    users ON social_posts.user_id = users.id
LEFT JOIN
    social_tags ON social_tags.post_id = social_posts.id
LEFT JOIN
    social_likes ON social_likes.post_id = social_posts.id
LEFT JOIN
    social_responses ON social_responses.post_id = social_posts.id
GROUP BY
    social_posts.id

You will get the tags as comma delimited string. Of course you need to adjust the column names to fit your database.

This can be scripted as follows:

public function getSummary(): array
{
    $this->db
        ->select('
            social_posts.*,
            users.*,
            GROUP_CONCAT(DISTINCT social_tags.name) AS tags,
            COUNT(DISTINCT social_likes.id) AS likes,
            COUNT(DISTINCT social_responses.id) AS answers
        ')
        ->from('social_posts')
        ->join('users', 'social_posts.user_id = users.id')
        ->join('social_tags', 'social_tags.post_id = social_posts.id', 'left')
        ->join('social_likes', 'social_likes.post_id = social_posts.id', 'left')
        ->join('social_responses', 'social_responses.post_id = social_posts.id', 'left')
        ->group_by('social_posts.id')
        ->get()
        ->result();
}