Verity and CFQUERY Order By Problem

you could also write another query that pulls all necessary data from
your product_info table and then do a QoQ between the verity results
query and this products query:

<CFSEARCH NAME="Getresults" collection="XYZ" type="simple"
CRITERIA="#Keyword#">

<cfquery name="qProducts" datasource="your_dsn_here">
SELECT productid, title, vendor, sell_price
FROM product_info
ORDER BY productid
</cfquery>

<cfquery name="qoq1" dbtype="query">
SELECT DISTINCT
Getresults.KEY, Getresults.score, Getresults.summary, Getresults.title,
qProducts.productid, qProducts.title AS prod_title, qProducts.vendor,
qProducts.sell_price
FROM Getresults, qProducts
WHERE qProducts.productid = Getresults.KEY
ORDER BY score DESC
</cfquery>

you can cache the qProducts query if it returns a lot of results, so it
is pulled from memory instead of hitting your db with every search...

hth

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Actually, Just found out that if the results bring back more than 80 items then the loop errors out (errno:24) which is a "too many files open" error. I can put a maxrows="80" but then I can't drill down further (category, mfg) if those items aren't in the first 80 results.

Hi Azadi.

You're a genius. Thank you so much. This actually worked once I get the syntax correct.
I have one question. If the results find 50 items. Would this union loop cause major
query delays?

This method makes sense but it wouldn't run. Is this code compatible with MX 6.1?

1) if you are doing a QoQ, specify dbtype="query" instead of
datarource="Step2"

2) <cfset Itemsfound = "#valuelist(key)#"> should really be <cfset
Itemsfound = valuelist(Step2.key)>

3) in your step 3 loop over your itemsfound list creating a UNION query
with an added sort column to sort your results according to score.
something like this:

<cfquery name="Step3" datasource="step3">
SELECT productid, title, vendor, sell_price, '1' AS sortcol
FROM product_info
WHERE productid = <cfqueryparam cfsqltype="cf_sql_integer"
value="#listfirst(Itemsfound)#">
<cfif listlen(Itemsfound) gt 1>
<cfloop from="2" to="#listlen(Itemsfound)#" index="idx">
UNION
SELECT productid, title, vendor, sell_price, '#idx#' AS sortcol
FROM product_info
WHERE productid = <cfqueryparam cfsqltype="cf_sql_integer"
value="#listgetat(Itemsfound, idx)#">
</cfloop>
</cfif>
ORDER BY sortcol, title
</cfquery>

i assume you have necessary checks in place to make sure your step 1 and
step 2 actually returned some results and your Itemsfound list is not
empty, so i did not include that code above...

hth


Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/