Search/Order Calculated field

[jvinolas]

Hi,

I've been searching the forum but couldn't find a solution that allows to search and order by a calculated field.

This is fields.ini sql sentece to create a grafted field:

Code: Select all

__sql__ = "select *, ID as TOTAL from contractes order by contractes"

This is application delegate specific functions:

Code: Select all

function TOTAL__renderCell(&$record){
   return '<a href='.$record->getURL('-action=related_records_list&-relationship=contractes_detall').'>'.$this->getRecordSum($record).' €</a>';
}

function TOTAL__display(&$record){
   return $this->getRecordSum($record);
}

My functions getRecordSum() returns a string value with format "1.234,56 €", that I know it is unsearcheable. I've tried returning the number in the same format as mysql does: "1234.56", but none of them worked.

Also I'm missing something about renderCell and display. When and how to use each of them?

In my tests I can only order it by the underlying field 'ID' if I try to order it by TOTAL. I've also tried to create a 'blank' field in mysql and change de sql sentence as 'NULLFIELD as TOTAL', but then it can't be ordered because de underlying field is null.

Thanks in advance!

[shannah]

sorting is done in mysql so it doesn't take into account __display() methods etc... In your case it would be sorting on the value of ID.

Unfortunately since Xataface's SQL parser currently doesn't like raw arithmetic in SQL queries, it can be tricky to get a numerically calculated field to be sortable (and searchable). If you could some how rig it up to create another table to store your calculations on demand (or smartly ahead of time) you could to a join on said table....

Other workarounds I'm sure, but you have to be creative.