phpcodeigniterjoinleft-joinquery-builder

Attempt to JOIN a table but allow the possibility of no related row with CodeIgniter query builder

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 make the code cleaner.

Solution

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

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