Yii2 data filtering in queries

Question

In my Yii2 project I have posts table linked to categories table as many-to-many relation (posts_categories table). In my Post model I have getByCategory($category_id) function which returns all posts of the category. In controller I have actionCategory where I use this function and pass all posts for certain category. I also have a GET form in the view for filtering my posts through GET parameter (I need this value to be contained in title or content of posts shown). The problem is I don't have an idea how to apply some filter function to my getByCategory call in controller in a smart way. My function code:

public static function getPostsByCategory($category_id = null)
{
    $posts = Post::find()
        ->select('posts.*')
        ->innerJoin('posts_categories', '`posts`.`id` = `posts_categories`.`post_id`')
        ->where(['posts_categories.category_id' => $category_id])
        ->orderBy(['date_create' => SORT_DESC])
        ->all();
    return $posts;
}

Controller action:

public function actionCategory($id)
{
    $posts = Post::getPostsByCategory($id);
    return $this->render('index', array('all_posts' => $posts));
}

All my ideas like using statements like "if $_GET is not empty - use one query - if empty - another one" in controller or in model does look messy and lead to duplicating code in other actions where I'll also need my $_GET filtering. Could you please advice something? Thanks.

Mortie · Accepted Answer · 2018-12-10 11:26:48Z

Please, for work with db queries use ActiveQuery. It's simple and more effective.

For your simple, create Class

class PostQuery extends \yii\db\ActiveQuery
{
    public function byCategory($id){
        $junction_table = '{{%posts_categories}}';
        $this
            ->innerJoin($junction_table, Post::tableName()'.id='.$junction_table.'.post_id')
            ->where([$junction_table.'.category_id' => $id]);
    }

    public function orderByDateCreated($sort_type = SORT_DESC){
        return $this
            ->orderBy(['date_create' => $sort_type]);
    }

    /**
     * @inheritdoc
     * @return Post[]|array
     */
    public function all($db = null)
    {
        return parent::all($db);
    }

    /**
     * @inheritdoc
     * @return Post|array|null
     */
    public function one($db = null)
    {
        return parent::one($db);
    }
}

Add find method to Post model:

public static function find()
{
    return new PostQuery(get_called_class());
}

For search and filters use PostSearch model, extended from Post model.

class PostSearch extends Post
{
    public $category_id;

    public function rules(){
        return [
            ['category_id', 'integer']
        ];
    }

    public function search($params = []){

        $query = Post::find();

        $query
            ->orderByDateCreated();

        $dataProvider = new ActiveDataProvider([
            'query' => $query
        ]);

        if( !($this->load($params) && $this->validate()) ){
            return $dataProvider;
        }

        if($this->category_id)
            $query->byCategory($this->category_id)

        return $dataProvider;
    }
}

In action controller with search

public function actionIndex(){
    $searchModel = new ArticleSearch();

    $dataProvider = $searchModel->search(\Yii::$app->request->post()); //data from filter form

    return $this->render('index', compact('dataProvider'));
}

without ActiveDataProvider

public function actionIndex(){
    $searchModel = new ArticleSearch();

    $query = Post::find();

    $query
        ->orderByDateCreated();

    if($searchModel->load(\Yii::$app->request->post()) && $searchModel->validate()){
        if($this->category_id)
            $query->byCategory($this->category_id)
    }

    $posts = $query->all();

    return $this->render('index', compact('posts'));
}

this returns ActiveDataProvider, right? What if I have to display it not in GridView or ListView? Thanks
Yes, but If you work with large data sets, it is best to use ActiveDataProvider. I've updated my answer.

Collectives™ on Stack Overflow

Yii2 data filtering in queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related