Link to home
Create AccountLog in
Avatar of richard-mckenna
richard-mckennaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Select row if value in list using cfquery of query

Hi,

I have a cfquery pulling records from a database, one of the columns returned contains a comma seperated list of integers. So the returned query would look something like this:

product        colours
Product1      1,5,8,9,10
Product2      1
Product3      5,8,9
Product3      5

I then need to filter that query using coldfusion's query of query feature based on a colour. So say I wanted all the products with colour 5 it would return products 1, 3 and 4. This would be easy if I could use the MySQL function FIND_IN_SET but because its coldfusion's query of query feature it doesn't support FIND_IN_SET

Attached is an example of the first database query and what I've tried for the query of query. Supllying a URL.colourid value of 5 would return product 4 but not 1 and 2. My actual code is a lot more complex than this, but the basic premise is the same.

I've looked for hours trying to find an example that does this but haven't had any luck.

Any help on how to do this would be great, as it's driving me mad!
<cfquery name="getProductsQuery" datasource="#APPLICATION.datasource#">
            SELECT *
            FROM products
</cfquery>

<cfquery name="products" dbtype="query">
    	SELECT *
        FROM getProductsQuery
        WHERE '#URL.colourid#' IN (colour_ids)
</cfquery>

Open in new window

SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of richard-mckenna

ASKER

I think you've both hit the nail on the head there. Seperating the product colours into another table seems the sensible option and would obviously be much more efficient than what I was trying to do.

Thanks for the the help.
agx, keep in mind that this is a list of color IDs compared against a list of IDs stored in the database.

I think the LIKE could work with an extra CLOOP ?

(
<cfloop index="xx" list="#color_ids#">
  or "," + colour_id + "," like '%,#xx#,%'
</cfloop>
)


Yeah, 3 tables is definitely the way to go.  It'll also be faster than the string/csv approach.
>> keep in mind that this is a list of color IDs compared against a list of IDs stored in the database

Yeah, I know. I don't think a loop is needed.  I just forgot my usual hack-of-a-hack ;-)   If you append the delimiter to the front and end of the value, it'll work in all cases (starts with, in between, ends with).  

   ie  WHERE ','+ columnName +','  LIKE '%{value}%'

Ack... friday fingers.  This:

   ie  WHERE ','+ columnName +','  LIKE '%{value}%'

... should be

   ie WHERE ','+ columnName +',' like '%,{value},%'

translated that would be...

  WHERE ','+ columnName +',' like '%,{value},%'

  WHERE ',1,2,3,4,' like '%,3,5,7,%'

That won't match the 3..


I think you need the loop to generate ...

  Or ',1,2,3,4,' like '%,3,%'
  Or ',1,2,3,4,' like '%,5,%'
  Or ',1,2,3,4,' like '%,7,%'


It's all not worth it :)    

No, I think his value is a single number. So translated it's

        WHERE ',1,2,3,4,' like '%,3,%'

It's all not worth it :)    

It's terribly ugly too. But if you're stuck with a really bad db you can't change (like on an intranet), it'd mostly work in a pinch.  But the whole string matching thing is a terrible idea ;-)
i agree, which is why I've changed my db and code now. I've also found it's reduced the amount of code I've need in other parts of the site. It's simplified things greatly.

I can't thank you enough :)