AR Default scope generates WHERE condition in relations.

[radzserg]

Right now we have

  public function hasMany($class, $link)
    {
        /** @var ActiveRecordInterface $class */
        /** @var ActiveQuery $query */
        $query = $class::find();

// Some model with default scope (Class User) 
public static function find()
    {
        return parent::find()->where('user.deleted = 0');
    }

therefore when we join model with default scope we have default condition in WHERE instead of ON condition. If we use LEFT join query doesn't work as expected.

SQL example

SELECT COUNT(*) 
FROM `user` LEFT JOIN `document` ON `user`.`id` = `document`.`user_id`
WHERE document.deleted = 0

LEFT JOIN doesn't work in this case cause

[qiangxue]

This is how it is designed to work. Yii doesn't know when it should magically put WHERE to ON, and it shouldn't do so.

[cebe]

you can use onCondition() instead.

[radzserg]

Yes but in this case I do the following

public function getDocuments()
    {
        $query = $this->hasMany(Document::className(), ['user_id' => 'id']);
        $query->onCondition($query->where);
        $query->where = null;
        return $query;
    }

Doesn't looks great to me

[cebe]

I mean in find() you can use onCondition() instead of where()

[radzserg]

correct, but I still need to remove WHERE condition from result sql
SELECT *
FROM user LEFT JOIN document ON user.id = document.user_id
WHERE document.deleted = 0
such query will work as INNER JOIN if document is empty I will get 0 rows.

[cebe]

what code generated you that query? looks wrong to me as document.deleted should be in ON when specified via onCondition.

[radzserg]

Ok say I have 2 models User and Document. User has default scope
https://github.com/yiisoft/yii2/blob/master/docs/guide/db-active-record.md#default-scope

class User extends ActiveRecord
{
    /**
     * @return \yii\db\ActiveQuery
     */
    public static function find()
    {
        return parent::find()->where('user.deleted = 0');
    }
}


class Document extends ActiveRecord
{
   /**
     * @return \yii\db\ActiveQuery
     */
    public function getUser()
    {
        // yes I can add onCondition here but where will be left
        return $this->hasOne(User::className(), ['id' => 'user_id']);     
    }
}

// then look into hasOne hasMany 
public function hasOne($class, $link)
    {
        $query = $class::find();                   // this will call User::find() and here we get query with default where
        $query->primaryModel = $this;
        $query->link = $link;
        $query->multiple = false;
        return $query;
    }

[cebe]

The solution would be to overwrite find() like this:

    public static function find()
    {
        return parent::find()->onCondition('user.deleted = 0');
    }

this will add 'user.deleted = 0' to ON when joining the relation and to WHERE when just finding users. We should add this to the docs.

[radzserg]

Thanks!

[samdark]

@cebe any suggestion about where to document it?

[cebe]

this is more like a cookbook things than something for the guide...

[samdark]

Added as an issue to cookbook repo. Closing this one as we're not going to add it to the guide.