Nested SQL function calls on related field with DataMapper?

[eluser]Dracos[/eluser]
I've got a query that needs to call multiple SQL functions on a related field. The function calls amount to doing a timezone shift on the field, which is stored as a unix timestamp, then limiting the results based on user input values that define the start and/or end of a date range. I'll use 'obj' as the current model and 'other' as the related model. The setup is CI 2.1.0 and DataMapper 1.8.2.

The direct, abridged query on 'other' where $start and $end are set is

Code:

Select
id, start_date
from
obj
where
(
date_format(convert_tz(from_unixtime(start_date), 'UTC', 'America/New_York'), '%Y-%m-%d')
between
'2013-08-05' and '2013-08-10'
)

Obviously I'll just use '>=' and '<=' instead of between when $start or $end are set. For that, this is what I think I need to do

Code:

$obj->where_related_field_func('other', '@other/start_date ' . ($start ? ' >=' : '<='), '(date_format(convert_tz(from_unixtime(@other/start_date), 'UTC', 'America/New_York'), '%Y-%m-%d'));

But I'm sure that's the wrong approach somehow, and that there's something I'm not seeing in the DataMapper docs.

[eluser]Dracos[/eluser]
Workaround: I've gotten around this by wrapping the date_format, convert_tz, from_unixtime calls in a stored function. But I'm sure lots of people would like to know the more direct DM solution.

I added two somewhat related stored functions a couple weeks ago, what's three more for this? Three because in various places I need the entire formatted date, and in others I just need the date or time portions.

[eluser]Dracos[/eluser]
Well, I got around the nested part by using stored functions. I'm still in a situation where I need to do WHERE ... BETWEEN ... FUNC in DM, but I can use WHERE ... <= AND WHERE >= instead, but I'd still rather use BETWEEN.

I realized that I need to query $other directly anyway; I can use that to get the $obj fields via include_related(), of course.

In the DM class, where_between_field_func is mentioned in a comment, but appears nowhere in the code. I've looked at DataMapper::__call(), but can't see how where_between_field_func could distinguish the function arguments from the BETWEEN operands.

Now I see that where_related_field_func() does not exist, and even if it did, __call() seems like it would return something unexpected. Doubly so for where_between_related_field_func.

However, this alternative approach will only solve this simple piece of the greater query, which actually involves up to 9 models and occasionally as many as 4 join tables.