AR query question

Yii 1.1 General Discussion
1

OK, it may be obvious but how do I

SELECT *

FROM People p

JOIN UserToPeople up ON p.person_id = up.person_id

AND up.user_id =Yii::App->user->id

AND up.role = ‘Assistant’

where

UserToPeople is user_id,person_id, role

via ActiveRecord. ie without handcrafting the SQL.

I am still trying to get my head around AR’s

Thanks,

Mike

2

OK, my example was a little oversimplified. I have solved my issue using the newly added ‘through’ relation.




$dataProvider=new CActiveDataProvider('Person',array(

			'pagination'=>array(

				'pageSize'=>40,

			),

	

		));


$criteria=new CDbCriteria;

$criteria->with = array('person','user','user_to_person');

$criteria->together = true;

$criteria->addSearchCondition('user_to_person.role','Assistant');

$criteria->addSearchCondition('user_to_person.agent_id',Yii::App()->user->getId());	


$dataProvider->criteria = $criteria;

I have some more complex needs similar to this which will need to JOIN other entities. I am a little concerned about performance as I add relationships. Is this the correct direction to be going?

Mike

3

Hi,

I would generally go for setting up an AR rather than adding search criterias all over the place.

With your particular situation, I’d start by creating a MANY_MANY Relationship in your User model.

public function relations() {

return array(

‘nameTheRelationship’ => array(self::MANY_MANY, ‘Person’,

    'tableNameOfTheRelationship(agent_id, person_id)')

);

}

You can now call this relationship in the controller.

$user = User::model()->findByPk(Yii::app->user->Id);

$user->nameTheRelationship; //this will output an array of all Persons that are related to User

$user->nameTheRelationship(array(‘condition’ => ‘role = Assistant’)) //this will output an array of Persons that have role Assistant and are related to User.

You can pass the output to a CArrayDataProvider in order to work with the Grid View and etc.

NOTE: You can also achieve this by using Named Scopes and Parameterized Named Scopes which would be faster in most cases. But for now, I think, it is important that you grasp the AR capabilities of Yii.

I hope that helps! Happy coding!

4

Great thanks. I wasn’t sure the role field was available with this method. The other issue I am wondering about is doing this without pagination the way you described.

In my example I do this via ActiveDataProvider with Pagination set so the query limits to 40 records. In your example assuming I am an ‘Assistant’ on 50,000 records wouldn’t it try to load 50,000 AR’s exhausting memory?

In the example I gave I am fetching a count of 50,000 but only loading 40 AR’s at a time.

I really appreciate the feedback and will continue to play and learn. This is the kind of performance stuff I am trying to wrap my head around. For example what if I need ‘Person’ with ‘Address’ with ‘PhoneNumber’ where User is in role ‘Assistant’. The amount of Joins starts slowing down a ‘together’ query doesn’t it?

Hmmmmm… maybe I just answered my own question. Lazy loading ‘Address’ and ‘TelephoneNumber’ of the 40 ‘Person’ records on a page is maybe more efficient?

I appreciate the discussion as I learn this. I will experiment and come back.

Thanks again.

5

No worries! Glad to help.

If you’re experimenting, Named Scopes and Parameterized Named Scopes are good starting points. Good luck!

6

Thanks again Jaymard. I am just went and profiled my original method and the method via Relationships

Profile:

GetPeople via relationship 1 8.22865 8.22865 8.22865 8.22865

GetPeople via CactiveDataProvider 1 1.72239 1.72239 1.72239 1.72239

Will try a few other methods and post back. Any other comments or suggestions anyone?

This is with a total record count of 15,000 UserToPerson’s where person table has about 99,000 records.