Skip to main content
Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.

Experiment: Modeling Complex Data Structures As Nested Query Objects In Lucee CFML 5.3.6.61

By Ben Nadel on
Tags: ColdFusion

One of the most exciting aspects of working with a Relational Database is the fact that you get you work with the ColdFusion Query object in your application code. The Query is just a wonderfully well-thought-out data-type with very pleasing developer ergonomics. But, once you start using a Document Database, with complex data structures, the Query object doesn't make much sense. Or does it? As a fun experiment, I wanted to see if I could take complex data structures, like the ones you might read from a Document Database, and model them using nested Query objects in Lucee CFML 5.3.6.61.

When it comes to the ColdFusion Query object, you can pretty much store anything you want in it - it's an extremely forgiving data-type. But, for the sake of the experiment, let's pretend that the data-types do matter; and, that we want to try and define Query objects with column-specifications that stay true-to-form.

When I use the Query object with a Relational Database, the vast, vast majority of my columns fit into a tiny subset of SQL types:

  • integer
  • tinyint
  • varchar
  • longvarchar
  • timestamp

But, this is just a small sample of the SQL Types that Lucee CFML supports. In fact, when I went to look these up, I discovered that ColdFusion supports a few surprising data-types:

  • array
  • struct
  • other
  • sqlxml

At first, when I saw these other SQL types, I thought about just storing my nested data values as array and struct columns since these probably line-up nicely with ColdFusion's native data-type constructs. But, I really wanted to try using nested Query objects. So, I decided to use the other column specification as I assume this is the catch-all for unexpected database schemas.

With that said, let's look at the experiment - I'm taking a collection of Contacts, complete with nested "tags" and "phoneNumbers", and I'm casting the various Arrays as Query objects in which each Struct becomes a row within a Query object. Then, once I have my nested Query objects, I'm going to output them using traditional CFLoop tag techniques:

