How Can I Combine the Output from Two CFSEARCH tags

[Photoshare]

I am trying to query a verity collection TWICE using CFSEARCH then combine the results.

The first CFSEARCH instance would search the collection by treating the entered keywords as a phrase (example: "Digital Photo Printer") - the 2nd CFSEARCH would then search the same collection by putting a comma in between each keyword term (example: Digital,Photo,Printer), thus asking CFSEARCH to look for all of the keywords in a document but not necessarily right next to each other.

The question I have - Is there a way to do two CFSEARCH queries on a collection and combine the results into one <CFOUTPUT QUERY = "ABC"> ?

[cf_dev2]

How about using a QoQ and UNION statement to combine the two queries into one. Something like this psuedo-code

<cfquery name="ABC" dbtype="query">
SELECT TheColumns FROM query1
UNION
SELECT TheColumns FROM query2
</cfquery>

[Photoshare]

Hi,

Thanks for the quick reply. I tried the Union statement and while it does work it prevents me from ordering the results by CFSEARCH and THEN by score

How would I show the results from the 1st CFSEARCH query BEFORE showing the results from the 2nd query. The reason for this is that IF the first query finds some matched products - I would want these to show first before showing the results from the 2nd CFSEARCH result.

[cf_dev2]

What about adding a numeric value to represent the search (1 or 2) and ordering by that value, then score?

[Photoshare]

Hi. So if the first CFSEARCH was named "Search1" and the second CFSEARCH was named "Search2" - how would I go about sorting or ordering by those individual searches? I had thought about this and tried to write something like this but could not figure out how to use "order by" in this case - since the queries each have the same data field names. Can you order by the search name?

[cf_dev2]

You should be able to add a manual column to the query then use it for sorting. ie Use 1 to represent search1 and 2 to represent the other query.

<cfquery name="ABC" dbtype="query">
SELECT CAST(1 AS INTEGER) AS SearchRank, TheColumns FROM query1
UNION
SELECT CAST(2 AS INTEGER) AS SearchRank, TheColumns FROM query2
ORDER BY SearchRank, Score
</cfquery>

[Photoshare]

Hi cf_dev2,

Thank you so much. Your suggestion worked. For some reason I wasn't able to get the CAST() function working (with or without 'Distinct' in the query) but 1 as SearchRank and 2 as SearchRank still worked. I cannot tell you how much I appreciate your help. If there is a way to donate to you (paypal?) please let me know. This search engine code I've been trying to tweak may finally be more accurate.

[cf_dev2]

If the CAST doesn't work, that's okay. It probably isn't needed anyway.

Thanks for the offer, but I'm just glad I could help :-)

Cheers

[Photoshare]

I spoke too soon. What I wrote above worked with one query but not with another. When it crashed I got the message that reads

"<br><b>Query Of Queries syntax error.</b><br> All resulting columns of queries in a SELECT statement containing a UNION operator must have corresponding types. <br> Columns with index number equal "1" have different types (OTHER, VARCHAR).

[Photoshare]

Okay. It seems if I use the following before the QoQ then it works fine (so far)

<cfwddx action="CFML2WDDX" input="search1" output="search1a">
<cfwddx action="WDDX2CFML" input="search1a" output="search1">

<cfwddx action="CFML2WDDX" input="search2" output="search2a">
<cfwddx action="WDDX2CFML" input="search2a" output="search2">

[cf_dev2]

Why wddx? Also what version of CF are you using and can you post the query that threw the error?

[Photoshare]

I was having data type problem on some search terms and I read a post a while back of someone that had the same problem. He found a workaround by converting the original data to WDDX then back again.

http://groups.google.com/group/macromedia.coldfusion.advanced_techniques/browse_thread/thread/d60200...

[cf_dev2]

Yes, data type problems aren't uncommon with QoQ. When CF tries to guess the data types, sometimes its right .. sometimes not. Explicitly using the CAST function can resolve some problems. But I don't think it was introduced until MX 7.

[Photoshare]

Well, thank you so much for your help in all this. The search is working great and your suggestions hopefully will help others as well.