6

I believe there is an error in this line in Codeigniter using Active Records, but I cant seem to figure out the syntax on the second line with IFNULL() and COUNT()

$this->db->select('places.*, category.*')
            ->select('IFNULL(COUNT("places_reviews.place_id"), 0) AS num_reviews')
            ->from('places')
            ->join('category', 'places.category_id = category.category_id')
            ->join('places_reviews', 'places_reviews.place_id = places.id', 'left')
            ->where('places.category_id', $category_id)
            ->group_by('places.id')
            ->limit($limit, $offset)
            ->order_by($sort_by, $sort_order);
Nyxynyx
  • 61,411
  • 155
  • 482
  • 830

1 Answers1

17

Add false after the SELECT statement. CodeIgniter is trying to escape the statement with backticks and doesn't know how to do so correctly. The false will tell it not to.

->select('IFNULL(COUNT(`places_reviews.place_id`), 0) AS `num_reviews`', false)

EDIT: In COUNT("places_reviews.place_id"), the quotes should be backticks.

gen_Eric
  • 223,194
  • 41
  • 299
  • 337