ColdFusion Query-Of-Queries vs. The Group Attribute In CFOutput
ColdFusion has two primary tags that allow for explicit iteration over a query: CFOutput and CFLoop. While I appreciate that CFOutput allows for query iteration, I've pretty much never used it to that extent. To me, CFOutput has really only ever been a tag that processes output. As such, I throw one at the top, one at the bottom, and then I try to make it rain in between. People who do make use of CFOutput as a query iterator, however, often lament the fact that there is one huge difference between CFOutput and CFLoop: the "Group" attribute. While the Group attribute is nice for implicit grouping, I find that ColdFusion query-of-queries provide more flexibility and functionality, even when dealing with the most simple data sets.
The Group attribute of the CFOutput tag allows you to implicitly iterate over a sub-set of sibling records that have the same value in a given column (the "group" column). In such a situation, a nested CFOutput tag provides the inner loop for each group. To see what I'm talking about, take a look at this code that iterates over singers and then their songs:
<!--- Query for all the songs - singer data will be replicated across the records. ---> <cfquery name="allSongs" datasource="testing"> SELECT s.id, s.singerID, s.name, <!--- Get artist information. ---> ( sgr.name ) AS singerName FROM song s INNER JOIN singer sgr ON s.singerID = sgr.id ORDER BY singerName ASC, name ASC </cfquery> <!--- Loop over all songs (group by singer name). ---> <cfoutput query="allSongs" group="singerName"> <!--- Output the singer for THIS group. ---> #allSongs.singerName#:<br /> <!--- Loop over all songs in this group (by singer name). ---> <cfoutput> -- #allSongs.currentRow#) #allSongs.name#<br /> </cfoutput> <br /> </cfoutput>
As you can see here, we have a query that contains a cross-product of songs and singers. We are then outputting the songs using CFOutput and the group column, "singerName". Running this code, we get the following output:
-- 1) Mr. Brown
-- 2) Simmer Down
-- 3) Stir It Up
-- 4) I Can't Make You Love Me
-- 5) Something To Talk About
-- 6) About Mr. Brown
-- 7) Heard The World
-- 8) Hey Girl
-- 9) If Only She Knew
As you can see, this makes group-based iteration really easy. Sure, the numbers next to the songs don't necessarily make sense; but, the code needed in order to produce this output was rather trivial.
So, if this is so easy, why do people ever use the CFLoop tag and then lament the lack of the Group attribute? Well, a lot of the time, using the CFOutput tag to output a query is simply not convenient. The CFoutput tag is used to process output in general. As such, we are often faced with situations in which a query loop is already in the context of a CFOutput tag. And, as anyone who has tried to use one CFOutput tag within another, you're well aware that it raises the following ColdFusion exception:
Invalid tag nesting configuration. A query driven cfoutput tag is nested inside a cfoutput tag that also has a query attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing.
In such a situation, a ColdFusion query-of-queries can be used to produce the same grouped output. Now, as much as this might seem like the "fall-back" choice, I would argue that the query-of-query approach is actually much more enjoyable.
When we use the CFOutput Group attribute, we do get implicit sub-grouping. But, we get nothing else. There's no greater sense of the group itself - no group-based index, no group-based record count. If we wanted to know anything about the group at all, or about the number of groups, we'd have to start adding our own logic. The query-of-query approach, on the other hand, requires more code for sure, but is, in my opinion, almost always a more enjoyable, flexible, powerful option.
Let's take the same example as above but generate the output using ColdFusion query-of-queries:
<!--- Query for all the songs - singer data will be replicated across the records. ---> <cfquery name="allSongs" datasource="testing"> SELECT s.id, s.singerID, s.name, <!--- Get artist information. ---> ( sgr.name ) AS singerName FROM song s INNER JOIN singer sgr ON s.singerID = sgr.id ORDER BY singerName ASC, name ASC </cfquery> <!--- Query for all singers. ---> <cfquery name="singers" dbtype="query"> SELECT ( singerID ) AS id, ( singerName ) AS name FROM allSongs GROUP BY id, name ORDER BY name ASC </cfquery> <cfoutput> <!--- Loop over all the singers - for each singer we will re-query the data for the songs sung by the given singer. ---> <cfloop query="singers"> <!--- Query for the songs sung by the current singer. ---> <cfquery name="songs" dbtype="query"> SELECT id, name FROM allSongs WHERE singerID = #singers.id# ORDER BY name ASC </cfquery> #singers.name# [#songs.recordCount# Songs]:<br /> <!--- Output things singer's songs. ---> <cfloop query="songs"> -- #songs.currentRow#) #songs.name#<br /> </cfloop> <br /> </cfloop> </cfoutput>
So, immediately you probably notice that this code is about twice as long. But, if you can detach yourself from the notion that lines of code is an indicator of quality, perhaps you can enjoy the fact that the code is much more insightful. Not only do we get the same grouping as above; but, this time, we know how many singers there are, how many songs each artist has sung, and we can properly number the songs within the sub-groups:
Bob Marley [3 Songs]:
-- 1) Mr. Brown
-- 2) Simmer Down
-- 3) Stir It Up
Bonnie Raitt [2 Songs]:
-- 1) I Can't Make You Love Me
-- 2) Something To Talk About
O.A.R. [4 Songs]:
-- 1) About Mr. Brown
-- 2) Heard The World
-- 3) Hey Girl
-- 4) If Only She Knew
There's no doubt that the CFOutput / Group attribute combination is useful. I think in a small set of problems and contexts, it really provides the perfect, low-hanging-fruit solution. But, given the problem with nesting CFOutput tags and the limited insight of the group itself, I personally find that a ColdFusion query-of-queries provides more value more often. At the very least, it's good to know that there are multiple ways to solve the same grouping problem when both options are not simultaneously viable.
Want to use code from this post? Check out the license.
Ben, you're making this overly complicated IMHO. If I want numbers next to something, I'm going to use the >ol<>li< and let HTML handle the numbering. :) Just sayin'...
Er, that should be <ol><li> :p
While I have to say that QoQ does offer additional support, you've now written three queries rather than one, and have to change all three queries if the base one is altered in the future. This is a maintainability nightmare. A <cfloop query="x" group="y"> uses functionality that CF already possesses (in cfouput), would save code, and provide an easy solution. It is the "low hanging fruit" but when is CF NOT about the low hanging fruit? Almost everything in CF is geared to solve the simple stuff quickly and only need to break out the extra depth when necessary... and when looping over a set of data like this, it just isn't necessary.
I definitely agree with Jon's statement.
@Todd, the numbering isn't really the point of the article. Just part of an example. His point is just that QoQ is the "correct" way to do grouping of records because you retain more information about the groups and data than using cfoutput's group.
I don't want to imply that there is a "correct" way to go about this. It is my opinion that a query-of-queries is more powerful and useful in most cases; but I certainly don't want to say that the native Group approach isn't valid.
Again, I'm not saying one or the other; I'm only saying that I personally find the query-of-queries approach to be more useful. And, even if we added a "Group" attribute to the CFLoop tag, again, it only only be useful in certain situations. I not saying that it shouldn't be added (it would definitely be useful at times). But, adding the Group attribute to the CFLoop tag still doesn't overcome any limitations other than that of nested CFOutput tags. Even with the Group attribute, you would still need to use either a QoQ approach or a lot of additional logic to accomplish the second demo above.
Interesting. I always felt the opposite. Nesting the CFLOOP and using Q-of-Q seems like the 'low hanging fruit' quick and dirty method. It is much more elegant to have one non-normalized recordset and then CFOUTPUT/GROUP as required.
If I'm dealing with a hierarchical catalog of thousands of courses, I really don't want to nest my loops and run queries within each one. I can achieve this with a few lines using CFOUTPUT.
I was also taught to avoid putting queries and other high impact code within loops when possible. As the system grows (the recordset grows) there is too much potential for performance problems. Maybe this doesn't apply to QofQ in the same way...
Just my take.
It is really only 1 query with 2 QoQ that are based on the column names in the original Query (which can be altered with any database change to reflect the original names).
I think this could have been accomplished without the QoQ by just keeping track of the artist name and catching any new artist - like this
<!--- We have a new singer, output & track the singer's name, and establish the singer's ordered list --->
<!--- Ignore first Singer --->
<ol type="1" title="queryname.singerName">
<cfset tempSinger = queryname.singerName>
<!--- All of the time, song will output --->
<!--- We need to close the last ordered list --->
If your looping is simple enough, then yeah, a group-based approach is definitely going to be the easiest way to accomplish this.
But, what if you need to supply a list of anchor links to later in the page? Or even output stats on the number of groups? At that point, a simple, group-based approach is not going to give you the functionality you need.
You just have to take each situation on its own. There's not one way to do this. It depends on the needs.
Also, I would hesitate to call this QoQ "queries" in the same sense as a traditional database query. Merely, they are record-set functionality. Now, that's not saying they are wicked fast (no one will tell you that QoQ is a super fast manipulation to perform); but, they can be faster than other solutions.
Definitely, you can start keeping track of intermediary variables. I used to use this approach all the time, actually. Over time, however, I've just grown to find the QoQ easier to work with. While there are more "query" tags, I find it easier to "think" about.
Hi Ben, here is another solution:
First, you can nest <cfoutput> tags inside each other. The error you displayed is when you try to nest a <cfoutput> tags that have the 'query' attribute set.
This is perfectly valid syntax, and will not throw an error.
<cfoutput>#createUUID()# <br />
Second, you can do the second demo using <cfoutput> with the group attribute. You would just need to have a more complex query to begin with. I am a big fan of letting your DB do aggregates (like getting the total number of albums for a given artist) - that is part of its job - and letting CF just handle/display the results.
If you had a large number of artists, I think it would be much more process intensive using the QoQ method rather than using <cfoutput> with the group attribute.
Ben, did you do any speed testing on either of these? I'd be curious as to the load for each.
I would put myself in the GROUP camp just because it seems simpler, but maybe that's because I started before QoQ existed. I'm also a big proponent of letting the database do what the database do. In SQL Server 2005 and 2008 for instance, there's quite a few nice new functions that allow me to use QoQ less and less these days.
And I'm sorry, but @marcin, seriously??? Please tell me that was a joke.
I don't see the advantage to your approach, either. You're making multiple roundtrips to the database server when one would do. This seems to be a case of taking a simple solution and making it harder.
This is an interesting intellectual exercise, but I would never code like that.
Sorry didn't test my code, perhaps I should have before I post anything, I was using notepad :P but I forgot to put:
Give it a try let me know if that works:
@john I was just trying to demonstrate similar approach. But the best way is to do this in one query and do as much of processing at the database. I guess it was a joke.
@marcin, your approach definitely works, but you are opening two DB connections to bring back a single record each for each row of your original query. That's just incredibly inefficient. I've seen custom code like that get to tens of thousands of queries on a single page as the original query's number of records grows. Don't forget database (or any remote) connections tend to be the number one cause of slowness in cf (or really, any dynamic web page).
@marcin, sorry if i was rude, i've rewritten more queries within loops code than I'd care to remember. apologies.
Ah, good catch on the technicalities of nesting CFOutput tags. I hadn't even thought of nested CFoutput tags in which one of them didn't have a group attribute.
I am also a fan of having the database do the heavy lifting (when it can). I guess I wouldn't know what the better option in this case would be though. I suppose I could hit the database twice - once for the singers with aggregate COUNT() on songs; and once to get the songs themselves. But, you'd still have to join singer and songs twice (one for aggregates, once for the query that allows for group attribute). I guess at that point, you'd literally have to look at the debugging to see which one is faster.
I have to admit that when it comes to the hot new features in some of the database servers, I don't have too much experience. When I stopped using MS SQL Server (new job), 2005/2008 was just starting to having some really amazing looking things; but, I never got to play with them. As such, it's not immediately apparent to me where the database could add some ninja-like functionality.
I should probably read up on MySQL, see what kind of magic tricks it has in the bag. Right now, I'm just a JOIN-junky.
There's only one request to the database; the subsequent CFQuery tags are simply operations on the existing record set - they are not communicating with the database again.
I am pretty sure you could do it in one query - its just that every row would contain the song count for each artist. Not perfect, I admit, but I think it better than making multiple trips to DB or using QoQ for each artist.
I am by no means a DB ninja, but i've worked with some and it's amazing what you can get back from the database if you know the rights functions. My current favorite is the Over() function which lets you do things like aggregate data on parts of your query. I originally encountered it when I was trying to speed up some paginated pages I was doing. I would grab 15 records per page, but how do I know how many pages there are total? Count(*) over() would return that there are 2045 pages for instance, but I'm only returning 15 at a time.
Check out example B on this page http://msdn.microsoft.com/en-us/library/ms189461.aspx for some much heavier duty stuff than I've done. I am pretty sure you could get all the song counts using the over() function. There's also a rownumber I've used but for the life of me I can't remember why, but like Todd said, that's what an ordered list is for.
On a separate note Ben, I'm a long time listener first time commenter, I love your blog, it's so super useful. thanks for the effort you put into it.
It's definitely possible that the query on the DB would be faster. I tried doing this and it seems to work (though it would still require extra logic to get the per-group song indices):
This works; but I guess it's just what you're used to. I think I would still use the QoQ approach. Just a personal preference.
Yeah, the over() stuff looks really cool. Actually, when I think about "cool database" features, I think that's typically the one I have in my head. I was a ColdFusion User Group when someone came to demo the new SQL Server 2008 stuff (I think it was 2008, not 2005) and they demo's the Over()... and also, I think some sort of recursive query for creating hierarchical relationships. Some really cool stuff.
Thanks for the comments! Always super excited to get more people in the conversation.
The 'extra logic' needed to get the song indices would be 2 lines of code.
Before the inner <cfotuput>, you add <cfset songCount = 1 />.
Before the closing the inner <cfoutput> you add <cfset songCount++ />
Then you simply need:
#songCount#. - #songs.name#
In my opinion, much easier (and I would bet more efficient than) than using QoQ.
True, the counter stuff wouldn't add too much more logic.
@Ben - here is a simpler query.
This works on MySQL, and should work on MS SQL as well (If not, it should not take much to make it work)
oops...that query will need some work...I created a table with different column names and missed changing some of them.
It should be:
I will surely give this a try, the cfoutput/group join queries play havoc with recordcount and pagination. I'm always using old counting tricks to get the proper numbers. Hope this will will help.
I always use cfloop against one perfectly sorted query object. Not a big fan of cfoutput/group.
I just need a few conditions such as:
if (q.singer_id[currentRow] neq q.singer_id[currentRow-1])...etc.
(I'm utilising cf's behaviour that does not throw an exception when referencing 0 or recordCount+1 index)
i usually use cfloop from=1 to=query.recordCount with an index ... i think in cf5 or even mx there was a problem when you nested two cfloops with attribute query like you did ... i guess am still stuck in the past :)
Using QoQ is costly especially when there is a lot of records. Another alternative is to build a structure first, and then loop through that structure to display elements. The count is also available in that case.
From a performance stand point, in this example, structure is 1000% faster then QoQ.
I think it's difficult to talk about types of database access in general terms with any degree of certainty. There are just too many things that can affect performance: how the query is written, how the tables are structured (keys/indexes/etc), how CF accesses the data and so on. An example that works well with 10 records might work poorly with 1000 and horribly with 100,000 ... but it's difficult to show an example with a huge database and make it meaningful to people, and some examples might scale very well. So I look at these examples as "Here are a couple of ways to do things - BUT make sure you understand the implications of using them on larger projects."
I think in most cases, I prefer to have the database do the "heavy lifting". Sure, sometimes you'll have little or no access to the database itself and to the people who do (funny example: I worked on a project once where the client wouldn't/couldn't set up remote access to the SQL Server db, but the web application was logging in as sa, so it was trivial to put up a test page selecting * from whatever table we needed), but for the most part, you should at least be able to set up cfqueries or objects to handle what you need, if not views/queries on the db server. CUBE and ROLLUP functions can be really powerful once you get to work with them a bit, although it may require more complex examples to get your feet wet, and you still may need some tricks to get what you want from those queries.
I totally get what you are saying about not using cfoutput for iterations through database results, but I have used it in my work extensively for that reason. I'm not exactly sure why, I think it is just a matter of learning it that way and an old habit sticking. But I do seem to remember a time when I was not going to use it that way, I was going to use a cfloop instead (and, by the way, I have used cfloop for that reason many, many times as well, but I usually use it for nested loops within database results. YIKES!!! I know I am not 'supposed to' do that, but there have been times when my work has required it, and me, being of humble and meager abilities otherwise in ColdFusion/programming/database/whatever, had to stick with what I knew how to do. :-/), and when I went to use the cfloop for that, for some reason...I don't remember what it was, cfloop simply wasn't working for what I had to do, and cfoutput would. Again, I don't remember the circumstances or why...and it may very well be that cfloop would've worked if I had known 'how' to use it in the way I needed to, but I remember cfoutput working very easily for what I needed it to in database results iteration, so I used it in that particular instance. Anyway, just thought I'd share and put in my .02 worth.
I don't know if your post was referring to mine about performance, but QoQ refers to internal CF queries, not database access. So performance can be calculated on the CF part of the code. Indexes sure help on DB side, but nothing can be done on CF side about indexes on QoQ.
The main plan using structure is to grab all information once from the database and play with that data without doing any other costly query to the database nor using QoQ which are slower then direct key access to a structure.
Query once, that's the key :)
In your example, you are iterating over the query AND the structures/arrays you created - much more work than is necessary
The last query I posted was very simple and would not be 'costly' at all. I feel safe in assuming that any DB server will process the data faster using that query than ColdFusion could with your solution. Let the database crunch the data...that is what it is designed for.
Using <cfoutput> with the group attribute is a perfectly acceptable way to handle this kind of situation. I would even go so far as to argue that it would be the preferred way to handle this situation. Every thing else that has been proposed feels very Rube Goldberg-ish to me.
@Louis-Philippe, querying once does help (usually; there are always exceptions), but what you're querying can have a significant impact on overall application performance, and that's important too. This is where scalability can be an issue, and I think Scott touches on that.
I agree that there is a little more work to do achieving the same thing with structure and it's a case by case basis.
But I did test the 3 solutions with GetTickCount() and the QoQ was taking ~11ms while structure was taking ~1ms. Grouping was taking ~1ms as well, but in main Ben's example, the song's count was missing while QoQ and Structure example final results were identical.
Let's say there was 100 singers having 10 songs each, the QoQ performance dramatically decrease while structures are still fast.
But you are still iterating over the data twice for each record. Once to populate the structures/array and once to display the data.
Using my example query and <cfoutput> with the group attribute, you iterate over the data once for each record and get the same output.
You are right on this one.
I just tested your code and it's as fast as structure in that case.
Thank you for that solution.
I benchmark the 4 solutions with more data and yours is the fastest.
Singers : 210
Songs : 2570
Ben (CFoutput Groups): 5 ms
Ben (Queries of Query): 650 ms
Mine (Structures): 20 ms
Yours (CFoutput Groups): 7 ms
I forgot to mention that the queries (database) were not in the benchmark time.
Here is the result with database time:
Ben (CFoutput Groups): 28 ms
Ben (Queries of Query): 677 ms
LP (Structures): 55 ms
Scott (CFoutput Groups): 2160 ms
The costly part of the query is:
(SELECT COUNT(singerId) FROM song WHERE singerid = s.singerId) AS singerSongCount
So maybe it's better doing the count in CF after all :)
Or maybe the query needs to be tuned. :) You know I'm going to ask about indexes on those tables ...
@Dave - I was just going to say the same thing. I bet adding an index to the singerID column will speed that query up quite a bit (should speed up the others too)
If you could post the script to add an index, that would be awesome. I'm testing on MySQL 5.
@Louis-Philippe, try this:
CREATE INDEX related_singer ON song (singerID);
It's a simple table, so I don't know that we need anything more than that. See how that changes your results - thanks for running those, by the way.
I have split the DB query and CF execution time
Ben (CFoutput Groups):
Query: 27 ms
CF: 9 ms
Total: 36 ms
Ben (Queries of Query):
Queries (2): 25 ms
CF: 258 ms
Total: 283 ms
Query: 16 ms
CF: 35 ms
Total: 51 ms
Scott (CFoutput Groups):
Query: 43 ms
CF: 7 ms
Total: 50 ms
Note that for that test the "Request Debugging Output" was turned of as opposed to other tests.
First off let me say great post Ben. Such a simple little comparison has sparked a long debate. I will admit that I haven't read every single comment here mostly cause I'm running out of time but I did want to get one question out here.
I understand that as the number of records that you're working with grow then it's going to become more intensive to work with them all. I figured (that is most likely where I start getting things wrong) that it is better to pull the data from the database in one query and then hit that same query with QoQ to keep the load off the database. In my mind I figured that CF could handle the raw data better and more efficiently then repeatedly querying the database for untold number of times.
Although now that I think about it that does put more effort conceivably on the CF server instead of spreading the thinking between the DB server and CF Server.
I guess this is the kind of question that you figure out when your data grows and you have to do it on a case by case basis and see what works best for the given situation?
If that's true then I'm going to end this with what Ben usually says about just how great ColdFusion it that there are many different ways to do the same thing, IE you can vary your approach as your needs demand.
I was still asking myself for alternative when I came up with another solution.
No QoQ, no structure, so sub-select in initial query...
Save the songs into a variable while calculating how many there are for that singer and display them after.
Finally, I've learned about cfoutput groups which I had never found a case of using it properly.
Thanks to Dave and Scott, I'll now pay more attention to database indexes.
Really? I did not know about this behavior:
"I'm utilising cf's behaviour that does not throw an exception when referencing 0 or recordCount+1 index"
... If that works, that's actually really cool. I can't tell you how many times I've used this kind of logic (in many different situations) and always had *thought* I had to put in some sort of (recordCount GT 1) check as well.
I'll have to double-check that, but if so, awesome stuff!
Yeah, that nested loop issue was around for a long time. They only finally fixed it in CF8:
... but, it was a problem for so long that I still try to avoid nested loops (just out of habit) where I have to refer directly to the outer loop from the inner loop.
You are definitely right that Indexing can very quickly make or break a solution. Especially with a sub-query. I've had situations where a sub-query would takes 5-6 SECONDS to run. Threw an index on it and it drops to like 16ms. Indexes are totally badass :D
There's no doubt that ColdFusion query of queries are expensive from a performance standpoint. They can't take advantage of any of the database indexing and are really not "database engines" at all. I enjoy them because:
1) in some cases, it can replace many database calls with client-side calls which does have a performance benefit (though not always).
2) I find it easier to express "intent" with a query of queries.
"Intent" is probably not the right word. I guess, really, for me, I just mean that it simplifies the complexity of the logic. Of course, with this simplification, there is a loss of efficiency.
Like you're saying, as the data grows, your choice of solutions is going to change. Like, if you look at the queries I write for reporting, they can be do be pretty ridiculous! I try to move as much as the logic as I possibly can into the database because there are far too many record to bring back to the client for intermediary steps. Those queries are actually a LOT of fun to write because you really get to push the database pretty hard (and then watch in amazement how fast it *hopefully* runs).
Using an intermediary save-content tag is an interesting approach. I for one LOVE the CFSaveContent tag in general :)
@Scott Stroz, I like your Jun 14, 2011 at 2:54 PM post the best, and as I was reading through the comments, I thought, "Why have the DB count? You're simply counting up, then throwing away your counter."
Amazingly, my hunches were the best-performing (if L-P's numbers were right). Yay for me!
@Ben, I'm surprised you like the QoQ's. Don't get me wrong, they're easy to read, but performance suffers. Let the DB do its thang, then let CF count up with a "+ 1" counter.
What I've recently started doing in my code is if I'm building upon another SQL statement, I'll tag on an additional param in my function call:
<cfcomponent all the typical stuff>
<cffunction all the typical stuff>
<cfquery all the typical stuff>
<cfparam required="no" default="" name="SortBy">
<cfparam required="no" default="" name="SortHow">
SELECT this, that, other
<cfif SortBy neq "">
ORDER BY #SortBy#
<cfif SortHow neq "">
Basically, you're able to get query results "fast" or "good", based on how many parameters you add to your query. It can also help on the long-term maintenance by NOT adding "yet another" query to the mix.
I know it's been said that QoQ is expensive in terms of performace time and all, but it wouldn't be too much of a drag if you were doing just a simple QoQ on something like just one column, for instance, just a count or something like that, would it? I kind of like QoQs, but use them in moderation and not really extensively or anything like that. But if I need them, or feel I need them, I use them. I haven't really ever noticed them slowing down my applications dramatically or anything.
Funny..I rarely use QoQ.
I can probably count on one hand the number if times I have had to use them - and most of those were likely where I had no control over the query being run OR it was to trim out info from something that creates a query object, like cfdirectory. As I have said, I prefer to let the DB server does what it is good at.
I rarely use either, but on the recent occasion that I did use the group on cfoutput, I found the preservation of currentRow from the originating query helpful for a variety of reasons. But thanks for the QoQ concept, I could see this being helpful with reducing complexity if I wanted to add some kind of aggregated data to any segmentation.
Ben, as far as I remember this behaviour has been existing at least since CFMX6 days.
For better readability, a snippet would be something like:
<cfset prev = q.currentRow - 1>
<cfset next = q.currentRow + 1>
<cfif q.someColumn neq q.someColumn[prev]>
<!--- do someting for starting a new group here --->
IIRC, I luckily leant this from Spike's code :)
I have used QoQ from time to time, and there have even been times when I used it where I really couldn't find a better solution to the problem I was having. I don't remember specifics. But recently, the company I am at is going from developing in php to ColdFusion, and so I had to train them in ColdFusion. Of course, during our training, the concept of QoQ came up, and they were very excited about it, and said they could come up with numerous places in the already existing (php) code where it would be useful and would be a better solution than what was in place, and they were anxious to get to using it and seeing how it worked. Eventually, the php code will be replaced with ColdFusion most likely, and there are places where QoQ will be used in that, I am sure, but for now, they are just working on enhancements to the system and new code, which is ColdFusion (yay).
Let me add my opinion too please. Databases are evolving; with every edition they include new functionality which lets developers retrieve very sophisticated sets of information in more structured way. We must use these new features, as long as we write our SQL statements ourselves. It is really worth learning SQL deeper (beyond the examples which we learned from CF books by Ben Forta) because it does pay off: your code becomes more efficient, it executes faster, you take the load of CF Server. In our business we have to learn new things constantly anyway, and I can tell you on my example that learning some SQL deeper was really beneficial.
With ORM, this might not be so crucial nowadays, but if you plan to write your SQL statements yourself, it's worth learning some SQL.
@Ben et al,
Speaking of ORM, can you speak on that a bit? How does that reduce or eliminate writing SQL?
I'm new in that arena so I'm hoping someone can enlighten me.
@Randall, ORM can reduce the amount of SQL that you need to write by allowing you to use object properties instead. Instead of writing a query like
you can simply write
Instead of managing inserts and updates yourself, you can simply use get() and set() functions
This can be particularly helpful when you don't know whether or not the record currently exists in the database. (You may be fortunate enough to use a database that allows the upsert command - where the db figures out whether an update or insert is required - but if not, it can be a pain to do it yourself.)
With a relatively simple database structure, ORM can handle the vast majority of behind-the-scenes work ... but ORM setup becomes much easier if you have a relatively good understanding of SQL and of the database you want to use. One-to-many and many-to-one relationships are easier to set up if you understand foreign keys; getting into HQL and filtering makes much more sense if you've been running ad-hoc queries in the database anyway.
One thing about Hibernate and CF9 is that the ORM setup is much easier than it's been with, say, Hibernate and Java in the distant past. Writing getters and setters and load() and save() is no fun ... a justifiable criticism of ORM. If you're simply shifting setup from one place to another, it's not really helping anything. We're fortunate in that the ORM setup is handled much better now.
@Randall - I'm not necessarily sure ORM reduces complexity, so much as it shuffles it around a bit. On the basic create, read, update, delete level, the use of entities/objects speeds along reuse and probably diminishes your SQL coding, especially considering all you have to do is update, at least with the Hibernate model, your object to accommodate a new field or the removal of one. The defining of relationships is also relatively painless. You also don't necessarily have to concern yourself with T-SQL for MSSQL vs. MySQL, etc. And there's still extensibility/flexibility with querying using HQL.
The other advantages are the ability to use a secondary cache for persistence, like ehcache. I could go on all day, but it's very elegant compared to the models we've had before in CF.
The only thing CF is missing is a .NET-style LINQ and the paucity of really intense front-end visual tools like Telerik's OpenAccess offerings (though CF Builder is maturing and I'm sure there are other utilities/plugins I'm unaware of as yet), but I'm not complaining, it comes really close. I think the use of ORM requires a little more head-end attention and very thoughtful database planning, but once in place it is far easier to maintain than building CRUD DAOs or in-line SQL.
Not sure if this truly helps, but meh, worth a stab!
Do you have to set up the relationships in CF/Hibernate? How does ORM know TableA is related to TableB without a back-end "TableA.id = TableB.id"?
If Dave's answer differs from mine, defer to him, btw - but with CF9 and ORM enabled, you define this information using the cfproperty (CFML) or property (cfscript) in an object/component. You'd have a property for each of your columns, then, and you subsequently have an argument for properties with relationships, one-to-many, many-to-many, etc.
For example (from Adobe's site):
The point of this being then that you could load data from a Musician table that would include a getter for a secondary array of the related data.
Like I said, a little more overhead to setup than perhaps one-offing and consuming cfquery results, but the benefits lurking on the periphery can really speed your efforts.
This explanation is probably as clear as mud. If you want a really good example you can relate to, if you don't have it already, get the trial of CFBuilder and try generating ORM CFCs for a couple of your tables using it. It will demonstrate the basics of what you'd be looking at with CF ORM, and you could scale it up from there to test the relationship concepts.
Hope this helps,
Brian, that's a good idea - let the tool set up some examples ... I think any kind of practical example is invaluable when you're first picking this stuff up.
One of the biggest improvements in the ORM implementation here is the default behavior; I'm not sure that Adobe emphasizes it enough in their examples.
Hibernate vets remember having to spec out every single detail of every column in every detail (or finding a third-party tool that might or might not do it for you) ... you can still do that now, and in fact you'll have to under certain circumstances, but sometimes your definition can be really simple.
Here's a definition for Girl.cfc:
You only need a table attribute on your component if the table name is different than the component name; if it's in the default schema, this will work just fine. Same for properties: CF looks for a matching column if there's no column attribute. (Of course, you can specify those names if you want to make your property human-readable.)
Yes, you do have to specify relationships, and it does help to understand database structure, but it can be somewhat easy with simple relationships. Add this line to the component above.
Now, you've told CF that a Girl will have zero or more related Friend objects. (Of course, the Friend table should have a foreign key called girlID in it; if you have CF set up relationships that don't necessarily exist in the database, strange things may happen.) To build on my earlier example, you can add an additional line to make the relationship more powerful:
You can put filters on those relationships to return only certain records; now, that call to girl.getAvailableFriends() works exactly as I wrote it. In a sense, ORM helps make pseudocode actual code!
Note: this is much easier to understand when you have working examples in front of you. I'm currently working on a CF9/AJAX/JS project that's using some complex ORM; with my previous Hibernate experience and the comfort level I have with databases, this seems pretty straightforward to me, but I know it can be a lot if you're not familiar with one or the other. I apologize if I'm glossing over some of it or making it seem like you should do this in your sleep or whatever ... it's a powerful tool, but IMHO not well documented yet, and it takes practice to use well.
Normally, I do love things that are fast. But, I don't know, there's just something I really like about query of queries. Maybe, after all these years, I am just so impressed with the fact that they can even be executed that I have a special place for them in my heart.
Don't get me wrong, I love the database and I fully understand that it is far more powerful and efficient; but sometimes, the query of queries just feels right :)
While not related directly to QoQ, your comment made me think of something. From time to time, I will add a "blank" column to a database query with the intent to populate it later on the page. For example, if I have a huge content blob, I might do something like:
Then, after the query comes back, I'll use the content to generate the preview, store it back into the query object, and the make the eventual query-loop a bit more clean and easier to read.
But, that is a very rare case. Just made me think of it.
Always happy to add another point of view, even if it's one not used very often :)
This is a really good reminder. I think one of the things we're seeing from this comment thread is just how powerful databases are -- and how much better they're always getting. Even when they are asked to perform someone redundant tasks, they are still faster than other options.
I really must take some time to see what kind of features are available in MySQL 5 (which is what I use most of the time these days).
I can't really speak to any ORM stuff from personal experience; but, from what I've seen people talk about and write about, there is still the need for SQL and also for HQL (Hibernate Query Language).
Actually, at the most recent cf.Objective() conference, I attended a great talk about ORM by Marc Esher. One thing that he said that I thought was particular insightful was that you probably shouldn't but a LARGE relationship in a CFC *unless* it was going to be used for executing "save" actions specifically.
So, for example, you would NEVER have a library object that has a getBooks() method since it would have to load a ridiculous number of child objects. If you need to get such a collection, you should still defer to HQL to round them up.
I found this intriguing because when it comes to new technologies, those of us who are not "in the know" (like myself) tend to look at it as an all-or-nothing approach. Then, you hear someone that says, its good for this and horrible for this and you should feel free (and encouraged) to mix the two was very nice.
RE: Speed & ORM - I'm getting to the point where I would rather go for simplicity rather than "Yet another new technology" (see my response to the First Year on Mac post). Not that I'm against new tech, but I'm pro-standards.
So, for me, I would hesitate to use this until it becomes much more mainstream. And for my program, I'm shooting for the ability for someone to replace me quickly. They should be able to walk in and understand my coding within a few days because it's boiled down to just a few core, standard technologies. ...In my ideal world, of course.
Right now there's about twice as many things to learn as their OUGHT to be. Standard SQL and procedures, etc., on the DB seems to make more sense than loading up the CF server when I could offload much of the work to the DB. Just seems more efficient use of resources (and speedier!).
@Randall - For most other platforms, ORM is fairly mainstream at this point, but then, so, too is MVC. I don't embark on a .NET initiative without using some sort of ORM, if only because there's a significant programmatic advantage in being able to treat retrieved data as a native type. It's much the same for ColdFusion - as we move it towards one of the various framework options we have at this point, one of the things that makes it so much easier is the ORM angle.
It surely can come at the expense of RAD, especially when an organization takes on neophyte developers. It's far easier to take on guys not familiar with imperative programming and have them be useful little engines, provided they have a smattering of understanding of relational databases. ORM in CF definitely does call for more of a imperative programmatic understanding than before, even ensconced in tags.
Enterprise level CF, though, by whatever method you're using, can degenerate and head towards entropy though. How many of us, especially prior to CFMX, saw numerous murderous ad-hoc queries? There comes a point where it doesn't matter where you distribute the load, be it on your database or application server. I'm reusing a large volume of static data in key places, and I don't want to beat a path using in-line SQL for mundane retrieval, where ever the onus of processing must take place. Sure, there are practices you can embrace without ORM, but the persistence capabilities are pretty smart. Another thing that's nice about ORM in conjunction with MVC is that it makes for well-organized development from the inception, happily segmenting business rules from your data interaction. Not that you can't accomplish this without ORM.
For my part - the cost of licensing for CF8 / CF9 makes me want to milk every single advantage I can from the server, frugality that may seem odd coming from a Mac user. :)
The learning curve is definitely present, especially if, like so many of us, you're struggling to find ColdFusion-savvy help and must take on those unfamiliar with it. It also poses a risk embracing a new technology for client initiatives. I know in the contract world, clients aren't usually paying for experiments. Your hesitation is certainly cogent.
Thanks again for the hot tip regarding ColdFusion queries and out-of-bounds stuff. I wrote it up in a post this morning:
@Ben yeah I've read the new post :)
Glad to contribute to a bit odd post ;)
And big thanks for your contribution to the comunity - I have learnt a lot of things from your blog!
Your only issue with cfoutput group is that cfoutput is also used for controlling displaying of output in CF.
To work around this fact you come up with a hack using lots of Query of Queries, which is extremely inefficent and creates code that is harder to read.
Surly you should just do what the rest of us do and use Cfoutput group=query :)
We even use Cfoutput group= in factories when creating object graphs of hierarchical objects, it's by far the fastest way\.
I think part of the point also was that you can do more with the QoQ, even if it is slower.
I will certainly admit that this is more code and is slower; but, to me, it's not harder to read. Of course, look at the way I format my code - clearly, my brain works a bit differently :D For, example, I LOVE that I can use the query of queries to rename fields that aren't as "elegant" in the original context.
All said and down, if Adobe added a "group" to the CFLoop tag, I'd probably use it from time to time.
@Ben, et al,
I hope I don't get lynched for this non-CF post, but I found that, with Oracle, you can use the "with" clause. Basically a QoQ, but using Oracle 9i or newer (2004).
We now return to your regularly scheduled ColdFusiony discussion.
@Randall - not at all. In fact, for work, I am using Oracle AND ColdFusion, so I am ALWAYS interested in hearing more about Oracle things that I can incorporate in my ColdFusion code. I really appreciate the comment about Oracle...and would you mind expanding upon it, giving an example of how to use with in the manner you described (with a code example)? Just the other day, I was using in my Oracle a RegExp() function. :-) It is always helpful to know things about Oracle and/or whatever database I am working with and ways to incorporate those techniques in my ColdFusion and/or php code. (I use sql Server at work too, but the main database is Oracle)
As you can see (Ben-ism!), the syntax is:
@Anna, you'll also love "Start With / Connect By" -- which lists out the parent/child relationship.
@Randall, wow! this looks so cool! I am going to have to digest it and try it and see how it works. Thanks so much for the examples! You must be an advanced oracle database developer!
@Randall, should you be able to do this in your sql editor? Or just code? I tried your example (of course with my table names and stuff) in my sql editor, and I am getting an error. It could be me, though.
the error, btw, is "missing expression"...not sure if that helps...
Why thank you! I'm getting there, but I definitely do not consider myself to be one. Just learning and trying and building up the 'mental toolbox.'
I did also try this on CF the other day and I got the error that I believe you're mentioning. Crap. Apparently CF doesn't like to send the data as you write it. I assumed it did, but looks like maybe it parses it just a bit before handing it over to the DB.
That is pretty cool. It's like Oracle is letting you create a derived table and store it in a variable that acts as a new table "object". This reminds me of when I was using MS SQL and would be able to declare in-memory tables:
They were pretty awesome when doing some tricky things in the database. Unfortunately, MySQL does not support in-memory table variables.
"This reminds me of when I was using MS SQL and would be"
You mean Oracle?
Yeah, today I'm trying to figure out functions and procedures (Oracle). Ben, you'd love Oracle 10g+. Oracle left a bad taste in a lot of people's mouths prior to 9i. I started learning w/ 10g and it's extremely powerful (duh, it's Oracle). You can offload a LOT of work to the DB, if you want.
I've never worked on Oracle before. Perhaps Oracle and MS SQL have the same syntax for declaring temp tables? I was just trying to draw a parallel between the WITH() and the DECLARE statements in Oracle and MS SQL, respectively.
That's quite possible. It would explain why my with statement didn't work...I wasn't saving the results to anything. @Randall -- I was writing a code for a package just the other day, so I'm with ya there on the functions and procedures. I actually wrote one stored procedure, and then decided just to go ahead and do what I wanted to do with it in my cf code due to lack of access to the Oracle. I didn't want to spend all this time writing a procedure and then not get to use it because the DBA wouldn't give me access. But I hear that if you have access, it's the way to go.
hey Joseph, how r u man.
i have below scenario.
1. I have main table records for a user. table name is "tblassessments"
2. For these assessments i have progressnotes. table is "tblAssessmentsProgressnotes"
3. for these "Progress notes" I have Review. table is "tblAssessmentsProgressnotesReview"
now i have to display all progress notes with their reviews respectively for a assessment.
- progress notes 1
-- Review 1
- progress notes 1
-- Review 1
- progress notes 2
-- Review 1
-- Review 2
how can i achieve this?
I'd do this:
<cfquery name="qAssessmentsAndReviews" ...>
select a.assessmentID, a.assessment, p.progressNoteID, p.progressnote, r.reviewID, r.review
from tblassessments a
join tblAssessmentsProgressnotes p on p....
join tblAssessmentsProgressnotesReview r on r...
<cfoutput query="qAssessmentsAndReviews" group="assessmentID">
#assessment# <!--- title of assessment --->
Seriously, if I weren't already married I would propose to you Ben (or one of the other people who always help work these issues along). Once again you've helped me resolve a problem quickly and easily. Thank you!
Hi Ben, tried like this:
<cfset answer = obj.getSearch(URL)>
The above resturen the query resultset.
Now doing this:
<cfquery dbtype="query" name="counts">
select count(*) as strcount from answers
while make a cfdump of <cfdump var="#counts#"> inside the cfoutput loop, it throws an error: variable answer is undefined. not sure why this is happening. you can throw some light on it
using coldfusion10 latest update