Skip to content

Sohel Ahmed [Work in progress]

Sorting calculated columns in Yii2

PHP, Yii 2, MySQL, PostgreSQL, Sorting2 min read

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
# PHP
# Proportionality
# Database View
# Versions and Links

Calculated column

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 * quantity
2+
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.

PHP

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;
2
3$data = [
4    ['id' => 1, 'name' => 'name 1', ...],
5    ['id' => 2, 'name' => 'name 2', ...],
6    ...
7    ['id' => 100, 'name' => 'name 100', ...],
8];
9
10$provider = new ArrayDataProvider([
11    'allModels' => $data,
12    'pagination' => [
13        'pageSize' => 10,
14    ],
15    'sort' => [
16        'attributes' => ['id', 'name'],
17    ],
18]);
19
20// get the rows in the currently requested page
21$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.

Proportionality

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.

id  name        radius
1  Table tennis     2
2  Cricket           4
3  Football        10

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.

id  name        radius volume
1  Table tennis2        33.51
2  Cricket      4       268.08
3  Football    10    4188.79

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];

Database View

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:

Order

id  user_id         created_at
1  1      2010-12-12 00:00:00
2  2      2010-12-13 00:00:00
3  3      2010-12-14 00:00:00

Order Items

id  order_idproduct_idquantity
1  1        1                 1
2  2        1                 1
3  2        2                 1
4  2        3                 2
5  3        3                 1
6  3        1                 1

An order can contain one or more products and each product can have one or more quantities.

Products

id  name    price
1  mobile  10000
2  keyboard  600
3  mouse      200

So orders with their total amount will be:

order_idtotal_amount                                                 items
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:

1select
2 ord.id,
3 ord.user_id ,
4 (
5 select
6 sum(oi_in.quantity * prd_in.price)
7 from
8 orders ord_in
9 left join order_items oi_in on
10 ord_in.id = oi_in.order_id
11 left join products prd_in on
12 oi_in.product_id = prd_in.id
13 where
14 ord_in.id = ord.id ) total_amount
15from
16 orders ord
17order by
18 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 AS
2select
3 ord.id,
4 ord.user_id ,
5 (
6 select
7 sum(oi_in.quantity * prd_in.price)
8 from
9 orders ord_in
10 left join order_items oi_in on
11 ord_in.id = oi_in.order_id
12 left join products prd_in on
13 oi_in.product_id = prd_in.id
14 where
15 ord_in.id = ord.id ) total_amount
16from
17 orders ord
18order by
19 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

id  user_idtotal_amount
2  2             11000
3  3             10200
1  1             10000

Versions and Links

Yii: 2.0.X

PostgreSQL: 12.3

SQL (Postgre) for above schema and data can be found at Gist at GitHub.com: https://gist.github.com/SOHELAHMED7/1a8429d57f9f264eb40b6cfce87439f6

© 2020 by Sohel Ahmed [Work in progress]. All rights reserved.
Theme by LekoArts