Solved

Select row if value in list using cfquery of query

Posted on 2011-02-11
10
1,008 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
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…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

22 Experts available now in Live!

Get 1:1 Help Now