Using cakephp model to handle pagination in custom query

Question

I have 10 subscriber tables from subscriber_0 to subscriber_9, I get all the data from them through union by custom query in subscriber model, in the function getAllSubscribers, this table executes the query and returns the result to the subscriber controller, I have set $useTable = false because there is no subscriber table, my question is how do I paginate this data in the controller?

For reference I am writing my query here and the result returned Query:

 $query = 'SELECT * FROM (
            SELECT * FROM subscriber_1
            UNION 
            SELECT * FROM subscriber_2
            UNION 
            SELECT * FROM subscriber_3
            UNION 
            SELECT * FROM subscriber_4
            UNION 
            SELECT * FROM subscriber_5
            UNION 
            SELECT * FROM subscriber_6
            UNION 
            SELECT * FROM subscriber_7
            UNION 
            SELECT * FROM subscriber_8
            UNION 
            SELECT * FROM subscriber_9
            UNION 
            SELECT * FROM subscriber_0
            ) AS subscriber WHERE created > \'' . $startDate . '\' AND created < \'' . $endDate . '\'';

Result:

array(
(int) 0 => array(
    'subscriber' => array(
        'a_party' => '923003210861',
        'subtype' => '0',
        'stat' => '0',
        'created' => '2012-11-26 06:53:31',
        'updated' => null
    )
),
(int) 1 => array(
    'subscriber' => array(
        'a_party' => '923005264511',
        'subtype' => '0',
        'stat' => '0',
        'created' => '2012-11-26 06:53:31',
        'updated' => null
    )
)
,
.
.
.
.(int) 50 => ...

I have added this in the subscribers controller

    public $paginate = array(
    'limit' => 10
);

What else do I need to add?

I have seen this but of no help

CakePHP pass custom query from controller into model paginate()

EDIT: Adding controller code below

            $data = $this->Subscriber->getAllSubscribers();//This model method returns custom data from query
        $this->paginate = array('fields' => $selectedField);
        $paginatedData = $this->paginate($data);
        //debug($paginatedData);

        $this->set('subslist', $paginatedData);

The above code is not working, what am i doing wrong, thanks

shabby shabby · Accepted Answer · 2012-11-28T14:55:30

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());
}

Using cakephp model to handle pagination in custom query

CakePHP pass custom query from controller into model paginate()

2 Answers