Confused about Relational Active Record

Thanks for your help on earlier questions, I’m making good progress in understanding Yii. However I am stuck again.

I’m confused as to how Relational Active Record works.

If I have several relationships between tables defined and I want a single row of data that crosses these tables. The documentation leads me to think that:

a ) The lazy loading approach makes a separate db call against each table to fetch the data.

b ) The eager loading approach brings back all the data using an outer join and then narrows the result down in the code.

Is this correct? If so it seems very wasteful of resources for large DB’s, so I think I am wrong.

Assuming I have created all the models for each class and defined the relationships between the primary keys, how would I recreate the following query using Relational Active Record?

SELECT access.access_id

FROM access

INNER JOIN site ON access.site_id = site.site_id

INNER JOIN user ON access.user_id = user.user_id

WHERE

site.domain = :domain

AND user.username = :username;

Here is the table structure:

CREATE TABLE IF NOT EXISTS `access` (

  `access_id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `site_id` int(11) NOT NULL,

  PRIMARY KEY (`site_access_id`),

);


CREATE TABLE IF NOT EXISTS `user` (

  `user_id` int(11) NOT NULL AUTO_INCREMENT,

  `username` varchar(128) NOT NULL,

  PRIMARY KEY (`user_id`)

);


CREATE TABLE IF NOT EXISTS `site` (

  `site_id` int(11) NOT NULL AUTO_INCREMENT,

  `domain` varchar(128) NOT NULL,

  PRIMARY KEY (`site_id`)

);

Edit: Forgot to mention that all the joins are one primary key to many.

Update:

I’ve worked out that this:

$results = Access::model()->with('site','user')->find(

	array(

		'username=:user', array(':user'=>'myUser'),

		'domain=:domain', array(':domain'=>'myDomain'),

	)

);

Essentially runs

SELECT access.access_id

FROM access

LEFT OUTER JOIN site ON access.site_id = site.site_id

LEFT OUTER JOIN user ON access.user_id = user.user_id

I just need to change those LEFT OUTERs to INNERs

And whats happend to my WHERE clause?

Perhaps I have my relationships wrong?

Access.php

    public function relations()

    {

        return array(

            'site'=>array(self::BELONGS_TO, 'Site', 'site_id'),

            'user'=>array(self::BELONGS_TO, 'User', 'user_id'),

        );

    }

User.php

    public function relations()

    {

        return array(

            'access'=>array(self::HAS_MANY, 'Access', 'user_id'),

        );

    }

Site.php

    public function relations()

    {

        return array(

            'access'=>array(self::HAS_MANY, 'Access', 'site_id'),

        );

    }

I think yii can’t understand what is the “username=:user” for AR finder, and it just ignore these rules. And in this case WHERE clause is just empty. Try this out:

$results = Access::model()->with('site','user')->find(array(

	'condition' => 'username=:user AND domain=:domain',

	'params' => array(

		':user'=>'myUser',

		':domain'=>'myDomain',

	),

));

or ( not sure ):

$results = Access::model()->find(array(

        'with' => array('site','user'),

        'condition' => 'username=:user AND domain=:domain',

        'params' => array(

                ':user'=>'myUser',

                ':domain'=>'myDomain',

        ),

));

Thanks frantic

The first one works.

It still uses LEFT OUTER joins, but after a moments head scratching I realised that in this case it does not matter because the where clauses prevents the left joins from including surplus data.

I’m still not sure what I would do if I had a genuine need for an INNER JOIN.

In the relations method you can use the ‘joinType’ to specify the join type:

eg:

    public function relations()

    {

        return array(

            'site'=>array(self::BELONGS_TO, 'Site', 'site_id','joinType'=>'INNER JOIN'),

            'user'=>array(self::BELONGS_TO, 'User', 'user_id','joinType'=>'INNER JOIN'),

        );

    }

Thanks Pol. It did the trick.

I tried that earlier, but I used

,'joinType'=>'INNER'

instead of

,'joinType'=>'INNER JOIN'