Using cakephp model to handle pagination in custom query

cakephp-2.0, pagination

Solution

Here is how I achieved it: Model code

/**
 * Overridden paginate method - group by week, away_team_id and home_team_id
 */
public function paginate($conditions, 
    $fields, 
    $order, 
    $limit, 
    $page = 1, 
    $recursive = null, 
    $extra = array()) {

    $recursive = -1;
    $sql = "SELECT * FROM (
        SELECT * FROM blacklist_0
        UNION
        SELECT * FROM blacklist_1
        UNION 
        SELECT * FROM blacklist_2
        UNION 
        SELECT * FROM blacklist_3
        UNION 
        SELECT * FROM blacklist_4
        UNION 
        SELECT * FROM blacklist_5
        UNION 
        SELECT * FROM blacklist_6
        UNION 
        SELECT * FROM blacklist_7
        UNION 
        SELECT * FROM blacklist_8
        UNION 
        SELECT * FROM blacklist_9
        UNION 
        SELECT * FROM blacklist_0
    ) AS Blacklist LIMIT " . (($page - 1) * $limit) . ', ' . $limit;

    $results = $this->query($sql);
    return $results;
}

/**
 * Overridden paginateCount method
 */
public function paginateCount($conditions = null, 
    $recursive = 0, 
    $extra = array()) {

    $sql = "SELECT * FROM (
        SELECT * FROM blacklist_1
        UNION 
        SELECT * FROM blacklist_2
        UNION 
        SELECT * FROM blacklist_3
        UNION 
        SELECT * FROM blacklist_4
        UNION 
        SELECT * FROM blacklist_5
        UNION 
        SELECT * FROM blacklist_6
        UNION 
        SELECT * FROM blacklist_7
        UNION 
        SELECT * FROM blacklist_8
        UNION 
        SELECT * FROM blacklist_9
        UNION 
        SELECT * FROM blacklist_0
    ) AS Blacklist";

    if ($conditions['Blacklist.b_party'] <> null) {
        $sql = $sql . ' WHERE Blacklist.b_party = \'' . $conditions['Blacklist.b_party'] . '\'';
    } else if ($conditions['Blacklist.a_party'] <> null) {
        $sql = $sql . ' WHERE Blacklist.a_party = \'' . $conditions['Blacklist.a_party'] . '\'';
    }

    $this->recursive = $recursive;
    $results = $this->query($sql);

    return count($results);
}

Not to forget i used

public $useTable = false;

in the model, but the key thing was how to override the methods, which i finally figured out with the help of my collegue, the controller code is simple. Here it is: Controller code:

public function index() {
    $this->set('msisdn', "");
    $this->Blacklist->recursive = 0;
    $this->set('blacklists', $this->paginate());
}

Problem

Original source