ColdFusion Query-Of-Queries vs. The Group Attribute In CFOutput

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.

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.

@Jon,

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).

@Ben,

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

<query>

<Cfset tempSinger="">
<cfloop query="queryname">
<cfif comparenocase(queryname.singerName,tempSinger)>
<!--- We have a new singer, output & track the singer's name, and establish the singer's ordered list --->
<cfif comparenocase(tempSinger,"")>
<!--- Ignore first Singer --->
</ol><br><br>
</cfif>
<h3>queryname.singerName</h3>
<ol type="1" title="queryname.singerName">
<cfset tempSinger = queryname.singerName>
</cfif>
<!--- All of the time, song will output --->
<li>queryname.songTitle</li>
</cfloop>
<!--- We need to close the last ordered list --->
</ol>

Hi Ben, here is another solution:

 
<cfquery name="allSingers" datasource="testing">
SELECT id, name
FROM singer
ORDER BY name ASC
</cfquery>
 
<cfoutput query="allSingers">
 
<cfquery name="allSingerSongCount" datasource="testing">
SELECT COUNT(id) as SongCount
FROM song
WHERE singerID = #id#
</cfquery>
 
#name# [#allSingerSongCount.SongCount# Songs]:<br />
 
<cfquery name="allSongs" datasource="testing">
SELECT id, singerID, name
FROM song
WHERE singerID = #id#
ORDER BY name ASC
</cfquery>
 
-- #allSongs.currentRow#) #allSongs.name#<br />
 
</cfoutput>

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 />
<cfoutput>#createUUID()#<br />
<cfoutput>#createUUID()#</cfoutput>
</cfoutput>
</cfoutput>

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.

John

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:

<cfloop query="allSongs">
-- #allSongs.currentRow#) #allSongs.name#<br />
</cfloop>

Give it a try let me know if that works:

<cfquery name="allSingers" datasource="testing">
SELECT id, name
FROM singer
ORDER BY name ASC
</cfquery>
 
<cfoutput query="allSingers">
 
<cfquery name="allSingerSongCount" datasource="testing">
SELECT COUNT(id) as SongCount
FROM song
WHERE singerID = #id#
</cfquery>
 
#name# [#allSingerSongCount.SongCount# Songs]:<br />
 
<cfquery name="allSongs" datasource="testing">
SELECT id, singerID, name
FROM song
WHERE singerID = #id#
ORDER BY name ASC
</cfquery>
 
<cfloop query="allSongs">
-- #allSongs.currentRow#) #allSongs.name#<br />
</cfloop>
 
</cfoutput>

@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.

J

@Ben -

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.

John

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.

@Ben - here is a simpler query.

SELECT
	s.id,
	s.name,
	s.singerId,
	sgr.name AS singerName,
(SELECT COUNT(artistId) FROM song WHERE artistid = s.artistId) AS singerSongCount
FROM song s
INNER JOIN singer sgr ON sgr.id = s.artistId
ORDER BY
	sgr.name ASC,
	s.title ASC

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:

SELECT
	s.id,
	s.name,
	s.singerId,
	sgr.name AS singerName,
	(SELECT COUNT(singerId) FROM song WHERE singerid = s.singerId) AS         singerSongCount
FROM song s
INNER JOIN singer sgr ON sgr.id = s.artistId
ORDER BY
	sgr.name ASC,
	s.title ASC

@Ben Nadel

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.

--BL

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 :)

Hi Ben,

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.

<h1>Structures</h1>
 
<!---
	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>
 
<cfset struct = GetTickCount() />
 
<cfscript>
	arrSingersSequence = []; // Contains the singers' id ordered
	objSingers = {};		 // Contains the signers' objects
	objSongs = {};			 // Contains the songs' objects
	 
	// We are going to transform the query in objects.
	for ( currentRow = 1; currentRow <= allSongs.recordCount; currentRow++ ){
		if ( !StructKeyExists( objSingers, allSongs.singerID[ currentRow ] ) ) {
			 
			// Append the singer's sequence to the array as structure are not ordered keys
			ArrayAppend( arrSingersSequence, allSongs.singerID[ currentRow ] );
			 
			// Assign value to singer object where the key is the singer's id
			objSingers[ allSongs.singerID[ currentRow ] ] = {
				name = allSongs.singerName[ currentRow ],
				songs = [] // This is the array containing the songs' sequence
			};
		}
		 
		// Assign value to the song object where the key is the song's id
		objSongs[ allSongs.id[ currentRow ] ] = {
			id = allSongs.id[ currentRow ],
			name = allSongs.name[ currentRow ],
			singerID = allSongs.singerID[ currentRow ] // not used but handy in some cases
		};
		 
		// Appends the song id to the singer's song sequence
		ArrayAppend( objSingers[ allSongs.singerID[ currentRow ] ].songs, allSongs.id[ currentRow ] );
	}
