Sohel Ahmed Mesaniya

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

Copycopy code to clipboard
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:

Copycopy code to clipboard
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.

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.

Copycopy code to clipboard
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

Order Items

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

Products

So orders with their total amount will be:

Now sorting on total_amount is needed. First the target is to only create SQL query which give total_amout

The SQL query is:

Copycopy code to clipboard
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.

Copycopy code to clipboard
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

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