Using $this->db->like() in Codeigniter returns wrong/missing results

Question

I have an existing SQL query that works perfectly as I want it:

$this->db->select('places.*, category.*')
            ->select('COUNT(places_reviews.place_id) AS num_reviews')
            ->select('(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating')
            ->from('places')
            ->join('category', 'places.category_id = category.category_id')
            ->join('places_reviews', 'places_reviews.place_id = places.id', 'left')
            ->join('places_popularity', 'places_popularity.place_id = places.id', 'left')
            ->where('places.category_id', $category_id)
            ->group_by('places.id')
            ->limit($limit, $offset)
            ->order_by($sort_by, $sort_order);

However now I want to add a LIKE clause to the query by adding one more line to the above to get:

$this->db->select('places.*, category.*')
            ->select('COUNT(places_reviews.place_id) AS num_reviews')
            ->select('(places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating')
            ->from('places')
            ->join('category', 'places.category_id = category.category_id')
            ->join('places_reviews', 'places_reviews.place_id = places.id', 'left')
            ->join('places_popularity', 'places_popularity.place_id = places.id', 'left')
            ->where('places.category_id', $category_id)
                            ->like('places.name', $term)
            ->group_by('places.id')
            ->limit($limit, $offset)
            ->order_by($sort_by, $sort_order);

However it is giving me inaccurate results. For example, when i let the string being searched $term = "hong" and I have 3 rows where the 'name' column matches "hong" ie. (Hong Kong Cafe, Hong Kong Cafe, Ramen Hong), I will only get (Hong Kong Cafe, Hong Kong Cafe) returned. Now if $term = "hong kong", I only get one of the 'Hong Kong Cafe' returned and not both.

Another one puzzles me even further! There is a row called 'Dozo'. When $term = 'dozo', no result is returned!

Any ideas why this is happening?

Actual SQL generated Sorry it appears in 1 line

SELECT `places`.*, `category`.*, COUNT(places_reviews.place_id) AS num_reviews, (places_popularity.rating_1 + 2*places_popularity.rating_2 + 3*places_popularity.rating_3 + 4*places_popularity.rating_4 + 5*places_popularity.rating_5)/(places_popularity.rating_1 + places_popularity.rating_2 + places_popularity.rating_3 + places_popularity.rating_4 + places_popularity.rating_5) AS average_rating FROM (`places`) JOIN `category` ON `places`.`category_id` = `category`.`category_id` LEFT JOIN `places_reviews` ON `places_reviews`.`place_id` = `places`.`id` LEFT JOIN `places_popularity` ON `places_popularity`.`place_id` = `places`.`id` WHERE `places`.`category_id` = 1 AND `places`.`name` LIKE '%Dozo%' GROUP BY `places`.`id` ORDER BY `average_rating` desc LIMIT 1, 3

UPDATE

SOLVED. Its a pagination problem that passes the wrong variable to the LIMIT clause. Thanks!

two years with CodeIgniter and I somehow didn't know you can append -> commands to previous ones.. I keep doing $this->db->... for each additional case.. +1 because i apparently suck ;) — Atticus, May 10 '11 at 16:48
@Atticus: That is not Codeigniter, that is simply php. By returning the object in a method, you can chain methods. [Related Reading](http://stackoverflow.com/questions/3724112/php-method-chaining) — Wesley Murch, May 10 '11 at 17:07
Are you sure that `places.category_id = 1` is true for the fields you're querying? Nothing immediately jumps out at me otherwise. — Sean Walsh, May 10 '11 at 17:11
@Wesley -- curious if CI's database methods return the object back — Atticus, May 10 '11 at 17:13
@Atticus: Regarding [ActiveRecord](https://bitbucket.org/ellislab/codeigniter/src/d07b76bf0e75/system/database/DB_active_rec.php), many of the methods do this, as you can see in OP's code. Not "all" of course, like returning results. Just query builder functions. — Wesley Murch, May 10 '11 at 17:15
@Atticus: http://codeigniter.com/user_guide/database/active_record.html#chaining — gen_Eric, May 10 '11 at 17:15
places.category_id = 1 is true for all. Every single row in the table has places.category_id = 1 at the moment — Nyxynyx, May 10 '11 at 17:30
What happens if you run that exact query in MySQL Query Browser, PHPMyAdmin, or something similar? — Sean Walsh, May 10 '11 at 17:38
@s992: I get the same result/problem when running the query above in phpmyadmin — Nyxynyx, May 10 '11 at 18:18
UPDATE: I removed the LIMIT clause and it works!! What went wrong? And I need to use the LIMIT clause for pagination :( — Nyxynyx, May 10 '11 at 18:22
UPDATE: SOLVED. SQL query is fine. Its a problem with passing the wrong variable to LIMIT clause. Thanks everyone!! — Nyxynyx, May 10 '11 at 18:28
@Nyxynyx: Ah, you already figured the answer yourself I suppose! — ifaour, May 10 '11 at 18:33
I completely overlooked the LIMIT clause. Glad you got this sorted. — Sean Walsh, May 10 '11 at 19:21

score 3 · Accepted Answer · answered May 10 '11 at 18:31

From your actual query, your offset is beginning from 1 instead of 0 this way it'll ignore the first record (at offset 0).

So for the case:

Another one puzzles me even further! There is a row called 'Dozo'. When $term = 'dozo', no result is returned!

Nothing will be returned obviously.

Using $this->db->like() in Codeigniter returns wrong/missing results

1 Answers1