Is there a way, using the DataMapper::Query::Conditions and DataMapper::Query::Direction object structures, to set a WHERE clause condition and/or an ORDER BY clause according to a computed value?
In our User model, we have two date fields. Our users have requested the ability to filter and order by the number of years between those two dates. Due to speed concerns, along with the fact that the results are paged at the database level, this computation needs to be done at the database and not once the results are brought back to the RoR server.
I've tried the following as a proof-of-concept (using Integer fields) in the Rails console:
direction = DataMapper::Query::Direction.new(User.integer_field_2 - User.integer_field_one, :asc)
and it gives the following error:
NoMethodError: undefined method `-' for #<DataMapper::Property::Integer @model=User @name=:integer_field_2>
I'd rather not write the query in direct SQL, as (a) this is done as part of a generic method to sort and filter by (potentially) any property of the User object, and (b) we are trying to keep the ORM database-agnostic (although it is, at the moment, using PostgreSQL, so while it's the last-resort option, it is an option).
Any assistance would be greatly appreciated, even if it's "sorry, but direct SQL is the only way to go." Thanks.
