Object Oriented approach to retrieve data from two related tables using coldfusion

Question

I have a test results database which has the following three tables along with others:

testresults - id, buildid, osid, pass, fail
build - id, build
os - id, os

I generated CFCs using the Adobe CFC generator that comes along with coldfusion builder. Now I have value objects for each table and CRUD methods. I am trying to retrieve all test results records for a particular build and os. Since I want to display the build and os information, I would need information from build and OS tables. Is there a object oriented approach to fetch these results without having to write queries with joins or create views?

Answer 1

I don't know if you have heard about ORM (object-Relational mapping) and if you are using ColdFusion 9 then you just check it out.

ColdFusion ORM

Answer 2

Vikas' answer is worth looking into - the CF 9 ORM will do much of the work for you, but under the hood it's going to need to query all three tables and likely do joins. I haven't used CF Builder, so I don't know what those objects look like, but I suspect they'll snap right into the ORM:

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSD628ADC4-A5F7-4079-99E0-FD725BE9B4BD.html

The "OO approach" will still need to join the data somehow - how you do that is up to you. The important thing is that the Object is a container for the data you want and it doesn't concern itself with how it gets that data or where it comes from.

At a high level, the OO approach is to decouple your object model from the database. You'll probably want a TestResults object which contains the data and methods you want. (e.g. getTestResultsByBuild(build), getTestResultsByOS(os), etc.) You may also end up with an object to handle the SQL queries and pass the data to the TestResults object (probably composed into TestResults). This would be your Data Access Object (DAO) and it abstracts and encapsulates the database access functions.