coldfusion QoQ and conditionals

Question

I've asked a question( Query with grouped results by column (SQL Server) ) a while back. And got some answers that work on the SQL server, but I can't get them to work as part of a QoQ. Turns out CF has some minor limitation, like not being able to use an INNER JOIN

What I'm trying to achieve, is get a query that can have multiple item names for the same item, but when I'm calling my QoQ, I want it to filter(keep) the items that matches the language ID, if there is one, and default to another one if it is missing.

I'm doing this for several queries, so I'm trying to have the code in a function, where I plug in the query, the uniqueColumn name the languageId.

Because I can't use inner joins and I'm having issues with some of the conditionals, I was thinking of creating a second table which only has the matching languageId and then add the entries that are missing from the other one.

Is there a way to do this in one query?

score 0 · Accepted Answer · answered Nov 29 '12 at 00:35

You can do inner joins with Q of Q. You just can't use the keyword "join". You have to join your queries in the where clause. Something like this:

select whatever
from query1, query2
where query1.field1 = query2.field2
etc

Q of Q union queries are done the same way as you do them with database queries. To do something like this, " I want it to filter(keep) the items that matches the language ID, if there is one, and default to another one if it is missing.", the code would resemble

select query2.actual_value
from query1, query2
where query1.field1 = query2.field2
etc
union
select default_value
from query1
where field1 not in( ValueList(query2.field2) )

but with proper syntax and queryparams of course

score 0 · Answer 2 · answered Nov 29 '12 at 00:43

ended up doing a pretty much that: compared to original solution, this speeds it up quite a bit :)

<cffunction name="getUniqueName" output="true" returntype="Query">
    <cfargument name="q" Type="query" required="true">
    <cfargument name="uniqueColumn" Type="string" required="false" default="ID">
    <cfargument name="languageId" Type="string" required="false" default="">

    <cfif languageID EQ ''><cfset languageID = #session.langID#></cfif> <!--- default language id to user language id --->

    <!--- Get all items that match language ID --->
    <cfquery dbtype="query" name="qwLangId">
        SELECT *
        FROM 
          q
        WHERE 
          languageid = #languageId#  
    </cfquery>

    <!--- Get list of IDs for items found --->
    <cfset usedIDs = ArrayToList(qwLangId[uniqueColumn])>

    <cfif usedIDs NEQ ''>
        <!--- get all items that were not found in matched query --->
        <cfquery dbtype="query" name="qMissing">
            SELECT *
            FROM 
              q
            WHERE #uniqueColumn# NOT IN(#usedIDs#)
        </cfquery>

        <!--- combine items in a new query --->
        <cfquery dbtype="query" name="langQ">
            SELECT * FROM qwLangId
            UNION
            SELECT * FROM qMissing
            ORDER BY #uniqueColumn#
        </cfquery>
        <cfreturn langQ>
    <cfelse>
        <cfreturn q>
    </cfif>

</cffunction>

coldfusion QoQ and conditionals

2 Answers2