Yii Framework Forum: Confused about Relational Active Record - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Confused about Relational Active Record Rate Topic: -----

#1 User is offline   SystemicPlural 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 129
  • Joined: 12-April 10

Posted 21 July 2010 - 10:00 AM

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.
0

#2 User is offline   SystemicPlural 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 129
  • Joined: 12-April 10

Posted 21 July 2010 - 11:00 AM

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'),
        );
    }

0

#3 User is offline   frantic 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 172
  • Joined: 01-March 10
  • Location:Saint-Petersburg

Posted 21 July 2010 - 11:14 AM

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',
        ),
));

1

#4 User is offline   SystemicPlural 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 129
  • Joined: 12-April 10

Posted 21 July 2010 - 11:37 AM

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.
0

#5 User is offline   PoL 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 506
  • Joined: 05-November 08
  • Location:Buenos Aires, Argentina

Posted 21 July 2010 - 11:42 AM

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'),
        );
    }

Don't say what you think, think what you say
The problem is communication! Excess of communication!
0

#6 User is offline   SystemicPlural 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 129
  • Joined: 12-April 10

Posted 21 July 2010 - 11:56 AM

Thanks Pol. It did the trick.

I tried that earlier, but I used

,'joinType'=>'INNER'


instead of

,'joinType'=>'INNER JOIN'

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users