Object oriented representation of multi-table query

Question

Hi all,

Suppose we have two related tables, for example one representing a person:

PERSON

• name
• age
• ...
• current_status_id

and one representing a status update at a specific time for this person:

STATUS_HISTORY

• recorded_on
• status_id
• blood_pressure
• length
• ...

I have built an application in PHP using Zend Framework, and tried to retain 'object orientedness' by using a class for representing a person and a class for representing the status of a person. I also tried to use ORM principles where possible, such as using the data mapper for separating the domain model from the data layer.

What would be a nice (and object oriented) way of returning a list of persons from a data mapper, where in the list I sometimes want to know the last measured blood_pressure of the person, and sometimes not (depending on the requirements of the report/view in which the list is used). The same holds for different fields, e.g. values computed at the data layer (sum's, count's, etc.).

My first thought was using a rowset (e.g. Zend_Db_Rowset) but this introduces high coupling between my view and data layer. Another way might be to return a list of persons, and then querying for each person the latest status using a data mapper for requesting the status of a specific person. However, this will result in (at least) one additional query for each person record, and does not allow me to use JOINS at the data layer.

Any suggestions?

Answer 1

We have this same issue because of our ORM where I work. If you are worried enough about the performance hit of having to first get a list of your persons, then query for their statuses individually, you really have no other choice but to couple your data a little bit.

In my opinion, this is okay. You can either create a class that will hold the single "person" data and an array containing "status_history" records or suffer the performance hit of making another query per "person". You COULD reduce your query overhead by doing data caching locally (your controller would have to decide that if a request for a set of data is made before a certain time threshold, it just returns its own data instead of querying the db server)

Having a pure OO view is nice, but sometimes impractical.

Answer 2

Try to use "stdclass" class which is PHP's inbuild class, You can get the object of stdclass which will be created automatically by PHP and its member variable will be column name. So u can get object and get the values by column name. For example.

Query is

SELECT a.dept_id,a.dept_name,a.e_id,b.emp_name,b.emp_id from DEPT a,EMP b where b.emp_id=a.e_id;

Result will be array of stdclass objects. Each row represents one stdclass object.

Object

STDCLASS { dept_id; dept_name; e_id; emp_id; emp_name; }

You can access like
foreach($resultset as $row) { $d_id = $row->dept_id; $d_nam= $row->dept_name; $e_id = $row->e_id; $em_id= $row->emp_id; $e_nam= $row->emp_name; }

But

Blockquote

I am not sure about performance.