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






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' ] )



So in $dataProvider

$dataProvider = new ActiveDataProvider([

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


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

Thank you Fabrizio Caldarelli

Right! Yii is damm clear! :)