Skip to content

Sohel Ahmed [Work in progress]

Sorting calculated columns in Yii2

PHP, Yii 2, MySQL, PostgreSQL, Sorting, sdofjhsd9f79s7d9f789sd7f9g934tioh23il4297w7fguso2 min read

TODO remove unwanted tags

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.

idnameradius
1Table tennis2
2Cricket4
3Football10

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.

idnameradiusvolume
1Table tennis233.51
2Cricket4268.08
3Football104188.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

iduser_idcreated_at
112010-12-12 00:00:00
222010-12-13 00:00:00
332010-12-14 00:00:00

Order Items

idorder_idproduct_idquantity
1111
2211
3221
4232
5331
6311

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

Products

idnameprice
1mobile10000
2keyboard600
3mouse200

So orders with their total amount will be:

order_idtotal_amountitems
1100001 mobile (10000)
2110001 mobile (10000) + 1 keyboard (600) + 2 mouse (2*200))
3102001 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

iduser_idtotal_amount
2211000
3310200
1110000

Versions and Links

Yii: 2.0.X

PostgreSQL: 12.3

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


Previous: Adding "blog" in URL in Gatsby Theme Minimal Blog by LekoArt

Next: Authenticate Laravel app with user data stored in JSON file

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