Thanks for the detailed response, ShadowCross. That's exactly what I was after. Now I read it, it all makes perfect sense.
T On Jan 24, 9:38 pm, ShadowCross <[email protected]> wrote: > Assuming > > ModelA hasMany ModelB > ModelB includes a date column called 'date' > > you could try: > > $this->ModelA->bindModel(array( > 'hasOne' => array( > 'MaxDateModelB' => array( > 'className' => 'ModelB', > 'foreign_key => 'model_a_id', > 'conditions' => 'MaxDateModelB.date IN (SELECT > MAX(ModelB_2.date) FROM ' . $this->ModelB->table . ' AS ModelB_2 WHERE > ModelB_2.model_a_id = ModelA.id' > ) > ) > ), false); > $this->paginate = array_merge($this->paginate, array( > 'order' = array('MaxDateModelB.date') > ); > $this->set('model_as', $this->paginate()); > > The first line creates a hasOne on-the-fly association with ModelB > using the alias of MaxDateModelB; note the conditions further > restricts the records by sub-selecting only the records with the > MAX(date) from ModelB (I used the alias 'ModelB_2' to avoid any > conflicts with the "hasMany ModelB" to avoid any confusion with the > "hasMany ModelB" association) that matches the original ModelA record > id. I avoid hardcoding the tablename of ModelB and use $this->ModelB->table > in case the tablePrefix variable is defined (either in ModelB > > or in AppModel). The $reset parameter for bindModel MUST be false, > since paginate() requires 2 queries. > The second line actually set the order using the new association > (MaxDateModelB.date). > > While I have used something similar to this (although I define the > hasOne in the model since I use it in several places), YMMV. You > should consider creating an (non-unique) index on ModelB using > (model_a_id, `date`) as the key for performance reasons. > > Another alternative, which will be faster if the projected number of > records in ModelB is significantly large, is to use a "cache" field in > ModelA similar to counterCache. Basically, add a field in ModelA > (i.e. max_model_b_date) that gets updated in the afterSave() method of > ModelB (i.e. > > $this->ModelA->updateAll( > array('ModelA.max_model_b_date' => $this->data['ModelB'] > ['date']), > array( > 'ModelA.id' => $this->data['ModelB']['model_a_id'], > 'ModelA.max_model_b_date < ' => $this->data['ModelB'] > ['date'], > ) > ); > > This alternative does require some "data conversion" if you already > have records in ModelB (you will need to populate the max_model_b_date > field, but may be do-able in a single SQL statement). > > Another consideration is whether the frequency of accessing the > index() action of the ModelAController is greater than the frequency > of accessing the add()/edit()/delete() actions of the > ModelBController. If the ModelA.index() action is going to be used > several times a day, but the ModelB actions only once a week, I would > use the "cache" field method, since the additional overhead on the > database is incurred when you insert/update/delete on ModelB. If, > however, you expect a lot of transactions on ModelB, but use > ModelA.index() infrequently (for example: ModelB represents individual > users' activity on a popular games site, including their scores, and > ModelA.index() is used for a weekly rankings report), I would go with > the custom hasOne method outlined earlier, since the "cache" field > method requires 2 sql updates to the custom hasOne's 1 sql update. -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
