Query of queries outside ColdFusion

Question

Hello.

My experience of using Adobe ColdFusion, even if still somewhat limited, was absolutely joyful and pleasant.

Of all good things I could say about ColdFusion, one feature completely blew me off my feet. It might be neither very effective, or useful in production, but anyway, I am talking about the so-called "query of queries" feature, or the dbtype="query" attribute of cfquery. It allows you to run SQL statements against arbitrary datasets, not just a database connection. You can, for example, join a resultset, that you've just retrieved from the database and an in-memory structure (that is, of course, subject to certain limitations). It provides a quick-and-dirty way to kind of "post-process" the data, which can sometimes be much more readable (and flexible, too!), than, say, iterating through the dataset in a loop.

However, ColdFusion is not a very popular product and I am not going to go over the reasons why it is like that. What I am asking is, is there any support for this technique in other languages (like a library, that does more or less the same)? Python? Perl? Ruby? PHP? Anything? Because, to me it seems, that the potential of this feature is huge, maybe not in production code, but it is an absolute life-saver if you need to test something quickly. Needless to say, the SQL ColdFusion uses for this is somewhat limited, to my knowledge, but still, the idea is still great.

Answer 1

Lots of frameworks use object-relational mapping (ORM), which will convert your database tables to objects.

For example, using Rails you fetch data from a model instead of directly talking to the database. Queries, or finds, are returned as array objects, which can in turn be queried themselves.

Answer 2

i can't for python, ruby, perl, php. however .Net has something called LINQ which is essentially QoQ on steroids.

Answer 3

If you dont't find anything that handles data as well as Coldfusion then remember it plays very well with other programming languages. You can always do the heavy query processing in CF then just wrap your processing logic in remote cfcs and expose them as web services serving up json.

That will let you benefit from what you find great about Coldfusion while trying out some other languages.

If you need to get away from CF try SqlAlchemy in Python, or like other posters said Rails and LINQ are worth playing with.

Answer 4

This technique (ColdFusion's query-of-queries) is one of the worst ideas out there. Not only does it keep business logic in the database, but it takes what little business logic you have left in your code and shoves it out to the database just for spite.

What you need is a good language, not bad techniques to make up for deficiencies.

Python and Ruby, as well as other languages not on your list such as C# and Haskell, have exceptional support for writing arbitrary and powerful queries against in-memory objects. This is in fact the technique that you want, not ColdFusion's query-of-queries. The technique of writing queries against in-memory objects is an aspect of a general style of programming called functional programming.

Answer 5

You can also accomplish this in .NET by using LINQ. LINQ will let you query objects as well as databases.

Answer 6

In doing performance analysis of Query of Queries, I was surprised by their execution time, I could not get them to return in less than 10ms in my tests, where simply queries to the actual database would return in 1ms or less. My understanding (at least in CF MX 7) is that while this is a useful function, it is not a highly optimized one. I found it to be much faster to loop over the query manually doing conditional logic to replace what I was attempting to do with my query of queries.

That being said, it is faster than going to the database if the initial query is slow. Just don't use it thinking that it is going to always be faster than doing a more creative sort or initial query as each QofQ is far from instantaneous.