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

richard-mckennaAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
contains a comma seperated list of integers

The biggest problem is the table structure. Storing delimited data is almost never a good idea for exactly this reason.  It's very hard to work with.  

QoQ are really basic. But a LIKE hack might work.  I say might because LIKE does a string comparison. So any variation could throw off the match.  Plus LIKE queries are always slow.

  WHERE colour_ids LIKE '%,#URL.colourid#,%' 

Open in new window


If you have a choice you should normalize the data into three tables. One row per Product + Color combination.

Product:  ProductID (unique products)
Colour:  ColourID (unique products)
ProductColour:  ProductID, ColourID

Then your query would be a simple JOIN

SELECT  Columns
FROM     Product p 
              INNER JOIN ProductColour pc ON pc.ProductID = p.ProductID
              INNER JOIN Colour c ON c.ColourID = pc.ColourID
WHERE  c.ColourID = <cfqueryparam value="#url.ColourID#" cfsqltype="cf_sql_integer">

Open in new window



Product1      1,5,8,9,10
Product2      1
Product3      5,8,9
Product3      5
0
 
gdemariaConnect With a Mentor Commented:
First, if at all possible, you should change your database design.  You have a many-to-one relationship here between product and color, so the color should be in a child table  ProductColors.  This would be the appropriate table structure.   The method you have now, as you can see, will only cause headaches.

If you are not able to change your design, then, question.

Why is it necessary for you to do a QofQ?  You seem to have a solution using SQL, so just do another database query.   Many people feel that a QofQ will save resources/time, but it's not true.  The database functions are very fast and often QofQ is much slower than hitting the database again.  So, returning to the database could be faster and more effective.

But if you can not do that, then you should just do a loop of your original query.  There is not much difference between a loop and QofQ.

<cfloop query="getProds">  
   <cfloop index="x" list="#getProds.colors">
     <cfif listFind(colour_ids,x)>
         <!---- this one matches, store the product ID ---->
         <cfset productIDs = listAppend(productIds, getProds.productID)>
         <cfbreak>
     </cfif>
   </cfloop>
</cfloop>
0
 
richard-mckennaAuthor Commented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gdemariaCommented:
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>
)


0
 
_agx_Commented:
Yeah, 3 tables is definitely the way to go.  It'll also be faster than the string/csv approach.
0
 
_agx_Commented:
>> 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}%'

0
 
_agx_Commented:
Ack... friday fingers.  This:

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

... should be

   ie WHERE ','+ columnName +',' like '%,{value},%'
0
 
gdemariaCommented:

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 :)    

0
 
_agx_Commented:
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 ;-)
0
 
richard-mckennaAuthor Commented:
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.