I have a fairly complicated sorting function I need to use that involves several joins and a bit of math. Because of this, there's no way to incorporate this into the standard DataMapper syntax (such User.all(:order => ...) ). Currently I have something like
repository(:default).adapter.select('
SELECT users.id, ( (count(table1.*) - count(table2.*))/((EXTRACT(EPOCH FROM current_timestamp)) ) as sort FROM users
LEFT JOIN table1
ON table1.user_id = users.id
LEFT JOIN table2
ON ...
AND ...
GROUP BY users.id
ORDER BY sort DESC')
However, I want to integrate this into DataMapper such that I can call this on any datamapper collection
For example, User.all(:name => 'bob').crazy_sort_function returns users named 'bob' sorted by my special function, or perhaps User.all(:order => crazy_sort), etc...
Whatever formulation integrates this as seamlessly into the datamapper ORM as possible.
Note: since current_timestamp is part of the query, this isn't something I can compile for all rows periodically and fit into a column.
