TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
[K3 ORM] Using join in ORM
  • joe1203joe1203
    Hi expert!
    I am planning to use orm to simulate the following sql

    select * from table1 inner join (select id from table2) as table2 on table2.id=table1.table2_id


    I have tried the following but it doesn't work. The generated sql statement misses the table2 subquery (i.e. (select id from table2). Please give me some hints.

    $table1=ORM::factory('table1');
    $table2=ORM::factory('table2');
    $table2->select(array('id','id'));
    $table1=$table1->select("*")->join(array($table2,'table2'),'inner')->on('table2.id','=','table1.table2_id')->find_all()->as_array();
  • papershoespapershoes
    You'll want to create a model for each table and define relationships. Have you read the documentation?
  • papershoespapershoes
    Example of a has_one / belongs_to relationship between users and profiles tables. Make sure you have read up on the naming conventions in the user guide.

    file: application/classes/model/user.php

    <?php defined('SYSPATH') or die('No direct script access.');

    class Model_User extends ORM
    {
    # Relationships
    protected $_has_one = array(
    'profile' => array('model' => 'profile'),
    );
    }


    file: application/classes/model/profile.php

    <?php defined('SYSPATH') or die('No direct script access.');

    class Model_Profile extends ORM
    {
    # Relationships
    protected $_belongs_to = array(
    'user' => array('model' => 'user', 'foreign_key' => 'id'),
    );

    # Instead of 'id' we use 'user_id' as the name of the primary key for this table
    protected $_primary_key = 'user_id';
    }


    Now when you instantiate an existing user you'll be able to access columns from the joining profiles table. In this case lets assume the users table has columns: id, username, email and the profiles table has columns: user_id, name, postalcode.


    # this instantiates a user object where ID = 1
    $user = ORM::factory('user', 1);

    echo $user->id; // prints 1
    echo $user->username; // prints username
    echo $user->profile->name; //prints name
    echo $user->profile->postalcode; //prints postal code


    The 'profile' in $user->profile->name is the alias you created for the has_one relationship.

    Hope this helps.
  • joe1203joe1203
    Posted By: papershoesexisting

    Yes, I did
    just too lazy to post the ORM extended table1 and table2 object
  • joe1203joe1203
    Posted By: papershoes

    Example of a has_one / belongs_to relationship between users and profiles tables. Make sure you have read up on the naming conventions in the user guide.

    file: application/classes/model/user.php

    <?php defined('SYSPATH') or die('No direct script access.'); class Model_User extends ORM { # Relationships protected $_has_one = array( 'profile' => array('model' => 'profile'), ); }

    file: application/classes/model/profile.php

    <?php defined('SYSPATH') or die('No direct script access.'); class Model_Profile extends ORM { # Relationships protected $_belongs_to = array( 'user' => array('model' => 'user', 'foreign_key' => 'id'), ); # Instead of 'id' we use 'user_id' as the name of the primary key for this table protected $_primary_key = 'user_id'; }

    Now when you instantiate an existing user you'll be able to access columns from the joining profiles table. In this case lets assume the users table has columns: id, username, email and the profiles table has columns: user_id, name, postalcode.

    # this instantiates a user object where ID = 1 $user = ORM::factory('user', 1); echo $user->id; // prints 1 echo $user->username; // prints username echo $user->profile->name; //prints name echo $user->profile->postalcode; //prints postal code

    The 'profile' in $user->profile->name is the alias you created for the has_one relationship.

    Hope this helps.



    Thanks papershoes.
    The problem is that actually table2 contains MANY field. I just need a few field but NOT all fields. That's why I plan to use SQL 'join' to see if loading particular field is possible. $table1->table2->field will work but will be the last measure.
  • joe1203joe1203
    so there is no way to load particular column in ORM join statement?
  • WebthinkWebthink
    No there isn't.
Add a Comment