Can you filter rows in a Query of Queries using a property value of a struct column value?

Question

If an object, such as an Array or Struct is used as the column value of a row in a CF query object. Can properties of that object be used in the WHERE clause of a query of queries to limit the result set?

Given:

<cfset local.exampleArray=[
    {   id:1,
        nestedArray:["Tom","Dick","Harry"],
        nestedStruct:{nid:42,name:"unknown"}
    },
    {   id:2,
        nestedArray:["John","Paul","Ringo","George"],
        nestedStruct:{nid:12,name:"rockstars"}
    },
    {   id:3,
        nestedArray:["Bonny","Clyde"],
        nestedStruct:{nid:43,name:"criminals"}
    },
]>
<cfset local.exampleQuery=queryNew("id,nestedArray,nestedStruct","integer,object,object",local.exampleArray)>

The queries of queries:

<cfquery dbtype="query" name="local.exampleQoQ">
    SELECT *
    FROM   [local].exampleQuery
    WHERE  nestedStruct.nid=12
</cfquery>

<cfquery dbtype="query" name="local.exampleQoQ2">
    SELECT *
    FROM   [local].exampleQuery
    WHERE  nestedArray.length=3
</cfquery>

Results in the query of queries runtime error: nestedStruct.nid/nestedArray.length does not match any table in FROM table list

When not using the object type columns in the WHERE clause, the objects are returned correctly when queried and behave as expected:

<cfquery dbtype="query" name="local.exampleQoQ">
    SELECT *
    FROM   [local].exampleQuery
    WHERE  id=1
</cfquery>
<cfoutput query="local.exampleQoQ">
    #local.exampleQoQ.id#:#ArrayLen(local.exampleQoQ.nestedArray)#:#local.exampleQoQ.nestedStruct.nid#
</cfoutput>

Will result in "1:3:42"

Is this just an issue where the QoQ implementation doesn't support accessing the properties of a column value object?

You answered your own question: it's simply not supported. You can always workaround by "flattening" the data you need to query/filter for though. — Alex, May 03 '19 at 20:13
That's what I thought from reading the documentation, was hoping I was just missing some obscure notation for QoQ. Thanks for the suggestion, will probably end up using ArrayMap, ArrayToList and a collection loop to flatten everything. — C.J. Scupski, May 03 '19 at 20:59
@C.J.Scupski This seems like kind of a contrived issue. Are you trying to work with a base query that is returning arrays and structs? And if so, which DBMS is it you're working with. This doesn't really seem like something you'd want to do in a query. That sort of data structure is meant to be more flat. It _can_ be done, but probably shouldn't for this reason here. That said, what you seem to want to do can be accomplished with `QueryFilter()` if you are using CF2016+. Answer forthcoming. — Shawn, May 03 '19 at 22:59

score 2 · Accepted Answer · answered May 03 '19 at 23:46

As I mentioned earlier, a database query can have a column with array/structure-ish data, but that's not really what a database is for. As you've seen, it makes querying for the data you want more difficult than it should be, and is really treating a database as little more than a place to store data.

Anyway, you seem to want to filter your query records by a specific value that's contained inside one column's structure data and also filter those results if another columns array data contains a certain number of records.

You don't want Query of Query for this. It's already a highly limited "query" aspect of CF, and should be used only when necessary. If you are using ColdFusion 2016+, you can use a function that was added: queryFilter().

Using your above setup under "Given:", you can use the following:

<cfscript>
    /* Instead of QoQ, limit your Query with queryFilter() */
    filteredQuery = queryFilter( exampleQuery
        ,function(o){ return o.nestedStruct.NID == 12 ;
        }
    ) ;
</cfscript>

Which will give you a variable filteredQuery that contains:

Then you can just address filteredQuery.nestedArray to get your array of "John, Paul, George and Ringo".

But you also want to filter for the array in nestedArray to be 3 elements. So you can just add another condition to your callback return:

local.filteredQueryForLength = queryFilter( 
    local.exampleQuery2,
    function(o){ return o.nestedStruct.NID == 12 && arrayLen(o.nestedArray) == 3 ; }
) ;

Which then gives you an empty Query Object, since there are 4 elements to the filteredQuery.nestedArray that you selected.

Finally, queryFilter has a member function that is simply filter(), so you can be even shorter and use this:

local.filteredQueryForLength2 = local.exampleQuery3.filter(
    function(o){ return o.nestedStruct.NID == 12 && o.nestedArray.len() == 3 ; }
) ;

Also remember that ColdFusion Query Objects are Pass-By-Reference, so if you do anything (like filter()) that modifies the object, it will change that base object so it will be different if you use it again. Which also means that you don't have to assign it to a variable. You can just call queryFilter and then reference your original query object. And another note: when using CF Script syntax (which I much prefer), don't forget that=is assignment and==is comparison. I forgot that initially and all of the records were returning withnestedStruct.NIDas12`. :-/

Last note: I created a Fiddle at https://trycf.com/gist/031a090059a46cd471aa44627fc7ee12/acf2016?theme=monokai. I added one extra element to your mocked query, so that you could see what your return object looks like with multiple elements matching the filters.

Can you filter rows in a Query of Queries using a property value of a struct column value?

1 Answers1