Dynamically creating query objects

  • Post author:
  • Post category:Uncategorized

In ColdFusion you can create data sets in memory called query objects. These can be queried with a limited set of standard SQL commands, which is very nice. Yesterday I wanted to do something I’ve never done before, and I had some problems so I thought I’d post my findings here for future reference.I wanted to query my database to get a dataset. Then I wanted to do a bunch of additional queries, but add their results to the first dataset (this is the part that was new to me). I knew this should be possible with CF, but I wasn’t sure how to do it. Here’s what I did:

  1. Created a query object with this code:
  2. Query to get dataset1
  3. Initialize a counter variable (‘i’) to keep track of the rows in my query object
  4. Add dataset1 to the query object:
  5. Query to get dataset2
  6. Add dataset2 to the query object:
  7. Repeat steps 5 and 6 as many times as necessary to fill up the data source.

You might be wondering why I would need to do this, or why I don’t just write a complex SQL statement. I needed to do this because I was dealing with a poorly designed database, and I had to jump through some loops to get my data in the format I needed. The above code is very simplified, my real code was doing a lot more than this. And I didn’t want to do a complex SQL statement because I prefer to code in CF when I can. 🙂