We're trying to create a Squeryl query in our Play application that will order the results by a coalesce of two columns, such that when the first column is NULL, the second column will be used in the ordering. Both columns are of type DateTime.
So suppose we join two tables, Channel and ChannelSubscription, we want to order by the subscription endDate, which is nullable, and otherwise the channel lastUpdatedDate.
Order By
coalesce(q1.ChannelSubscription23_endDate,q1.Channel24_lastUpdatedDate)
We found dat the nvl function in Squeryl should do something like this, but it wants to calculate the values by querying first, creating a stackoverflow.
orderBy nvl(row._2.endDate, row._1.lastUpdatedDate)
(Where row._1 is the Channel and row._2 is the subscription)
Is there any way to just insert the coalesce into the query?
(We also tried to add a &() around the nvl)
Or should we go the custom function route?
