onCondition added to where part of child query

[yomexzo]

I shared the same problem on stackoverflow - http://stackoverflow.com/questions/30349913/oncondition-added-to-where-part-of-child-query

I noticed in v2.1, the onCondition is being added to the where condition -https://github.com/yiisoft/yii2/blob/2.1/framework/db/ActiveQuery.php#L202

I do not know why, but this is affecting me from being able to use my relation as below.

I am working on the RBAC module for a new project. I am not using Yii2's default because it is a SaaS system, roles have to be defined per account, we would rather use id instead of name as unique keys and we do not like the naming conventions.

I have tables permissions, privileges and roles

privileges - [id, name] - things you can do in the system roles - [account_id, id, name] - different roles per account permissions - [parent_type, parent_id, child_type, child_id] - mapping of roles to privileges, roles to roles, and privileges to privileges.

Problem now is i need to findAll permissions and get the child / parent having the appropriate objects.

For parent, I have

public function getParentPrivilege($type) {
    return $this->hasOne(Privilege::className(), ['id' => 'parent_id'])->onCondition('parent_type = "privilege"');
}

public function getParentRole() {
    return $this->hasOne(Role::className(), ['id' => 'parent_id'])->onCondition('parent_type = "role"');
}

public function getParent(){
    if($this->parentPrivilege)
        return $this->parentPrivilege;
    else
        return $this->parentRole;
}

Then i do a find by:

$permissions = \app\models\Permission::find()
    ->joinWith(['parentRole','parentPrivilege'])
    ->all();

Problem is, onCondition is used in the SQL on condition since I used joinWith BUT, the onCondition is still being added to the where clause of the parent queries as seen below.

SELECT `permissions`.* FROM `permissions` 
LEFT JOIN `roles` ON (`permissions`.`parent_id` = `roles`.`id`) AND (parent_type = "role") 
LEFT JOIN `privileges` ON (`permissions`.`parent_id` = `privileges`.`id`) AND (parent_type = "privilege")

SELECT * FROM `roles` WHERE (`id` IN ('3', '1', '4', '2', '25')) AND (parent_type = "role")
SELECT * FROM `privileges` WHERE (`id` IN ('5')) AND (parent_type = "privilege")

WHY?!!!

Makes no sense to add AND (parent_type = "role") and AND (parent_type = "privilege") to the following relation queries

[klimov-paul]

See #4938

[yomexzo]

Hey @klimov-paul , i do not understand how this is a duplicate.

You mind saying more?

[klimov-paul]

The code you complain on was created as a fix for #4938.

[yomexzo]

Right! And now it causes this. I thought you closed this issue because it was "solved" already and was pointing me to where and how to fix it.

[klimov-paul]

You have asked 'WHY?!!!' - here is your answer

how to fix it

I can't see anything to be fixed.

[yomexzo]

:) NIce one. My bad, i actually need a way to get this sorted. It is blocking me from achieving greatness in the project.

I need to be able to get the hasOne relation without the onCondition duplicated in the where clause

The below is what I came up with. It will only work if i lazyload. But that's not good enough because of performance issues.

public function getParent() {
    if ($this->parent_type == 'privilege') {
        return $this->hasOne(Privilege::className(), ['id' => 'parent_id']);
    } else {
        return $this->hasOne(Role::className(), ['id' => 'parent_id']);
    }

}

Allow me to rephrase. How do I get this to work / achieve this?

Thanks.

[yomexzo]

@klimov-paul ?

[cebe]

@yomexzo you can not define one relation differently based on an attribute value of one record. for a relation to work in eager loading it must be the same definition for all records.

There is no way to define whether a JOIN to some table should happen on a per record basis, you can only write a join per query.

[yomexzo]

elm @cebe .... what i'm trying to do is not out of this world. I am simply trying to add a join onCondition. It is valid in SQL.

I should ask you all - why is the link attribute of the hasOne, which is essentially a onCondition not being added to the where just like the explicit onCondition.

Thankfully, Yii optimizes fetching relation with eagerloading by making a second query for the joinWith. Problem here is the onCondition is being added. The onCondition should have nothing to do with the second query (for the with a.k.a relation) in reality.

Except i'm missing something.

This is what i have fixed with - https://github.com/yiisoft/yii2/pull/8507

[cebe]

I should ask you all - why is the link attribute of the hasOne, which is essentially a onCondition not being added to the where just like the explicit onCondition.

It is. For eager loading we just do not check whether they are equal to one value but to multiple values: `id IN ('3', '1', '4', '2', '25')` is what is specified by the link.

The onCondition should have nothing to do with the second query (for the with a.k.a relation) in reality.

the onCondition is added so that you have the same result in both cases. eager and lazy loading:

// lazy
$model = Record::find()->where(['id' => 1])->one(); // first query
$model->myRelation; // second query

// eager
$model -> Record::find()->with('myRelation')->where(['id' => 1])->one(); // two queries
$model->myRelation; // no query but exact same data as above.

[yomexzo]

@cebe You clearly still do not understand me

For eager loading we just do not check whether they are equal to one value but to multiple values: id IN ('3', '1', '4', '2', '25') is what is specified by the link.

You are correct. But what you specify is hasOne(CLASSNAME, ['id' => 'relation_id']). The actual id for relation is gotten from first query to have something like id IN ('3', '1', '4', '2', '25') in second query. id = relation_id is not the one added.

With this, If you do hasOne(CLASSNAME, ['id' => 'relation_id'])->onCondition(['type' => new Expression['"special"']]) and a joinWith, the onCondition should only be used in the join (first query). Then only id from first query used in second query. Still only id IN ('3', '1', '4', '2', '25'). Not id IN ('3', '1', '4', '2', '25') AND type = "special"

Please do you get what I am trying to explain now? I am sorry if I am not explaining well.

[cebe]

I am afraid this is getting to theortical and I do not get your point, sorry. Please explain what you are trying to do on a concrete example and include the yii syntax and also how you would fetch what you need with plain SQL.

[cebe]

not sure if that is maybe too much but if you would come up with a failing unit test for your change that could show off the error best.

[klimov-paul]

Then only id from first query used in second query. Still only id IN ('3', '1', '4', '2', '25'). Not id IN ('3', '1', '4', '2', '25') AND type = "special"

This is not correct. You may have a record in related table, which matches id = 3, but missmatch type = "special". If later part will be omitted you will have a populated related record, while it should not be one,

[yomexzo]

Thank you @klimov-paul .

Were the ids not gotten from query 1 result?

[yomexzo]

Thank you @cebe . Please read my original post. That is exactly what I am trying to do.

Summary

I am implementing a RBAC system similar to \yii\rbac but different because of our specific requirements.

I have

1. Privilege (what yii calls Permission)
2. Role (yii also calls Role)
3. Permission (what yii uses auth_assignment for)

Permission table schema is [parent_type, parent_id, child_type, child_id]. RBAC says a Role can be assigned to a Role, Privilege assigned to a Role and Privilege assigned to another Privilege. But not Role to Privilege. (I am using my terms here) This is why i have that schema for Permission.

Now permission can join to Role or Privilege based on type.

In trying to get Role object when type = "role" and Privilege when 'type = "privilege"` for child and parent

You understand?