Search code examples
coldfusionqoq

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

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?

Solution

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

    filteredQuery

    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.