My quick and dirty response is that if this view is essentially only used for view operations (in other words you're not updating the data) and you don't need to do any joins at the model level, then if it works your approach is the right one. The database is designed to do these complex joins so I'd say you were leveraging its native power.
Jeremy Burns Class Outfit [email protected] http://www.classoutfit.com On 8 Oct 2010, at 03:36, Michael T wrote: > 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 [email protected] > To unsubscribe from this group, send email to > [email protected] For more options, visit this group at > http://groups.google.com/group/cake-php?hl=en 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
