Kohana (KO3) columns with ORM join

Question

I'm trying to retrieve information from a database using Kohana ORM.

There are two relevant tables in my database:

branches

    id          smallint
    parent_id   smallint
    name        varchar
    active      int

branches_options

    id          mediumint
    branche_id  smallint
    name        varchar
    customer_id int

With the following code I want to retrieve the information from the branches_options table

`    $branchesOptions[] = ORM::factory('branches_option')
        ->where('branche_id', '=', $subBranche)
        ->join('branches', 'LEFT')->on('branches.id', '=', 'branches_options.branche_id')
        ->order_by('name')
        ->find_all()
        ->as_array();`

Now I want to see the value of branches.name in the result set, but I'm not sure how to do this in Kohana.

The code of the models is:

    `class Model_Branche extends ORM
     {
        protected $_has_many = array( 
            "options" => array('model' => 'branches_option'),
            "adwords_templates" => array ('model' => 'adwords_template')
        );

        public $result = array();`

and

    `class Model_Branches_option extends ORM
     {
        protected $_has_many = array ( 
            "keywords" => array('model' => 'branches_options_keyword')
        );

        protected $_has_and_belongs_to = array (
            "adwords_templates" => array (
                "model" => "adwords_template", 
                "through" => "branches_options_templates"
            )
        );

        protected $_belongs_to = array ( "branche" => array () );`

Can this be done and if so, how?

Answer 1

You need to make some important changes to you models for this to work properly:

class Model_Branche extends ORM
{
    protected $_has_many = array( 
        'options' => array(
            'model' => 'branches_option',
            'foreign_key' => 'branche_id'
        )
    );
}

And the Branches_Option model (it should be in model/branche/ folder):

class Model_Branches_Option extends ORM
{
    protected $_belongs_to = array(
        'branche' => array()
    );
}

Now you can do something like that:

$options = ORM::factory('branche', $branche_id)
    ->options
    ->find_all();

foreach ($options as $option)
{
    $branche_active = $option->branche->active;
    $branche_name = $option->branch->name;
    $option_name = $option->name;
}

One of the most important changes here is that we specify the foreign_key option in the $_has_many relationship. Since the ORM is using the Kohana Inflector helper it might not recognize it automatically (branches in singular form is branch and not branche).

If it doesn't work try specifying the $_table_name property for the same reason.

Answer 2

I'm trying to also grasp the concept here. In order to think like an ORM in this regard, you need to start with tables that the main tables reference as related information.

So in my world, I have a waste report, (model/waste/report) and there exists another table that has all the codes (model/waste/codes). so in the waste_reports table there's a column called code. That field might have 2E. 2E means nothing without the waste_codes table. The waste_codes table is (id, code, name).

I defined this relationship as such:

class Model_Waste_code extends ORM {

    protected $_primary_key = "code";

    protected $_has_one = array(
        'waste_report' => array()
    );
}

Then in my waste report model:

class Model_Waste_report extends ORM
{
    protected $_belongs_to = array(
        'codes' => array(
            'model' => 'waste_code',
            'foreign_key' => 'code'
        )
    ); 
}

To show different examples:

public function action_ormwaste() {
    $test = ORM::factory('waste_report',76);
    if ($test->loaded())
        echo $test->codes->name;

    echo "<hr noshade />";

    $test = ORM::factory('waste_report')->find_all();
    foreach($test as $row)
        echo $row->codes->name . "<BR>";

}

Would output:

Error with image file (mirrored, etc)
-------------------------------------
Wrong Size
Extra Prints
Error with image file (mirrored, etc)
etc...

So in essense, the join on the data is handled on the fly. I'm using Kohana 3.2.

Thanks, that cleared me up.