What's your opinion: database views VS complex join conditions?

Hi all,

In attempt to solve a particular problem, I've tried a solution which
to me, a CakePHP novice, seems a bit un-Cake like. I wanted to ask
your opinion here on whether a) this matters; and b) if it does
matter, what's the Cake-friendly way to do it?

Here's a summary of my models and their relationships:

I have a model for Jobs. I have a model for Employees. Over time, one
Job has different Employees occupying it. This information is stored
in a joining table JobOccupations (with a foreign key to the Job, the
Employee, plus additional details like start date, end date, etc).

Job hasMany JobOccupation
JobOccupation belongsTo Job
Employee hasMany JobOccupation
JobOccupation belongsTo Employee

To query the database for the "latest" employee (i.e. the last
employee to occupy each job) I need to do something like this:

SELECT JO1.id, JO1.job_id, JO1.employee_id
FROM job_occupations JO1
LEFT JOIN job_occupations JO2
ON JO1.job_id = EO2.job_id
AND JO1.id < JO2.id
WHERE JO2.id IS NULL

This will select the records of the job_occupations table with the
maximal primary key id for any given job id. (Because records are
inserted sequentially, this guarantees that the maximal id belongs to
the most recent record.)

So in database world, to execute this query easily and use its
information with other tables in the database, I created a stored view
called "job_latest_occupations". Then I can join this view with other
views or tables to get all the employee or job details for only the
most recent occupations.

Now, I want to report on this information in my Cake application. So I
created a Model "LatestJobOccupation" based off the database view
(actually, not the view I showed above, but a more complex one which
incorporates that view and joins onto another 6 or so tables). This
model has no relationships to other models.

To report on the data, I just run find queries on this model. Because
the view just pulls in every field from all the joined tables, I get
convenient access to all the fields in the one result record (and not
split into different associative arrays based on the table name --
although that still happens when I specify conditions on the query
strangely). I get away with not having to specify hairy join
conditions.

So I guess my question is -- is this wrong? Should I be trying to do
it the more Cake way (I'm not sure if such a thing exists)? Or should
I be utilising database features wherever possible? What would you do?

Cheers

Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions.

You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en