klenwell information services : Paste20090117

CakePhp Custom Pagination Methods

Model and Controller code needed to create pagination for a model that hasMany relationships. No effort has been made to abstract or generalize this code, but it should hopefully offer a bit clearer example than some of the existing examples out there for how this is done.

For more information: see http://www.klenwell.com/press/2009/01/cakephp-custom-pagination/

Model Code

$AssocHasManyModel in foreach loop below is the model associated with this model that has the field/column I want to filter on.

$assoc_hasmany_table in the paginateCount method is the table name for the associated model.
    function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array())
         What happens here: model retrieves all records, then loops through them
         to filter out the desired records based on the status values passed in
         the extra array argument.  Not the most efficient method, perhaps, but
         certainly simpler than and safer than crafting a custom SQL query.
         Note: the argument list is fixed by the CakePhp framework.  Use the extra
         argument if you need pass additional values.

        $Result = array();
        $PreResult = $this->find('all', compact('conditions', 'fields', 'order', 'limit', 'page', 'recursive'));
        foreach ( $PreResult as $Rec )
            if ( in_array($Rec['ExtagMod'][0]['status'], $extra['statuses']) )
                $Result[] = $Rec;

        #pr(array('paginate result' => $Result));
        return $Result;
    function paginateCount($conditions = null, $recursive = 0, $extra = array())
        $StatusList = ( isset($extra['statuses']) ) ? $extra['statuses'] : array(1);
        if ( !is_array($StatusList) ) $StatusList = array( $StatusList );
        $statuses = implode(',', $StatusList);
        $sql = <<<MYSQL
SELECT MAX( created )
FROM $assoc_hasmany_table
WHERE STATUS IN ( $statuses )
GROUP BY $assoc_hasmany_table_id_column


        $count = count($this->query($sql));
        #pr(array('paginateCount result' => $count));
        return $count;

Controller Code

Replace $model_name with your model name.
    var $paginate = array
        $model_name => array
            'recursive' => 2,
            'limit' => 10,
            'order' => array("$model_name.created" => 'DESC'),
            'conditions' => array(),
            // these will be part of the extra argument in your custom method
            'statuses' => array(0,1)

    // action method using paginator (simplified)
    function overview($status=null)
        // override the defaults above
        $StatusReq = ( !is_null($status) ) ? explode('-', $status) : $this->paginate[$model_name]['statuses'];
        $this->paginate[$model_name]['statuses'] = $StatusReq;
        // on to the view
        $this->set('RecordList', $this->paginate($model_name));