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
