Bug in Kohana 3 ORM?

Sorry to ask all these questions about Kohana. They usually get ignored. I think I just found a bug. I'm making a join between two tables that are not directly related.

$results = ORM::factory('foo')->join("bar")->on("foo.foreign_id", "=", "bar.id");

This generates a query that does not resolve the table names explicitly:

SELECT * FROM `foo` JOIN `bar` ON (`foo`.`foreign_id` = `bar`.`id`)

Which gives (in phpMyAdmin) a table that looks like this:

id    time          foreign_id      blah_int    id  baz
4     1291851245    3           0               3   52501504

Notice there are two id columns, one for the foo table and one for bar. This is a real problem. Because now, in my results, if I loop through...

foreach ($results as $result) {
    echo $result->id;    // prints 3!!!
}

Because my results should be foo objects, I expect to get an id of 开发者_JS百科4, but it's giving me 3 because of the join. Is this a bug in the ORM library? Should I be using a different method to restrict my results from the query? I really don't want to do two separate queries where I load all the bars id's, and then load my foos that way, but it looks like I have to.

You have to use the Database object to build raw queries, not ORM, like this:

$results = DB::select()->from('foo')->join('bar')->on("foo.foreign_id", "=", "bar.id")->execute();

You will need to specific some column aliases however to make your query work unless you use ORM as it was intended.

Using ORM

If you want to use ORM, you need to define the relationships in your model. You mention that they share a relationship with another table so in your case you could use a has many through relationship like this:

protected $_has_many = array(
    'bars' => array('model' => 'bar', 'through' => 'other_table', 'foreign_key' => 'foreign_id'),
    );

Although your example as given suggests that a straight has_many relationship would work:

protected $_has_many = array(
    'bars' => array('model' => 'bar','foreign_key' => 'foreign_id'),
    );

This would allow you to access all of the bars using a statement like

$bars = $results->bars->find_all();
foreach($bars as $bar)
{
    echo $bar->id; // should echo 4, assuming one record in bars with id 4
}

The Kohana 3.1 ORM Reference Guide is good place to start if you want to learn more about ORM and relationships

Using the Kohana database object and query builder

If you prefer ad hoc queries and are doing joins using the query builder you will likely have colliding column names regardless if you are using Kohana or just raw queries (pop "SELECT * FROM foo JOIN bar ON (foo.foreign_id = bar.id)" into MySQL and you will get the exact same result).

Kohana, just like MySQL allows you to define column aliases for precisely this reason. (See here for more information)

Rewrite your query as follows:

$results = DB::select('id', 'time', 'foreign_id', array('bar.id', 'bar_id'), 'baz')->from('foo')->join("bar")->on("foo.foreign_id", "=", "bar.id")->execute();

This will return:

id    time          foreign_id      blah_int    bar_id   baz
4     1291851245    3               0           3        52501504