Sorting the calculated column in the grid might be challenging. From PHP array to database view which approaches to choose and when, are uncovered.
Calculated column or field is the column shown in grid view having value not directly stored in relational database, but rather calculated at runtime in PHP. For example, if there is a column "Total Amount" in the "Orders" grid that show total cost of purchased items by
1price of one item * quantity2+3similarly add other items
and the "Total amount" is not directly stored in any column of any table in the database, then
total_amount is the calculated column.
"Users" grid showing "First Name" that is directly stored in the
first_name field/column in the
users table in the database is not a calculated column.
If there are comparatively less records to show in grid (for example less than 250) and it will remain finite in future and its grid contains calculated column, sorting can be done within PHP avoiding ordering from database side or database view.
It can be achieved by loading all the records in the PHP array and hence stored in memory (RAM) and performing sorting on it.
Yii provides amazing in-built way to pass data stored in array to grid by Array Data Provider
Quoting from the Yii docs, it can be achieved by:
1use yii\data\ArrayDataProvider;23$data = [4 ['id' => 1, 'name' => 'name 1', ...],5 ['id' => 2, 'name' => 'name 2', ...],6 ...7 ['id' => 100, 'name' => 'name 100', ...],8];910$provider = new ArrayDataProvider([11 'allModels' => $data,12 'pagination' => [13 'pageSize' => 10,14 ],15 'sort' => [16 'attributes' => ['id', 'name'],17 ],18]);1920// get the rows in the currently requested page21$rows = $provider->getModels();
This way can be used for config table which contain key-value and have comparatively less number of records and if the config grid has calculated column.
Sorting a particular calculated column can easily be done if its value is directly proportional to other column stored in the database avoiding PHP array and database view. For example, consider a table containing several radiuses of different balls such as table-tennis, cricket, football etc.
Once the radius is known it's volume can be calculated by formula 4/3 * π * r * r * r.
Here the radius is directly proportional to volume of ball. If the radius increases the volume increases, and if the radius decreases the volume decreases.
So a calculated column "volume" in the above grid is added which shows the volume of the ball. And to add the ability to sort on volume column, radius can be used.
1$dataProvider->sort->attributes['volume'] = [2 'asc' => ['radius' => SORT_ASC],3 'desc' => ['radius' => SORT_DESC],4];
For complex calculated columns, database view can be used. View is a kind of virtual table. Result set of any SELECT query can be made to its own table. Consider example:
An order can contain one or more products and each product can have one or more quantities.
So orders with their total amount will be:
|1||10000||1 mobile (10000)|
|2||11000||1 mobile (10000) + 1 keyboard (600) + 2 mouse (2*200))|
|3||10200||1 mobile (10000) + 1 mouse (200)|
Now sorting on
total_amount is needed. First the target is to only create SQL query which give total_amout
The SQL query is:
1select2 ord.id,3 ord.user_id ,4 (5 select6 sum(oi_in.quantity * prd_in.price)7 from8 orders ord_in9 left join order_items oi_in on10 ord_in.id = oi_in.order_id11 left join products prd_in on12 oi_in.product_id = prd_in.id13 where14 ord_in.id = ord.id ) total_amount15from16 orders ord17order by18 total_amount asc
And once writing SELECT query is completed, it's view can be easily generated by CREATE VIEW statement.
1CREATE OR REPLACE VIEW order_with_total_amount AS2select3 ord.id,4 ord.user_id ,5 (6 select7 sum(oi_in.quantity * prd_in.price)8 from9 orders ord_in10 left join order_items oi_in on11 ord_in.id = oi_in.order_id12 left join products prd_in on13 oi_in.product_id = prd_in.id14 where15 ord_in.id = ord.id ) total_amount16from17 orders ord18order by19 total_amount asc
Once the view is made, it's search model can be made and thus sorting calculated column in grid can be accomplished.
Orders with total amount
SQL (Postgre) for above schema and data can be found at Gist at GitHub.com: https://gist.github.com/SOHELAHMED7/1a8429d57f9f264eb40b6cfce87439f6