Skip to main content
W
wcx08
Known Participant
October 12, 2010
Question

Query Of Query...Somewhat...

  • October 12, 2010
  • 3 replies
  • 644 views

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.

This topic has been closed for replies.

3 replies

D
Community Expert
Dave WattsCommunity Expert
Community Expert
October 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.

<cfquery ...>

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.

Dave Watts, Eidolon LLC
D
Community Expert
Dave WattsCommunity Expert
Community Expert
October 12, 2010

Or, what the savior of slack said.

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.

Dave Watts, Eidolon LLC
J
JR__Bob__Dobbs
Inspiring
October 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.

<cfset keyList=ValueList(qryFoundProducts.myKey)>

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.

D
Dan_Bracuk
Inspiring
October 12, 2010

ValueList()

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices