Solved

Select row if value in list using cfquery of query

Posted on 2011-02-11
10
1,010 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:richard-mckenna
  • 5
  • 3
  • 2
10 Comments
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 75 total points
ID: 34875245
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 175 total points
ID: 34875279
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
 

Author Comment

by:richard-mckenna
ID: 34875313
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 34875335
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
 
LVL 52

Expert Comment

by:_agx_
ID: 34875336
Yeah, 3 tables is definitely the way to go.  It'll also be faster than the string/csv approach.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 52

Expert Comment

by:_agx_
ID: 34875369
>> 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
 
LVL 52

Expert Comment

by:_agx_
ID: 34875388
Ack... friday fingers.  This:

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

... should be

   ie WHERE ','+ columnName +',' like '%,{value},%'
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34875424

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
 
LVL 52

Expert Comment

by:_agx_
ID: 34875452
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
 

Author Comment

by:richard-mckenna
ID: 34875683
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now