Getting the number of images of an item : Join and Count

PHP Code:

function my_posts(){
    $mp = $this->db->SELECT('p.*, count(pi.post_image_id) as image_count')
    ->from('posts as p')
    ->join('post_images as pi', 'pi.post_id = p.post_id', 'LEFT')
    ->group_by('p.post_id')
    ->get()
    ->result();

    return $mp;
       } 


when you do left join all the rows from posts are retrieved.
left join is used in case if there is no image for the post, if left is not used then that post is won't be retrieved.
if there are N images for the post the there are will be N records(rows) for the post, but as you see I used to GROUP BY clause which groups by post_id that means those N recordes are become 1 row. 
COUNT(pi.post_image_id) counts the number of image in the group.
if you join another table you may need change it to COUNT(DISTINCT pi.post_image_id)