CakePHP 2.4 fetch 10 records per event in single Select query

Question

I've made following code to fetch records of users per event but I want to fetch only top 10 users per event. Please guide how can I update my current query to get this:

$registrant = $this->RmrEventsRegistrant->find('all', array('conditions' =>
    array(
        array('RmrEventsRegistrant.event_id' => $event_ids)
   ),
   'fields' => array('RmrEventsRegistrant.id,RmrEventsRegistrant.first_name,RmrEventsRegistrant.last_name,RmrEventsRegistrant.email,RmrEventsRegistrant.event_id'),
   'order' => array('RmrEventsRegistrant.created'),
));

Where $event_ids is array of event_id.

Answer 1

First of all you need to Define the Relationship between your RmrEventsRegistrant Model and User Model. In your case Relationship is Event hasMany Users and it will be defined as below in your Event Model.

class RmrEventsRegistrant extends AppModel { 
    public $useTable = 'rmr_events_registrant'; 

    public $hasMany = array(
        'User' => array(
            'className' => 'User',
            'limit' => 10,
        )
    );

} 

Since in the hasMany Relationship you have defined the limit parameter as 10, you do not need to make any changes in the query that you have posted. Cakephp will automatically fetch 10 Users for each Event that it retrieves.

You may need to change the className of the User Model in hasMany association depending upon how you have defined it. Like your Event Model was defined as RmrEventsRegistrant.

You can read more about the Associations in Cookbook