How to cache active dataProvider ?

Yii 2.0 General Discussions
1

In my PostSearch model I have this code :


    public function search($params)

    {

        $query = Post::find()->where(['status' => 1]);


        $dataProvider = new ActiveDataProvider([

            'query' => $query,

            'sort'=> ['defaultOrder' => ['id' => SORT_DESC]],

            'pagination' => [

                'pageSize' => 10,

            ]

        ]);


        if (!($this->load($params) && $this->validate())) {

            return $dataProvider;

        }


        $query->andFilterWhere([

            'id' => $this->id,

            'status' => $this->status,

        ]);


        $query->andFilterWhere(['like', 'title', $this->title])

            ->andFilterWhere(['like', 'text', $this->text]);


        return $dataProvider;


        // my try, instead of above line "return $dataProvider", would be this block of code:


        // $dependency = [

        //     'class' => 'yii\caching\DbDependency',

        //     'sql' => 'SELECT MAX(updated_at) FROM post',

        // ];


        // $result = self::getDb()->cache(function ($db) {

        //     return $dataProvider;

        // }, 3600, $dependency);


        // return $result


    }

I would like to cache the result returned by ADP, based on the updated_at field. I mean I want to serve data from cache until some change is made. My code try is this commented out block of code. It does not work, I mean caching is not applied at all. What I am doing wrong, and is it possible to do this on ADP ? Thanks

2

I have figured out that dependency need to be set like this, because it will not work like I did it in original post:


$dependency = new \yii\caching\DbDependency(['sql' => 'SELECT MAX(updated_at) FROM post']);

But I still do not know how to cache returned result. No1 knows ?

3

Are you absolutely sure you want to cache the result of all possible combinations of pagination, sorting and filtering? That might result in a huge amount of cached data and might not be very effective.

4

So what is the alternative, if I have to pull data from tables with millions of records ( and I do ) ? Am I able to cache query that is pulling all those records before pagination, sorting and filtering is applied ?

5

Query optimization and strategic use of indices. As a first step, i’d try to remove this part




$query->andFilterWhere(['like', 'title', $this->title])

  ->andFilterWhere(['like', 'text', $this->text]);

and replace it with a full-text index/search (actual implementation depends on your DBMS). LIKE conditions can be very inefficient on large tables especially when a column contains long texts (which is likely the case for a column named "text").