4 Replies Latest reply on Oct 12, 2010 9:22 AM by JR "Bob" Dobbs

    Query Of Query...Somewhat...

    wcx08 Level 1

      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.

       

        • 1. Re: Query Of Query...Somewhat...
          Dan Bracuk Level 5

          ValueList()

          • 2. Re: Query Of Query...Somewhat...
            JR "Bob" Dobbs Level 4

            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.

            • 3. Re: Query Of Query...Somewhat...
              Dave Watts Adobe Community Professional

              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.

              • 4. Re: Query Of Query...Somewhat...
                Dave Watts Adobe Community Professional

                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.