</cfscript>
 
<cfoutput>
 
	<cfloop array="#arrSingersSequence#" index="singer">
		 
		#objSingers[singer].name# [#ArrayLen( objSingers[singer].songs )# Songs]:<br />
 
		<!--- Output things singer's songs. --->
		<cfloop from="1" to="#ArrayLen(objSingers[singer].songs)#" index="idxSong">
 
			-- #idxSong#) #objSongs[objSingers[singer].songs[idxSong]].name# <br />
 
		</cfloop>
		<br />
	</cfloop>
</cfoutput>

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.

@Ben,

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.

@Dave,

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 :)

@Louis-Phillippe

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.

@Scott,

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.

@Louis-Phillipe -

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.

@Scott,

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.

@Scott,

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)

@Dave,

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

LP (Structures):
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.

<cfoutput query="allSongs" group="singerName">
	<!--- Loop over all songs in this group (by singer name). --->
	<cfset songCount = 1 />
	<cfsavecontent variable="strSongs">
		<cfoutput>
			-- #songCount#) #allSongs.name#<br />
			<cfset songCount++ />
		</cfoutput>
	</cfsavecontent>
	<!--- Output the singer for THIS group. --->
	#allSongs.singerName# [#songCount-1# Songs]:<br />
	#strSongs#
	<br />
</cfoutput>

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.

@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
FROM table
<cfif SortBy neq "">
ORDER BY #SortBy#
<cfif SortHow neq "">
#SortHow#
</cfif>
</cfif>
</cfquery>

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:

<cfloop query="q">

<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 --->
</cfif>

etc, etc.

</cfloop>

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

select f.* from girls g inner join friends f on g.girlid = f.girlid
	where f.available = true

you can simply write

girl.getAvailableFriends()

Instead of managing inserts and updates yourself, you can simply use get() and set() functions

myPhone.add( girl.getPhoneNumber() );
girl.setInterest( "none" );
 
girl.save();

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!

@Dave, @Brian,

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"?

@Randall,

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):

<cfcomponent hint="I am a musician" output="false" persistent="true">
	<cfproperty name="musicianID" hint="The id for the musician" fieldtype="id" type="numeric" datatype="integer" generator="identity">
	<cfproperty name="name" hint="name of the musician" type="string" length="255">
	<cfproperty name="age" hint="Age of the Musician" type="numeric" datatype="integer">
	<cfproperty name="instruments" hint="Array of instruments" singularname="instrument" fieldtype="many-to-many" collectiontype="array" cfc="com.Instrument" linktable="musician_instrument" FKColumn="musicianID" inversejoincolumn="instrumentID" orderby="name">

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,
B

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:

component persistent="true" {
	property name="girlID" type="numeric" fieldtype="id";
	property name="name" type="string";
	property name="hairColor" type="string";
	property name="IQ" column="intelligence_quotient" type="numeric" ormtype="int";
}

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.

property name="friends" type="array" fieldtype="one-to-many" cfc="Friend" fkcolumn="girlID" lazy="true";

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:

property name="availableFriends" type="array" fieldtype="one-to-many" cfc="Friend" fkcolumn="girlID" where="status = 'single'" lazy="true";

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.

@Ben,

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.

@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!

Ben,

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\.

dave

I think part of the point also was that you can do more with the QoQ, even if it is slower.

@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)

WITH
dept_costs AS (
	SELECT dname, SUM(sal) dept_total
	FROM   emp e, dept d
	WHERE  e.deptno = d.deptno
	GROUP BY dname),
avg_cost AS (
	SELECT SUM(dept_total)/COUNT(*) avg
	FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;

As you can see (Ben-ism!), the syntax is:

WITH  
alias_name AS (  
	typical SQL statement sans semi-colon  
	select 1 from dual  
) comma if you're adding another query alias  
,  
alias_name_2 AS (  
	select 2 from dual  
)  
select * from alias_name, alias_name_2;

@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...

You must be an advanced oracle database developer!

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.

"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.

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.

Assessmnet 1
- progress notes 1
-- Review 1
Assessmnet 2
- progress notes 1
-- Review 1
- progress notes 2
-- Review 1
-- Review 2

how can i achieve this?

I'd do this:
<code>
<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...
</cfquery>

<cfoutput query="qAssessmentsAndReviews" group="assessmentID">

#assessment# <!--- title of assessment --->

<cfoutput group="progressNoteID">

#progressNote#

<cfoutput>
#review#
</cfoutput>

</cfoutput>

</cfoutput>
<code>

awesome thanks,

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:

<cfoutput query="answer">
<cfquery dbtype="query" name="counts">
select count(*) as strcount from answers
</cfquery>
</cfoutput>

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