I'm sure all this has done the rounds before, but I cannot find the tickets. So I have raised them again:
Improve joining in query of query (ColdFusion | Railo)
CFML's QoQ is very limited in how it can join queries:
- only two queries
- only old-school filter joins (eg: from q1, q2 where q1.pk=q2.fk)
- no outer joins
This implementation is lovely for a proof of concept, but it's not really production-ready. And it's been like this since forever.
It's about time QoQ got a second tranche of development.
Improve scalar functions in query of query (ColdFusion | Railo)
QoQ has, I think, only these functions:
avg()count()max()min()sum()upper()lower()
Not bad for a first try (although the casing ones didn't even make it in the first try, I think?), but there are really a lot of scalar functions an SQL implementation ought to support.
Obviously Adobe are not in the business of creating a DB platform, but as a minimum a few ought to be added:
* time part extraction like
year() / month() / day() / hour() / minute() / second()*
datediff() / dateadd()*
substring() / replace() / trim()
Add CASE construct to query of query (ColdFusion | Railo)
As per the ANSI standard, eg: http://en.wikipedia.org/wiki/SQL#Conditional_.28CASE.29_expressions
These are non-theoretical requirements: I've needed them all in the past, and due to QoQ's lack of them, it's invalidated it as part of a solution.
As I state in the first ticket, I think query of query is a fine proof of concept, but it's never really moved on from there. And it's been around for over a decade now and has received very little attention. It's time that was changed.
Thoughts / votes / comments?
--
Adam