<cfscript>

	// EXPERIMENT: One of the things I love about ColdFusion is the ease with which we
	// can consume the Query object. As such, I wonder what would it look like to
	// actually model a complex data-type as a Query object, complete with NESTED QUERY
	// values. To explore, let's cast this collection of Contacts into a Query recordset.
	contactsQuery = wrapContacts([
		{
			name: "Johnny Cab",
			email: "johnny.cab@totalrecall.movie",
			isFavorite: true,
			phoneNumbers: [
				{ type: "work", number: "+1 917-555-9552" }
			],
			tags: [ "Taxi", "Cab", "Car Service", "Movie References" ],
			createdAt: createDate( 2019, 7, 13 )
		},
		{
			name: "Bella's Bakery",
			email: "baker@bellasbakery.made.up",
			phoneNumbers: [
				{ type: "work", number: "+1 212-555-0032", isPrimary: true },
				{ type: "work", number: "+1 212-555-0033" },
				{ type: "work", number: "+1 212-555-0034" }
			],
			tags: [ "Bakery", "Cookies", "Munchies" ],
			createdAt: createDate( 2014, 2, 9 )
		}
	]);

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	// And, now that we have the Contacts modeled as a series of nested Query objects,
	// let's try to iterate over nested Query objects! Like a boss! Note that we are
	// performing CFLoop operations on the nested query objects the same way we would
	// perform them on a top-level query object.
	```
	<cfoutput>
		<cfloop query="contactsQuery">

			<h3>
				#contactsQuery.name#

				<cfif contactsQuery.isFavorite>
					( &starf; )
				</cfif>
			</h3>

			<p>
				Added #contactsQuery.createdAt.dateFormat( "mmmm d, yyyy" )#
			</p>

			<cfif contactsQuery.tags.recordCount>
				<p>
					<cfloop query="contactsQuery.tags">
						[ #contactsQuery.tags.tag# ]
					</cfloop>
				</p>
			</cfif>

			<ul>
				<cfloop query="contactsQuery.phoneNumbers">
					<li>
						#contactsQuery.phoneNumbers.number#

						<cfif contactsQuery.phoneNumbers.isPrimary>
							( &starf; )
						</cfif>
					</li>
				</cfloop>
			</ul>

			<cfif ( contactsQuery.currentRow lt contactsQuery.recordCount )>
				<hr />
			</cfif>

		</cfloop>
	</cfoutput>
	```

	// ------------------------------------------------------------------------------- //
	// ------------------------------------------------------------------------------- //

	/**
	* I cast the given contacts collection as a Query object. The "phoneNumbers" and
	* "tags" properties are composed as sub-Query objects.
	* 
	* @contacts I am the collection being cast as a Query.
	*/
	public query function wrapContacts( contacts ) {

		// NOTE: I didn't even know there was an "OTHER" type. In fact, there were also
		// "ARRAY" and "STRUCT" types in the list of supported SQL types; but, I decided
		// to stick with "OTHER" for now.
		var results = queryDefine([
			name: "varchar",
			email: "varchar",
			isFavorite: "boolean",
			phoneNumbers: "other",
			tags: "other",
			createdAt: "timestamp"
		]);

		for ( var contact in contacts ) {

			results.addRow({
				name: contact.name,
				email: contact.email,
				isFavorite: ( contact.isFavorite ?: false ),
				phoneNumbers: wrapPhoneNumbers( contact.phoneNumbers ),
				tags: wrapTags( contact.tags ),
				createdAt: contact.createdAt
			});

		}

		return( results );

	}


	/**
	* I cast the given phoneNumbers collection as a Query object.
	* 
	* @phoneNumbers I am the collection being cast as a Query.
	*/
	public query function wrapPhoneNumbers( required array phoneNumbers ) {

		var results = queryDefine([
			type: "varchar",
			number: "varchar",
			isPrimary: "boolean"
		]);

		for ( var phoneNumber in phoneNumbers ) {

			results.addRow({
				type: phoneNumber.type,
				number: phoneNumber.number,
				isPrimary: ( phoneNumber.isPrimary ?: false )
			});

		}

		return( results );

	}


	/**
	* I cast the given tags collection as a Query object.
	* 
	* @tags I am the collection being cast as a Query.
	*/
	public query function wrapTags( required array tags ) {

		var results = queryDefine([
			tag: "varchar"
		]);

		for ( var tag in tags ) {

			results.addRow({
				tag: tag
			});

		}

		return( results );

	}


	/**
	* I return a Query object with the given column definitions. I just provide a key-
	* value abstraction over the queryNew() constructor.
	* 
	* @columns I am the column types.
	*/
	public query function queryDefine( required struct columns ) {

		var names = [];
		var types = [];

		loop
			key = "local.name"
			value = "local.type"
			struct = columns
			{

			names.append( name );
			types.append( type );

		}

		return( queryNew( names, types ) );

	}
	
</cfscript>

Once I have my complex, nested Query objects, notice that I am using <CFLoop> to iterate over the top-level and lower-level queries the same way I would any other Query result set:

  • <cfloop query="contactsQuery">
  • <cfloop query="contactsQuery.phoneNumbers">
  • <cfloop query="contactsQuery.tags">

And, inside these <CFLoop> bodies, I reference column values, in the current iteration record, just as I would any other Query object:

  • #contactsQuery.name#
  • #contactsQuery.phoneNumbers.number#
  • #contactsQuery.tags.tag#

And the awesome thing about this is that it just works. In fact, when we run this ColdFusion code, we get the following browser output:

Complext, nested query objects ouptut using nested CFLoop tags in Lucee CFML

As you can see, it just works!

To get a meta-sense of how these ColdFusion Query objects fit together, here's what a dump() of the contactsQuery object looks like:

As you can see, the nested Query objects are embedded individually within each record of the top-level Query object.

To be clear, this was just a fun experiment! I am in no way recommending that you start created complex Query object structures. I just wanted to see if it was possible; and, if possible, what it might look like. I do love the Query object in Lucee CFML 5.3.6.61; and, it's extra pleasing to see how gosh-dang flexible it is.


Reader Comments

Nice! I wonder if Adobe would take this under advisement when pulling complex documents out of the database. Maybe you have a flag that allows you to return all lesser objects as query objects.

Reply to this Comment

@Joseph,

Thank you :D

@Colin,

I am actually to know what would happen with the databases that do have more complex structures. I'm still on MySQL 5.6; but, I think new MySQL releases and Postgress support things like embedded JSON and XML documents. I do wonder if those get parsed by the driver?

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Blog
Live in the Now
Oops!
NEW: Some basic markdown formatting is now supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.