Well, I can think of a few ways to achieve the desired result you're looking for. First of all, you should be doing that 'last_job' key creation in your Project model's afterFind($results) callback if you're going to go down the array manipulation route.
In regards to pagination, you could do it yourself manually using custom pagination. You'd pass in http://www.yoursite.com/projects/index/sort:last_hour/ and in the controller look for $this->params['named']['sort'] == 'last_hour' and organize your returning pagination accordingly. However, I think the cleanest way I would approach this is with a beforeSave() either in the Project or Job model that creates a 'last_job' key for that project and saves it to the database. in the Job model it would update its parent Project on any new job created under that project, or in the Project model it would only update upon updating the parent project. Not knowing your application I would suggest the Job model that way you can be sure your Project's last_job is always correct on job creations/updates. This way you'd be able to use database sorting which is always faster. All your PHP intensive array organization would only occur on a save, not on every retrieval of the data as you're currently doing. Here's some reading on Model Callbacks: http://book.cakephp.org/view/76/Callback-Methods Hope that helps, Nick On Dec 4, 4:47 am, otherphil <otherphi...@gmail.com> wrote: > hi, this is my first post. I am new to cakePHP (and databases in > general). > > I have built a little application with a table called Projects and a > table called Jobs. > > a project has many Jobs. > > a job has (amongst others) a 'hours' field and a 'date' field. > > I have an index page for the projects which lists all the projects, > showing the name, the last job and the total hours for that project. > There is a paginator whichs breaks up the results. At the moment I am > finding the last job and the total hours by searching through the > results after they are returned (see code below). > > My problem is that I want to be able to order the results by the last > job or by the total hours. Can someone explain the best way to do this > please. My only ideas are > > 1. that I can manually re-sort the array returned by the find query > but I have no idea how this would work with the paginator, or > 2. to use 2 new fields on the projects table - total hours and last > job - but it seems like this would be redundant information which can > be found in the jobs table. > > below is my code in the Projects controller. Everything works (there > are no errors) I just need to know how to do what I mentioned above. > > Thanks very much for any help you can give. I'm sorry if this has been > answered before (I did look) > > - Phil > > function index() { > > $this->Project->Behaviors->attach('Containable'); > $this->Project->contain(array('Job.date', 'Job.hours', > 'Client.name')); > > $conditions = array(); > > //$projects = $this->Project->find('all', array('order' => > 'Project.name DESC', 'conditions' => $conditions)); > $projects = $this->paginate('Project', $conditions); > > // for each project find the last job and record it in the array > for ($i = 0; $i<count($projects); $i++) { > > $proj_id = $projects[$i]['Project']['id']; > > // create a new member of the Project array for this project and > give it a default value of 'never' > $projects[$i]['Project']['last_job'] = "Never"; > > foreach ($projects[$i]['Job'] as $job) { > if ($projects[$i]['Project']['last_job'] == "Never" || > $projects[$i]['Project']['last_job'] < $job['date']) { > $projects[$i]['Project']['last_job'] = $job['date']; > } > } // end job loop > } // end project loop > > $this->set('projects', $projects); > > } // end index 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+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
