Solved

Select row if value in list using cfquery of query

Posted on 2011-02-11
10
1,014 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Build PDF in coldfusion 2 79
Mixing cold fusion headers and footers with PHP body 6 65
Fixing Old Legacy Code 1 41
Lucee & <cftransaction.... />  WITAF? 10 17
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…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 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