ActiveDataProvider & ListView - filtering resultset with pagination

Yii 2.0 General Discussions
1

I want to filter a resultset after it has been fetched. The reason for this is that I have a "price" field which is generated in real time (based on current currency rate and other calculations). So what I have done is a standard ActiveDataProvider and then I have used array_filter PHP function to filter this resultset.

This displays correctly in my ListView widget, however the filtering is only working on the initial set of results, i.e. in my case 30 items.

Here is my code:


$query = $this->find();


$dataProvider = new ActiveDataProvider([

    'query' => $query,

    'pagination'=> ['pageSize' => 30],

]);


$query->andFilterWhere([

    '<=', 'weight', $this->weight,

]);


// get the models from the dataprovider

$results = $dataProvider->getModels();


// create empty array to hold items

$items = [];


foreach($results as $item)

{

    $data = [

        'item' => $item,

        'category' => $item->category,

        'price' => $item->getPriceCalculated(),

    ];


    $items[] = $data;

}


// filter results on price

$items = array_filter($items, function($a) {

    return $a['price'] >= $this->price_min && $a['price'] <= $this->price_max;

});


// assign modified items array to dataprovider

$dataProvider->setModels($items);


return $dataProvider;

Anybody know how I can use ActiveDataProvider and still use pagination / sorting etc with the custom filtered resultset?

2

I see two options:

  1. Fetch all rows, filter them with custom function and then pass them to an ArrayDataProvider;

  2. Try to apply custom filter to ‘query’ attribute of ActiveDataProvider;

I prefer the last option.

3

Option 2 - does that not require database column to query?

4

You should try to transformate getPriceCalculated content and next filter in a sql filter. Explain what getPriceCalculated contains.

5

getPriceCalculated() function simply returns an integer value (of the price).

In this function there are some calculations carried out by using the product category code and tax code - this data is sent to an external API which returns a new code that I use to work out the price. This is why I can’t have the price in the database column - it must be calculated in real time.

Can you give me an idea of a sample code to perform the transformation?

6

If getPriceCalculted is so complex, I think that only way you can use is to fill all models, otherwise you can load data by step.

For example, if you need to 30 records for page, and after price filter you have 25 records, you can take other 30 records and fill the first block until it reachs 30 records.

It is complex, but it works without load all data.