sort on calc'ed element in list

[skyrun]

sorts the list perfectly, but the sort based on what it stored in db, which is not equal to the value calculated in the list.

looks like the calc-ed value only gets updated on the table when you open and save the row.

perhaps this is 'working as designed...'.

my calc includes calculations based on many other tables and events external to the tables (like current time and date), so sorting based on the on-the-fly calculation is necessary (whether or not the calc-ed value is actually stored on the table). any workaround anyone can think of? sounds like a change in the way calc-ed elements are sorted when viewed in a list would be needed.

 

[rob]

we can only order based on what's in the database. I can't think of a way around that.
Imagine you have half a million records, if you wanted on the fly ordering, you would need to load them all, perform the calculations, then sort - which is going to be very very very slow

 

[cheesegrits]

We only support ordering via MySQL, i.e. using "ORDER BY ...". Otherwise, as Rob says, we'd have to read every row in the table in to PHP, and sort it in memory.

And yes, that is how the calc is designed - it calc's before storing to the table. It can also can when being displayed (setting "Calc on Save Only" to No), but as you have no doubt observed, it'll still use the value in the table for the ordering.

The only possible alternative would be to use if a MySQL view, if the logic in your calc can be structured in to a SELECT query, and create the field you currently do as a calc using SELECT logic in the view. Which if it's just pulling data from other tables and using date/time, should be possible.

If you went that route, you'd probably end up having to have two lists, one based on the view (which would be read only) and one based on the actual table, so you can add / edit rows using it.

-- hugh