Is there a way to sort the whole database table according to a calculated data

benily 0
Viewed 36

I have a very large table called user and it looks like this

id events (array) ... (extra columns)
1 [] ...
2 [] ...
... ... ...

When I query the table, I will pass two extra parameters, no_per_page and page so that only the data I want will be retrieved.

And before returning the data, an extra column called 'total_event_hours' will be calculated using the 'events' column from the query. However, because it is also a column and will be presented in a table of a front-end app, I want it also to be sortable.

The naive way I could think of will be to query the whole table and sort it after the calculation, but since it is very large, it will slow down the process.

Is there a way to sort the table using the calculated column and keep the pagination at the same time? Any input will be appreciated.

Edit:

$no_per_page = $param['no_per_page']
$start = $param['page'] > 1 ? ($param['page'] - 1) * $no_per_page : 0;

$query = "SELECT * FROM user LIMIT :start, :no_per_page"; 
$get_query = $this->db->prepare($query);
$get_query->bindParam(':start', $start);
$get_query->bindParam(':no_per_page', $no_per_page);
$get_query->execute();
$data = $get_query->fetchAll(PDO::FETCH_ASSOC);

foreach ($data as $_data) {
  $_data['total_event_hours'] = $this->_getTotalEventHours($_data['events']) 
  // I want to sort by this column but it is not presented in the table
}

return data;
mysql sql
1 Answers

To answer your question

Is there a way to sort the table using the calculated column and keep the pagination at the same time?

The DBMS cannot sort data by something it does not know. To sort by a calculated value, there are several possibilities, e. g.

  1. sometimes the result of the calculation is in the same order as a column. Then you can sort by this column, of course.
  2. you can rewrite your query to include a calculated column. That is, you must perform the calculation with SQL/a user defined function. You can then sort by the calculated column. Note that it's sometimes possible to get the same order with a simpler calculation.
  3. approach #2 most likely prevents the DBMS from using an index for sorting. To speed things up, you could modify your table to contain the calculated column, and then update its value using triggers. If you have this column, you can also index it.

As always, there's a trade-off, so it depends on the specific circumstances/use case which option to choose.

Mihe0
Related