I recently started working on converting my messy, legacy, procedural code to CFC's. Lets just say I love CFC's. It makes coding a joy, and maintenance a breeze.
I wrote one component for categories. When you load this component, it loads all information for this category, including a list all sub-categories and products in this category. The number of products in the category are used in various places throughout the site when I render a link to the category, so I pull the products when the category is loaded.
One of the features of the site is a price filter, allowing you to view products within a specific price range. Because I load all the products when the category is loaded, rather than going back to the database to get the products matching the filter, I use Query of Queries to get only the matching products from the original query.
However, a customer pointed out that the price filter returning incorrect results. Say you filtered the results to view products under $20, it would show all products where the price began with a 1. The customer, who is a web developer for a large accouting firm, suggested that the price was being stored as a text string, and this result was because it was doing a text comparison, not a numeric comparison. I know that the database is storing it as a decimal, but it got me thinking about my Query of Queries, and how Coldfusion is assigning data types to the columns.
A little research showed me a big issue. Coldfusion MX does not allow you to assign the data type of the columns in a query (later versions do allow you to, but I haven't tested it with Query of Queries.) There is also almost no documentation about which Coldfusion functions can be used in QoQ, aside from UPPER() and LOWER(). And for reasons I'm not quite sure about, possibly because the field had 4 numbers after the decimal??, Coldfusion decide my price was a text field, and there was nothing I could do about it.
To solve this, I have to something thats truly a last resort. I actually loop through all the products, build a list of matching products and the QoQ pulls only those products. I know thats not the "right" way to do things, but I really wanted to avoid having to run another query to pull the products from the database.
Item of the Day: Blancodiamond 511-709 - Blanco Sink Silgranit
