yii2 - filtering model by default with joined table

Question

I actively develop in Yii2. And I have a question. I didn't find any information about that.

My question about filtering model by default. For example, I have a categories and items that assign to category. And not any users can access all categories. When category are creating, there an option where you can set access for users.

So, how can I set a global filter for categories model? That, wherever I requesting category list - I'll recive it according user access. Of course I can write my own method insteed of find () ->, findAll () ... But maybe there an another solution, more canonical for yii2

Thanks for your help!

Updated #1

Sulotion with override find() method. But problem with validateAttributes() method

public static function find()
{
    $query = new ProjectsQuery(get_called_class());

    $query->alias('p')
        ->leftJoin(ProjectsAccessibility::tableName().' pa','pa.project_id=p.id')
        ->andWhere(['like','pa.user_id',Yii::$app->user->id]);

    return $query;
}

Updated #2

Using joinCondition() But still catching 1052 Column 'id' in where clause is ambiguous

public static function find()
{
    $query = new ProjectsQuery(get_called_class());

    $query->joinWith([
        'projectsAccessibility' => function($query){
            $query->onCondition(['user_id' => Yii::$app->user->id]);
        },
    ]);

    return $query;
}

Updated #3

Let me explain about my issue in more detail. I'm creating a project manager system. So, I have list of projects and tasks which assigned to project. And a have one more table with access information between users and projects:

Projects
id | name
1    Project 1
2    Project 2

Tasks
id | project_id | name
1          1      Task 1 
2          2      Task 2

Users
id | name
1    User 1 
2    User 2

Project Access
id | user_id | project_id
1      2           1
2      2           2 
3      1           1

So, what I trying to do is show project and tasks list according to authorized user_id, and it's working now. But as I override find() method with join table, when I trying create a new task I got mysql error, coz during save() yii2 checking all attributes if exist, but it's doesnt know about joined table in find() method with same column name - id

Full error message:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous
The SQL being executed was: SELECT EXISTS(SELECT tm_projects.* FROM tm_projects LEFT JOIN tm_projects_accessibility ON (tm_projects.id = tm_projects_accessibility.project_id) AND (user_id=37) WHERE id='1')

Salem Ouerdani Salem Ouerdani · Accepted Answer · 2017-01-24T12:10:07

You can always override the find() method inside your model class:

public static function find()
{
    return parent::find()->onCondition(['owner_id' => Yii::$app->user->id]);
}

Alternatively if you have overridden it already to use an ActiveQuery class like:

public static function find()
{
    return new \app\models\CategoryQuery(get_called_class());
}

Then you can use that class's init() method to define the default filter:

class CategoryQuery extends \yii\db\ActiveQuery
{
    public function init()
    {
        $this->andOnCondition([['owner_id' => Yii::$app->user->id]]);
        parent::init();
    }
}

yii2 - filtering model by default with joined table

1 Answers