Kohana 3 - Query builder gives 0 rows

Question

The following query returns one row as expected when run from phpmyadmin.

SELECT units .  * , locations .  *
FROM units, locations
WHERE units.id = '1'
AND units.location_id = locations.id
LIMIT 0 , 30 

But when I try to do it in Kohana 3:

$unit = DB::select('units.*', 'locations.*')
->from('units', 'locations')
->where('units.id', '=', $id)->and_where('units.location_id', '=', 'locations.id')
->execute()->as_array();
var_dump($unit);

It prints

array(0) { }

What am I doing wrong?

Answer 1

I can't immediately tell what is wrong with that query builder, however, checkout this for debugging purposes.

After calling execute() on your db chain, try this.

echo Database::instance()->last_query;

This will show in plain SQL the last query performed. It will be worth looking at what the query builder generated, and how it differs to your SQL you used in phpmyadmin.

If all else fails, just use the plain query methods.

$query = "SELECT units .  * , locations .  *
FROM units, locations
WHERE units.id = :id
AND units.location_id = locations.id
LIMIT 0 , 30 ";

$unit = Db::query(Database::SELECT, $query)
          ->bind(':id', (int) $id)
          ->execute()
          ->as_array();

Answer 2

You'll notice that your call to last_query returned (in the where portion):

WHERE units.location_id = 'locations.id' the value being passed for comparison is being quoted as a string, hence an empty result set. This may help you: http://kohanaframework.org/guide/api/DB#expr

As for your fatal error, double check to be sure you are passing $id explicitly (and not a referenced copy of another variable), that's all I could think of considering you aren't providing any source.