Solved

Select row if value in list using cfquery of query

Posted on 2011-02-11
10
1,020 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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