How to join tables using ORM in Kohana

Question

Im as u can see in my history kinda new into kohana, but learning quick.. I am struggling on a weird problem that worked before in Kohana 3.3

I want to create a join query between two databases. i created the Model_Veiligen en Model_Veilingvoorkeur models, and put this as ORM call:

This is the RIGHT JOIN QUERY:

 SELECT `vastprijs`, `veilingen`.`id` AS `id`, `veilingen`.`gid` AS `gid`, `veilingen`.`provincie` AS `provincie`, `veilingen`.`created` AS `created`, `veilingen`.`termtime` AS `termtime`, `veilingen`.`postcode` AS `postcode`, `veilingen`.`lat` AS `lat`, `veilingen`.`lng` AS `lng`, `veilingen`.`titel` AS `titel`, `veilingen`.`beschrijving` AS `beschrijving`, `veilingen`.`images` AS `images`, `veilingen`.`ip` AS `ip`, `veilingen`.`unique` AS `unique` FROM `veilingen` AS `veilingen` RIGHT JOIN `veilingvoorkeur` ON (`veilingen`.`id` = `vid`) ORDER BY `veilingen`.`created` DESC LIMIT 10

DIT IS DE LEFT:

SELECT `vastprijs`, `veilingen`.`id` AS `id`, `veilingen`.`gid` AS `gid`,     `veilingen`.`provincie` AS `provincie`, `veilingen`.`created` AS `created`,     `veilingen`.`termtime` AS `termtime`, `veilingen`.`postcode` AS `postcode`,     `veilingen`.`lat` AS `lat`, `veilingen`.`lng` AS `lng`, `veilingen`.`titel` AS `titel`,     `veilingen`.`beschrijving` AS `beschrijving`, `veilingen`.`images` AS `images`,     `veilingen`.`ip` AS `ip`, `veilingen`.`unique` AS `unique` FROM `veilingen` AS `veilingen`     LEFT JOIN `veilingvoorkeur` ON (`veilingen`.`id` = `vid`) ORDER BY `veilingen`.`created` DESC LIMIT 10

RIGHT JOIN output:

[_object:protected] => Array ( [id] => [gid] => [provincie] => [created] => [termtime] => [postcode] => [lat] => [lng] => [titel] => [beschrijving] => [images] => [ip] => [unique] => [vastprijs] => 56 )

LEFT JOIN OUTPUT:

[_object:protected] => Array ( [id] => 121 [gid] => 10 [provincie] => 9 [created] => 1385534362 [termtime] => 1386743962 [postcode] => 8031CJ [lat] => 52.528510 [lng] => 6.081043 [titel] => gdfgd [beschrijving] => fgfdgdfgfdsfsfd [images] => [{"file_name":"5a5a7a7ef44.png","file_size":238687,"file_type":"image\/png","file_link":"uploads\/\/5a5a7a7ef44.png"}] [ip] => 94.215.33.178 [unique] => [vastprijs] => )

My code for the Left or right join is as follows:

$veiling = ORM::factory('veilingen')
        ->select('vastprijs')
        ->join('veilingvoorkeur', 'RIGHT')
        ->on('veilingen.id', '=', 'vid')
        ->order_by('veilingen.created', DESC)
        ->limit(10)
        ->find_all();

The problem im hanging into is that i don't understand the has_many and belongs_to, the example above worked before, but not anymore.

The problem that occurs if i change RIGHT in LEFT, it will output all data from the veilingen table. But it ignores the data of the vastprijs field. But if i print the foreach element, i can see that the vastprijs has been selected. Except for data.

If i use RIGHT, instead of left. above story occurs but then otherwise. vastprijs isn't empty and veilingen fields are empty. but shown.

My main question is, how can i relate those 2 tables together with maybe has_many, but veilingen only has_one veilingvoorkeur. The key is veilingen.id = veilingvoorkeur.vid

Why aren't you using a has_many/belongs_to (1:n) relationship? — kero
i just added some more information, i forgot about the question. Thanks in advance. Maybe you can explain me how to build the relationship between the Models? — KevinH
Does veilingvoorkeur always have a related item in veilingen (or can vid be empty)? — kero
Kingkero, vid can never be empty, every veilingen entry id has a veilingvoorkeur entry on vid. — KevinH

kero kero · Accepted Answer · 2013-11-27T23:17:08

My main question is, how can i relate those 2 tables together with maybe has_many, but veilingen only has_one veilingvoorkeur. The key is veilingen.id = veilingvoorkeur.vid

Problem where? Kohana also offers the possibility of has_one:

class Model_veilingen extends ORM {

    protected $_has_one = array(
        'veilingvoorkeur' => array(
            'model' => 'veilingvoorkeur',
            'far_key' => 'vid',
        ),
    );

}

Now as relationships have always two sides, you need to define the other as well. belongs_to is used when there always needs to be something on the other side.

class Model_veilingvoorkeur extends ORM {

    protected $_belongs_to = array(
        'veilingen' => array(
            'model' => 'veilingen',
            'foreign_key' => 'vid',
        ),
    );

}

If you are unsure whether to use foreign_key or far_key, this answer should help you. Also to explain relationships a little more:

A has_one B: One item from A has either one or no item from B related
A belongs_to B: One item from A always has one item from B related

Your query could now be done like this:

$veilings = ORM::factory('veilingen')->order_by('created', DESC')->limit(10)
    ->find_all();
foreach ($veilings as $veiling) {
    $veiling->veilingvoorkeur->find();
    print $veiling->veilingvoorkeur->vastprijs;
}

I'm honestly not quite sure what to do if $veiling as no related veilingvoorkeur, but it should be possible to do like this (from the idea)

    $veilingvoorkeur = $veiling->veilingvoorkeur->find();
    if (!empty($veilingvoorkeur))
        print $veilingvoorkeur->vastprijs;

I didn't test the code, if it fails somewhere I hope to at least have clarified on how to solve this using relationships.

How to join tables using ORM in Kohana

1 Answers