CODEWITHSUNDEEP
phpdatabaseactiverecordyii
Andrew
Andrew

Reputation: 987

Yii CActiveRecord multi level (nested) joins

I'm fairly new to Yii and so far I've managed to get by on my own but now I'm stuck.

I have a complex relational db (implemented in MySQL). I have the models for them and it's working properly my only problem is that I can't figure out how to make complex queries with CDbCriteria

The application is like an issue tracker so a user can report some problem and someone in charge of that type of problem will contact him/her.

Main tables relevant to the problem:

A developer can create an issue like "new PHP version needed" but the accountant can't do that so I need to query the database for all the available issuetypes for a set of roles. If the user have multiple roles (developer, tester) then I need the union of the issuetypes available for those roles. So far it's working but when I need to take it a step further and query all the issues submitted with these issuetypes...I'm stuck.

Roughly I need to get the following query:

SELECT DISTINCT i.* FROM `issue` i
    LEFT JOIN issuetype ON issuetype.id=i.issuetype_id
    RIGHT JOIN role_has_issuetype rit ON rit.issuetype_id=issuetype.id
    RIGHT JOIN role ON role.id=rit.role_id
WHERE role.role IN ('developer','tester') AND i.id IS NOT NULL

I know I could use the SQL query directly but the db backend will change in the future (most probably to Oracle) so I'd like to keep the abstraction as far as I could to avoid changing any hard-coded SQL statement and be "backend-independent".

The relevant parts of the models:

class Role extends CActiveRecord
{
    public function relations()
    {
        return array(
            'issuetypes' => array(self::MANY_MANY, 'Issuetype', 'role_has_issuetype(role_id, issuetype_id)'),
            'users' => array(self::MANY_MANY, 'User', 'user_has_role(role_id, user_id)'),
        );
    }
}

class Issuetype extends CActiveRecord
{
    public function relations()
    {
        return array(
        ...
        'issues' => array(self::HAS_MANY, 'Issue', 'issuetype_id'),
        'roles' => array(self::MANY_MANY, 'Role', 'role_has_issuetype(issuetype_id, role_id)'),
    );
}

class Issue extends CActiveRecord
{
    public function relations()
    {
        ...
        'issuetype' => array(self::BELONGS_TO, 'Issuetype', 'issuetype_id'),            
    );
    }
}

I've tried something like this:

Issue::model()->with(
array(
    'issuetype'=>array(
     'select'=>false,
     'joinType'=>'INNER JOIN',
     'condition'=>'issuetype.roles IN ("developer","tester")',
)))->findAll();

It doens't work because issuetype has no column roles it's just a relation.

I've tried to do it in two steps. First get the issuetypes associated with the roles then get the issues.

The first part is working with this code:

$crit = new CDbCriteria();
$crit->addInCondition('roles.role',array('developer','tester'));
$crit->select = array('id');
$res=Issuetype::model()->with('roles')->findAll($crit);

But I don't know how to use the $res in another criteria. (I'm not even sure this approach will work and even if will it's far from optimal)

I've read about a dozen SO answers and read the Yii forum together with the Yii docs but the examples I've found were not sufficient to solve this (at least I couldn't adopt those codes to my problem)

I'm quite sure I'm just overlooking some obvious stuff but unfortunately I can't figure it out on my own.

Thanks

Upvotes: 1

Views: 686

Answers (1)

Andrew
Andrew

Reputation: 987

Sleeping on the problem helped :)

I've realized my mistake. I should have written this:

return Issue::model()->with(array(
                            'issuetype.roles'=>array(
                                'select'=>false,
                                'joinType'=>'INNER JOIN',
                                'condition'=>"roles.role IN ('developer','tester')",
                            )))->findAll();

I hope this will help someone in the future.

Upvotes: 1

Related Questions