Table Alias In "default Scope"

Yii 2.0 General Discussions
1

I’m not able to figure out how to handle table aliases in a “Default Scope” in Yii 2.

I need the Default Scope to exclude soft-deleted rows from query results. In Yii I’ve used defaultScope() in a following way:


public function defaultScope()

{

    return array(

        'condition' => $this->getTableAlias() . '.deleted_at IS NULL',

    );

}

In Yii 2 I’ve overridden find() method according to docs (using a tip from cebe):


public static function find()

{

    return parent::find()->onCondition('[[' . static::tableName() . ']].[[deleted_at]] IS NULL');

}

It works fine as long as the table is not aliased. However I need to use aliases since I have more foreign keys referencing the same table.

Do you have any tips for a clean solution in Yii 2?

2

To be more specific I’m posting excerpts from my classes:


class Order extends \yii\db\ActiveRecord

{

...

	public function getAgentPerson()

    {

        return $this->hasOne(Person::className(), ['id' => 'agent_person_id'])->from(['agent_person' => Person::tableName()]);

    }


    public function getClientPerson()

    {

        return $this->hasOne(Person::className(), ['id' => 'client_person_id'])->from(['client_person' => Person::tableName()]);

    }

...

}


class Person extends \yii\db\ActiveRecord

{

...

    public static function find()

    {

        return parent::find()->onCondition('[[' . static::tableName() . ']].[[deleted_at]] IS NULL');

    }

...

}

It work well in simple query:


$persons = Person::find()->all();

Problem arises when the Person table is joined in a query.


$query = Order::find();


$query->joinWith(['agentPerson', 'clientPerson']);

Instead of statically defined table name a contextual alias would be needed.

Actually, in these particular join queries the condition is kind of redundant since Person record shouldn’t be deleted as long as any related records exist. Could it be that a proper solution resides in eliminating the default condition from join queries?

3

I’ve realized I can redefine the onCondition in the relation declaration where the alias is configured:


class Order extends \yii\db\ActiveRecord

{

...

    public function getAgentPerson()

    {

        return $this->hasOne(Person::className(), ['id' => 'agent_person_id'])

                        ->from(['agent_person' => Person::tableName()])

                        ->onCondition('[[agent_person]].[[deleted_at]] IS NULL');

    }


    public function getClientPerson()

    {

        return $this->hasOne(Person::className(), ['id' => 'client_person_id'])

                        ->from(['client_person' => Person::tableName()])

                        ->onCondition('[[client_person]].[[deleted_at]] IS NULL');

    }

...

}

Would you consider this a proper solution?