Relations And Complicated Joining / Sorting

Yii 1.1 General Discussion
1

I have the following database structure

user

id

name

client

id

name

task

id

name

client_task_permissions

id

user_id

client_id

task_id

I have a relationship set up under my user that is a self::HAS_MANY

‘taskpermissions’=>array(self::HAS_MANY, ‘TaskPermission’, ‘user_id’)

That works great to get me the bulk of my data.

Then the client_task_permissions class TaskPermission has three relationships:

‘client’=>array(self::HAS_ONE, ‘Client’, array(‘ID’=>‘CLIENT_ID’)),

‘task’=>array(self::HAS_ONE, ‘Task’, array(‘ID’=>‘TASK_ID’)),

‘employee’=>array(self::BELONGS_TO, ‘Employee’, ‘USER_ID’),

My challenge now is figuring out how to sort my $user->taskPermissions records first by client.name and then by task.name:

I would usually use a series of joins to accomplish this with SQL and join in the client and task records with the permissions record to add the entities and sort by them. I can’t find any documentation here or via searching the forums (I’ve spent hours digging).

All I can seem to do is add an ‘order’=>’’ parameter to sort by any of the task_permissions table fields, but it won’t let me sort of the task_permission relationships.

Any good way to accomplish this? Seems like something that should be fairly common.

Thanks!

2

I think you want something like the following:




$criteria = new CDbCriteria;

$criteria->with = array(

    'taskPermissions'=>array(

        'with'=>array('client', 'task'));

$criteria->together = true;

$criteria->order = 'client.name ASC, task.name ASC';


$user = User::model()->findByPk($userId, $criteria);
3

What I want is for all of this to happen automatically when the relations entity is created. I would like to add an ‘order’ parameter to the relationship defined in the user class to sort the relevant permission records.

4

I suppose you could use a scope to do this and then just call:




$user = User::model()->with('orderedTaskPermissions')->findByPk($userId);

You’d need to configure the scope in a similar manner to my example above.

5

Keith, thanks for the insight. I’ve been doing a lot of digging and learning on yii this week, so I will check out scopes and see if that will help.

It seems like maybe I’m hoping for relationships to do too much for me that I would usually do with normal SQL, but I thought that was what yii was intended to help with!