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:
- Created a query object with this code:
- Query to get dataset1
- Initialize a counter variable (‘i’) to keep track of the rows in my query object
- Add dataset1 to the query object:
- Query to get dataset2
- Add dataset2 to the query object:
- 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. 🙂