How to limit the the number of row retrieved from an associated table in CakePHP?

Question

Assuming two database tables: Funds and Prices, in which Funds hasMany Prices.

What I wanted to do is to retrieve the latest 15 prices of a particular fund in a certain scenario. Is there a means in CakePHP to make a $this->Fund->find('all') call that would allow me to limit the number of rows to be retrieved from the associated Price table?

Note that I prefer not setting the 'limit' option in the Fund model's $hasMany variable.

Note on accepted answer [Nov 2]:

In Jason's answer which I had accepted, I personally opt for the bindModel solution as I felt despite feeling a bit “hack-y”, it bodes much better with me as to make a one-off override on the default Model bindings.

The code I used is as follows:

$this->Fund->bindModel(array(
    'hasMany' => array(
        'Price' => array(
            'limit' => 15,
            'order' => 'Price.date DESC'
        )
    )
);

No unbindModel is necessary. More information could be read from “3.7.6.6 Creating and Destroying Associations on the Fly” in the CakePHP manual.

Answer 1

As I understand the question, you don't want to set the limit statically in the model. If you wish to use a hasMany association, there really isn't any other way that I'm aware of other than changing that limit one way or another.

Here are a few ways to dynamically change it:

1. You could change the limit with a call to Funds->bindModel(). (This may require an unbindModel() first, but I can't remember for certain)
2. You could add a function to Funds to change the limit directly. (See example below.)
3. If you're feeling ambitious, you could write a Behavior to add the functionality to specify it per find() call similarly to the Containable behavior.

Example for #2

<?php
function setPriceLimit($limit = 10) {
     // Assuming the association is 'Price'
     $this->hasMany['Price']['limit'] = $limit;
}
?>

Answer 2

Your $this->Model->find('all') could include conditions. ie.

$this->Model->find('all', array('conditions' => array('Model.field' => $value), 'limit' => 15));

For more info check the cake docs.

Retrieving Your Data

Answer 3

You can use the Containable behavior to accomplish this easily.

• in your AppModel or Fund model add :

  var $actsAs = array('Containable');

• then in your controller you can add the 'contain' option to your find('all') :

  $this->Fund->find('all', array(
      'contain' => array(
          'Price' => array(
              'limit' => 15,
              'order' => 'Price.date DESC')
  )));
  

More information is available in the book : http://book.cakephp.org/view/474/Containable