AR Relation

Archives Yii 1.0.x Discussions
1

I have the following code:


$new = new CoreMembers();

        $findAll = $new->with('groups')->find('member_id = 15');

        $test = $findAll->getAttributes();

        print_r($test);

Using the following relation arrays:

CoreGroups:


    public function relations()

    {

        return array(

            'members' => array(self::HAS_MANY, 'CoreMembers', 'member_group_id', 'alias'=>'member')

        );

    }

CoreMembers:


    public function relations()

    {

        return array(

            'groups' => array(self::BELONGS_TO, 'CoreGroups', 'member_group_id', 'alias' => 'groups', 'together' => true),

        );

    }

The code above runs the following query:





12:07:08.187131 	trace 	system.db.CDbCommand 	


Querying SQL: SHOW COLUMNS FROM `members`


12:07:08.193823 	trace 	system.db.CDbCommand 	


Querying SQL: SHOW CREATE TABLE `members`


12:07:08.201379 	trace 	system.db.CDbCommand 	


Querying SQL: SHOW COLUMNS FROM `groups`


12:07:08.204816 	trace 	system.db.CDbCommand 	


Querying SQL: SHOW CREATE TABLE `groups`


12:07:08.205404 	trace 	system.db.ar.CActiveRecord 	


CoreMembers.find() eagerly


12:07:08.205933 	trace 	system.db.CDbCommand 	


Querying SQL: SELECT `members`.`member_id` AS `t0_c0`, `members`.`name` AS

`t0_c1`, `members`.`member_group_id` AS `t0_c2`, `members`.`email` AS

`t0_c3`, `members`.`joined` AS `t0_c4`, `members`.`ip_address` AS `t0_c5`,

`members`.`time_offset` AS `t0_c6`, `members`.`skin` AS `t0_c7`,

`members`.`language` AS `t0_c8`, `members`.`last_visit` AS `t0_c9`,

`members`.`last_activity` AS `t0_c10`, `members`.`mgroup_others` AS

`t0_c11`, `members`.`members_display_name` AS `t0_c12`,

`members`.`members_seo_name` AS `t0_c13`,

`members`.`members_created_remote` AS `t0_c14`, `members`.`members_cache`

AS `t0_c15`, `members`.`members_disable_pm` AS `t0_c16`,

`members`.`members_l_display_name` AS `t0_c17`,

`members`.`members_l_username` AS `t0_c18`, `members`.`failed_logins` AS

`t0_c19`, `members`.`failed_login_count` AS `t0_c20`,

`members`.`members_profile_views` AS `t0_c21`,

`members`.`members_pass_hash` AS `t0_c22`, `members`.`members_pass_salt` AS

`t0_c23`, `members`.`member_login_key` AS `t0_c24`,

`members`.`member_login_key_expire` AS `t0_c25`, `members`.`org_perm_id` AS

`t0_c26`, `members`.`identity_url` AS `t0_c27`, `members`.`member_banned`

AS `t0_c28`, `members`.`fb_uid` AS `t0_c29`, `members`.`fb_emailhash` AS

`t0_c30`, `members`.`fb_emailallow` AS `t0_c31`, `members`.`fb_lastsync` AS

`t0_c32`, `members`.`fb_photo` AS `t0_c33`, `members`.`fb_photo_thumb` AS

`t0_c34`, `members`.`pp_status` AS `t0_c35`, `members`.`pp_status_update`

AS `t0_c36`, `members`.`avatar_location` AS `t0_c37`,

`members`.`avatar_size` AS `t0_c38`, `members`.`avatar_type` AS `t0_c39`,

`members`.`pp_main_photo` AS `t0_c40`, `members`.`pp_main_width` AS

`t0_c41`, `members`.`pp_main_height` AS `t0_c42`,

`members`.`pp_thumb_photo` AS `t0_c43`, `members`.`pp_thumb_width` AS

`t0_c44`, `members`.`pp_thumb_height` AS `t0_c45`, `members`.`pp_gender` AS

`t0_c46`, `members`.`pp_about_me` AS `t0_c47`, groups.`g_id` AS `t1_c0`,

groups.`g_access_cp` AS `t1_c1`, groups.`g_title` AS `t1_c2`,

groups.`g_perm_id` AS `t1_c3` FROM `members`  LEFT OUTER JOIN `groups`

groups ON (`members`.`member_group_id`=groups.`g_id`) WHERE (member_id =

15

But it doesn’t return the columns from the groups table. How can i make the code above return the columns from the members table and the associated columns from the group table?

Thanks.

2

There are other post talking about similar problems, but basically you can’t relate a record with some other records (of other tables) if the first record doesn’t exist in the database (remember that $new is a new record and it is not saved yet, even if you save the record you need to fetch it again!!!)

3

Forget the relation then…how do i join between two tables using LEFT JOIN?

4

You can use together() in combination with with()




$posts=Post::model()->with(

    'author.profile',

    'author.posts',

    'categories')->together()->findAll();

http://www.yiiframework.com/doc/guide/database.arr

5

So just for an example if i use members and groups as my tables i would like to fetch member id 1 and the group data from the groups table, this is how it’s done:


$member=Members::model()->with(

    'groups.title',

    'groups.id',

    'groups')->together()->find('member_id = 1');

Like that? I think something is missing. Where is the ON clause for the LEFT JOIN?

6

Yes, but you will access to the group data like this:


$member->group->id;
7

Doesn’t work.

I used:


$findAll = $member=CoreMembers::model()->with(

        'groups.g_id',

        'groups.g_title',

        'groups')->together()->find('member_id = 15');

        $test = $findAll->getAttributes();

        print_r($test);

And i get:


Relation "g_id" is not defined in active record class "CoreGroups". (C:\wamp\www\all_mighty_yii\Library\Yii\db\ar\CActiveFinder.php:249)

How do i just use a simple join?!

something like:


$member=CoreMembers::model()->join(array('groups' => 'g_id=member_group_id'))->find('member_id = 15');
8

It is auto-generated by Yii as you can see in your first post.

together() makes no difference here as only one relation is involved.

Simply running




CoreMembers::model()->with('groups')->findByPk(15);

has the desired effect, doesn’t it? I am assuming that member_id is your primary key.

9

Yea that worked, Thanks. How do i join another table? and how can i set in that certain instance not to fetch from the members table all of the columns? but just the ones i want? and doing the same for groups?

10

You can specify multiple relations when calling with: with(‘groups’, ‘relation2’, etc…).

Defining array item with ‘select’ index in model relations will make the query only select the requested fields. You can also set ‘select’ in CDbCriterias and criteria arrays. See the corresponding find methods in API.