Some help with Relational Active Record needed

Yii 1.1 General Discussion
1

Hi all,

I’m getting a little bit lost here, been working on this one issue for hours and I just don’t get anywhere. I hope you can help me.

The following is my setup:

I have a rather small application, with the extensions yii-user and rights installed.

Thus, I have, among others, the following two tables:

tbl_users:

id | username | superuser | …

AuthAssignment:

itemname | userid | bizRule | data

Additionally, I have a table called tbl_jobs, which simply contains something like projects one could say.

id | name | owner_id | responsible_admin_id | …

My first aim is to get a dropDown-Menu for the _form.php of jobs, so that I can choose the responsible admin for a job, which means any user with tbl_users.superuser=1.

This was actually pretty easy:


    

/**

* @return array of valid admin users, indexed by User IDs

*/

    public function getAdminOptions()

    {

        $criteria=new CDbCriteria;

        $criteria->select='id, username';  // only select the 'title' column

        $criteria->condition='superuser=1';

        $adminOptions = User::model()->findAll($criteria);


        $adminArray = CHtml::listData($adminOptions, 'id', 'username');

        return $adminArray;

    }

But my second task is the following:

I want to list all users, again with id and username, which have a certain role assigned, let’s call it “JobOwner”.

Say the User with id = 3 is allowed to own a job on the site.

Then, in AuthAssign, there would be an entry:

itemname: JobOwner

userid: 3

(…)

But I simply have no idea how to get these rows.

Normally, I would think I could just do a query like

“SELECT tbl_users.id, tbl_users.username FROM tbl_users, AuthAssignment WHERE AuthAssignment.itemname = ‘Seeker’ AND tbl_users.id = AuthAssignment.userid”

But how can I achieve this using Relational Active Record?

I think my problem is that there is no "official", i.e. Foreign Key-based relation.

I tried to manipulate the relations within the User-Model (modules/user/models/User.php), but I’m not really sure which relation to add exactly.

There is modules/rights/models/Rights.php where I could look something up…

My thought was to do something like this:


'authRole'=>array(self::HAS_MANY, 'Rights', 'userid'),

And then, back in my models/Jobs.php, I tried to simply get all users with there assigned role:


    public function getOwnerOptions()

    {

        $ownerOptions = User::model()->with('authRole.itemname')->findAll();

        return $ownerOptions;

    }

But calling this function simply kills the whole application. ;)

Additionally, I’m confused with regards to how I add additional conditions when using with(), but I think this is one step ahead already.

Any help on

  1. how I would have to clarify relations in this case,

  2. how I can then use AR to get the data I look for

would be very appreciated!

Yii alone is quite comlex, but the fact I kind of have to work my way through two additional modules doesn’t make it easier I guess. ;)

In the meantime, I’ll try again via just a SQL query, but I think this is not really the way it should be done in Yii and it would be only a matter of time until I face the next problem.

Thanks

Christoph