Join Query in Object Oriented Programming

[Guest]

Hi,

I am trying to understand better how OO programming should work in CFC context.  For example, I have two database tables: Customer and Order.  So I create two CFCs, one for Customer and another for Order.  In the CFCs I have query functions (select, insert, update) to access and manipulate data in the underlying tables.

Now, I need to create a new CFC, OrderReport.  This CFC takes in customerID and returns data pulled from both Customer and Order tables.  I can just have a join query that pulls data from these two tables.  However, I have been wondering whether this method is within the spirit of Object Oriented programming.  Should this CFC be able to access directly to the two tables?  Or should I pull data separately using Customer CFC and Order CFC, and join them locally (ie. in OrderReport CFC)?  This latter method would be a lot slower to run than the first method.

Can you advise me as to what the best practice is in the context of OO programming?  Thank you.

[Adam Cameron.]

Read up on "ORM" & specifically "Hibernate", then read the docs on of CF implements Hibernate integration:

ColdFusion ORM:

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

--

Adam

[DanWilson_]

This is a common question for those new to OO programming. Here are some of

my thoughts on the topic.

OO programming has nothing to do with tables. Your tables are essentially a

relational storage of one part of a business concept. It just so happens

that most of the data you need to support the business concept of Customer

and the business concept of Order are stored in tables named Customer and

Order. This is because database tables often relate to a business concept. I

think you'd find that to get the entire customer, you'd have to get data

from other tables, like State and Gender and other such tables.

The reason why I bring this up is to begin to separate your thinking.

Databases are concerned with efficient storage and retrieval of data.

Objects (CFCs) are not concerned at all with storage of data. Objects are

concerned with encapsulating business logic relating to a business concept.

Just like your database may have columns not needed by your CFCs, your CFCs

likely have methods not needed by your database.

For example, if you wanted to know, in your application, whether to show an

In Store Pickup option, you may wish to add an isLocal() method to your

customer object. This would ( we'll pretend) get the customer's zip code,

and look it up in GeographicalPostalServiceMapper object to tell us how far

away the customer is.

The point is, the right OO (CFC) design has nothing to do with how your

tables are organized in the database. You would do well to concern yourself

with the needs of the application and what sort of questions you need your

customer object to be able to answer to the other parts of your application.

Like, isLocal(), isOver21(), isBadCreditRisk() and so on.

As to your question about joins, you would do well to use joins in your

application. Do not be afraid of using queries to get the information you

need. Especially for reporting queries. In these cases, I often make an

object called a xxxList. My CustomerList.cfc would have the different ways

to list customer data, like CustomerOrders and CustomerOrderReturns. I'd

hide this join relationship inside of the CustomerList object so only the

CustomerList object has the SQL. That's usually enough encapsulation for the

needs of my application.

Truly, it doesn't matter what the name of the object is, just that you

assign it the responsibility of managing a business concept and keep that

business concept inside that object. You seem to have suggested

CustomerReport.cfc and that would be a fine name for an object that can

return numerous Customer Reports.

Happy Coding

DW

[Guest]

@Adam: Thanks for the tip.  I have read a little bit about ORM but have not quite grasped the concept.  Will give it a good read now.

@Dan: Thanks for the clear explanation.  I think that's essentially what I have been doing: organizing the CFCs around business logic, so it's quite a relief.  I thought I was doing something wrong from OOP perspective.