Virtual Property sorting

[mikk0s]

Hello!

First of all great plugin! Loving it so far. But I have a question that I couldn't find an answer for;
Is there any way I can order the columns by virtual properties?

Currently I have created methods for virtual properties (that I don't need to store in my DB, only show in my table ie. total price of products) that work great but once I try to order the columns it gives me an error Unknown column 'price_total' in 'order clause' since the column doesn't exist in my DB only in the virtual field.

Thanks in advance!

[ypnos-web]

You can use delegateOrder option to DataTablesComponent and then in your table's finder, process the customOrder option to fulfill the ordering request.

Or you could go without server-side processing so DataTables will order on the client. However this is a global option, not per-column.

[mikk0s]

Ok thanks for the quick reply! I'll try those!

[Lurtz963]

Recently I came across this same problem of searching a ordering by virtual fields in DataTables. I'll let a code example for anyone having problems with this. Basically you pass a custom finder to the datatablesComponent with the options delegateSearch and delegateOrder on true like this:
$data = $this->DataTables->find('TableName' , 'datatables' 'delegateSearch' => true, 'delegateOrder' => true] , $columns);
In my case I called the finder 'datatables', so now in the model/Table file of my table I declare the finder like this:
public function findDatatables(Query $query, array $options) { $search = ''; if(!empty($options['globalSearch'])){ $search = $options['globalSearch']; } $order = []; if(!empty($options['customOrder'])) { $order = $options['customOrder']; } return $query->where( //conditions )->order($order); }

Then you take the globalSearch and customOrder from the options array and do whatever query you want