Query Of Query...Somewhat...

Report · Oct 12, 2010

I have a problem I can't quite figure out for some reason. I'm trying to create an advanced search using part Verity cfsearch and part regular database cfquery. I have to do this because the advanced search uses text-based criteria as well as number criteria, and apparently Verity does not support numerical comparison (at least not for me...See my previous thread about it). Regardless, here's my situation:

I'm doing a cfsearch on a Verity collection for a keyword. This works and returns a query collection named "qryFoundProducts." I then want to take all the primary keys of this query and search my database for all products that match these keys. So basically, the resulting query from my database should match the Verity query, but instead it will be a regular database query and not a Verity query, which I can then perform another query on for numerical comparisons. How would I go about doing this? I've tried putting it in a cfloop, but this just writes over the previous query and only returns one product when the loop is through. I need a way to build the query each time through the loop when a primary key match is found.

Any infomation would be greatly appreciated.

Thank you.

Report · Oct 12, 2010

ValueList()

Report · Oct 12, 2010

Assuming that the qryFoundProducts result set has a field named "myKey" which contains only numeric values, and that your want to get all records with matching "myKey" values from a table named "dbTable".

1. Convert the myKey values in qryFoundProducts to a comma delimited list.

2. Use the keyList variable in a database query with CFQUERYPARAM.

<cfquery name="theQuery">
    SELECT FieldA, FieldB
    FROM dbTable
    WHERE myKey IN ( <cfqueryparam value="#keyList#" cfsqltype="cf_sql_integer" list="yes"> );
</cfquery>

Note that this does not "build the query each time through the loop when a primary key match is found.". It uses a single query.

Report · Oct 12, 2010

Yikes! No, you don't want to build a query for each Verity match! You may think you do, but trust me, you don't.

Instead, you want a single query to return all records which were fetched by your Verity query.

SELECT ...

FROM ...

WHERE primaryKey IN (<cfqueryparam cfsqltype="..." value="#valueList(yourVerityQuery.primaryKeyField)# list="yes">)

</cfquery>

You can then loop through this query or the Verity query, and reference values from both queries in the body of the loop.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

GSA Schedule, and provides the highest caliber vendor-authorized

instruction at our training centers, online, or onsite.

Report · Oct 12, 2010

Or, what the savior of slack said.