Difficult(?) query loop

[jarla]

Hi!
I can´t make this query work. First little story about what this query should do:
I have picture searching interface on my company´s website. Some keywords are in english, some are in our language which is finnish. So I made database "translate" which have most of typical search words translated to both languages. So when customer writes a word "cat", it should search also with a word "kissa" which is "cat" in finnish. Well, that is almost easy with this kind of code:
-----------
<cfloop list=&quot;#keyword#&quot; delimiters=&quot;,&quot; index=&quot;index1&quot;>
<cfquery datasource=&quot;#mydsn#&quot; name=&quot;translate&quot;>
SELECT * from translate
WHERE uk = '#index1#'
OR fin = '#index1#'
</cfquery>

<cfoutput query=&quot;translate&quot;>
<cfset uk = &quot;#uk#&quot;>
<cfset fin = &quot;#fin#&quot;>
</cfoutput>

</cfloop>

<cfquery datasource=&quot;picturedata&quot; name=&quot;finalsearch&quot;>
SELECT asset_name, categories
FROM pictures
WHERE
categories LIKE '%#uk#%'
OR categories LIKE '%#fin#%'
</cfquery>
-----------
That search works right when customer use only one keyword. It translate keywords and find pictures with both languages without any problems. But when customer use option to add many words to keyword -field (cat,child,nature) it takes always only the last keyword from the list to search. I know that it´s because the loop, but how can I work with the loop that ALL keywords go to search after translation??

Thanks

 

[mansii]

Sure, it does.

The currentrow of the &quot;translate&quot; query is the last record, so the #uk# is taken from the last record.
I think that you need to make another loop to acomplish the whole list of the query.

Something like:
<cfloop query=&quot;translate&quot; ...>
<cfquery datasource=&quot;picturedata&quot; name=&quot;finalsearch&quot;>
SELECT asset_name, categories
FROM pictures
WHERE
categories LIKE '%#uk#%'
OR categories LIKE '%#fin#%'
</cfquery>
<cfoutput query=&quot;finalsearch&quot;>
#asset_name# #categories#
</cfoutput>
</cfloop>

Hope this helps.
mansii

 

[r937]

if at all possible, do not run a query inside a loop

change the first query to use the keywords in an IN list

where uk in ( ListQualify(#keyword#) )
or fin in ( ListQualify(#keyword#) )

this will bring back one or more rows

now take the uk and fin column values and turn them into lists

<CFSET uklist = ValueList(translate.uk)>
<CFSET finlist = ValueList(translate.fin)>

now what you want to do is combine these lists and then use the resulting single list in a loop inside the second query to generate OR conditions

where 1=2
<CFLOOP ... >
or category like '%#listitem#%'
</CFLOOP>

this is messy but it is required since you are using the LIKE construction to search for keywords anywhere inside the category

rudy

 

[philhege]

Jarla,

Let's try this with one query.

You need all of the words translated from your translate table that are in the pictures table, so query the pictures
table based on the return from the translate table:


<cfloop list=&quot;#keyword#&quot; delimiters=&quot;,&quot; index=&quot;index1&quot;>
<cfquery datasource=&quot;#mydsn#&quot; name=&quot;translate&quot;>
SELECT * from translate
WHERE uk IN(select uk from translate where uk IN(#quotedvaluelist(index1)#)
OR fin IN(select fin from translate where fin IN(#quotedvaluelist(index1)#)
</cfquery>

HTH,

 

[r937]

if that works as intended, philhege, then great, but it still has a query inside a loop

 

[philhege]

Erg. It's Friday; I wrote that at lunch, [insert other stock excuse here]....

The loop's not needed. Replace the #index1# variable with #keyword#, and you should be good to go.

SELECT asset_name, categories
FROM pictures
WHERE
categories
IN(select uk from translate where uk IN(#quotedvaluelist(keyword)#)
OR categories IN(select fin from translate where fin IN(#quotedvaluelist(keyword)#)

HTH,

 

[r937]

you must still be recovering from that lunch, phil, because here's one more thing -- categories are not supposed to match keywords, keywords are supposed to be found anywhere inside a category, using LIKE '%keyword%'

;o(

 

[philhege]

r,

Absolutely correct. I think I'll go home and start over again on Monday.

The OR loop construction in the query, as you wrote, should fit the bill.

Rgds,
PH