Get dataprovider with search in related table

Maybe what I need to do have a simple approach but I’m not finding it.

I have 3 tables

[list=1]

[*]posts

[*]post_categories

[*]categories

[/list]

The posts and categories are related using the table post_categories that have 2 columns, post_id and category_id

I want to retrieve the posts that contain the category "red", and have the status field(this is a field on posts table) set "active".

How can I get this?

This is my code right now, how can I include to show only the posts with the "red" category




$dataProvider = new ActiveDataProvider([

            'query' => Posts::find()->where([status' => Posts::STATUS_PUBLISHED]),

        ]);



In Post models you should have these reletions:




public fuction getPostCategories()

{

     return $this->hasMany( PostCategories::className(), [ 'post_id' => 'id' ] );

}


public fuction getCategories()

{

     return $this->hasMany( Categories::className(), [ 'id' => 'category_id' ] )

     ->via('postCategories');

}



So in $dataProvider




$dataProvider = new ActiveDataProvider([

            'query' => Posts::find()->innerJoinWith('categories')->where([ 'categories.name' => 'red', 'status' => Posts::STATUS_PUBLISHED]),

        ]);



Perfect you solve my problem. it’s so damm simple.

Thank you Fabrizio Caldarelli

Right! Yii is damm clear! :)