Tables not joining in Kohana 3.1 ORM

Question

How do I get this to work?

$stuff = ORM::factory('mytable')
    ->with('user')
    ->with('other_stuff')
    ->find_all();

I've got all of my relationships set up and everything seems to be working when I do other queries. However, in the query above it is not joining table users to mytable. I think it may be because there can be many users for one mytable.

In the reference there is a method called join() which I think I might need to use here, but they don't give any information on it, and the stuff I've searched for on here does not work.

When I try to use join instead of with, it tries to join the table, but it doesn't include any "join on" information, just gives an empty ().

I know my ORM DB relationships are all set up correctly, so I'm a bit baffled.

score 5 · Accepted Answer · answered Apr 16 '11 at 08:40

5

Kohana has decent documentation, not looking in the right place is ... well, your problem.

ORM::with() is used for loading one-to-one (belongs to and has one) relations, though you have all the Database_Query_Builder methods to use with ORM on your disposal:

$stuff = ORM::factory('mytable')
        ->join('users','LEFT')
        ->on('users.mytable_id','=','mytables.id')
        ->find_all();

answered Apr 16 '11 at 08:40

Kemo

6,942
3
32
39

That was it. I was missing the `->on()` stuff. /sigh. I didn't realize that you could use DB Query Builder methods with ORM. I'm learning Kohana as I go, and there was nothing in the documentation about that! But now I know, and I thank you very much for your help! -Brian – user256430 Apr 16 '11 at 09:04
A quick followup question... I'm getting the correct DB results back from all the "with" tables, but nothing from the "joined" table. How do I get one field's data from the users table in addition to the others? Thx. – user256430 Apr 16 '11 at 10:38

score 1 · Answer 2 · edited Apr 25 '12 at 12:28

SELECT * from table1
LEFT JOIN table2
ON table1.id = table2.id
AND table2.flag = 'Y' 
AND table2.siteid = '12'
WHERE table1.siteid = '12'

How the above query is written in ORM format of kohana? Is the below one is correct

$stuff = ORM::factory('table1')
    ->join('table2','LEFT')
    ->on('table1.id','=','table2.id')
    ->on('table2.flag','=','Y')
    ->on('table2.siteid', '=', '12')
    ->where('table1.id', '=', '12')
    ->find_all();

Tables not joining in Kohana 3.1 ORM

2 Answers2

Linked