Multiple joins in Codeigniter (Active Records)

I'm trying to retrieve results from my database with details spanning across tables 'places', 'category' and 'reviews', then sort by number of reviews. When I add the 2nd join, only one row is retrieved.

$this->db->select('places.*, category.*')
            ->from('places')
            ->join('category', 'places.category_id = category.category_id')
            ->join('places_reviews', 'places_reviews.place_id = places.id')
            ->where('places.category_id', $category_id)
            ->limit($limit, $offset)
            ->order_by($sort_by, $sort_order);

I did not add in the IFNULL(COUNT(places_reviews('review_id')) AS 'num_reviews', 0) into the select() function to开发者_StackOverflow中文版 make the code cleaner.

Any ideas?

I think what you want is a LEFT JOIN, try:

->join('places_reviews', 'places_reviews.place_id = places.id', 'left')