Cake PHP paginate multiple tables of one model-Collection of common programming errors

community, there is a problem with multiple tables which receive their entries of the same model “Post” on the same page. When clicking a paginator-number the app will change the page for both tables.

I tried it with dummy classes in the AppModel which should work but I get an error saying an internal error has occurred (error 500). I found out that there is a problem with the corresponding SQL-statement with a not found row.

In the AppModel:

class PostHood extends Post {
    public $useTable = 'posts';
};

That code uses the table “cake_posts” of “class Post extends AppModel”. In the controller I tried to get the PostHood like following:

$this->paginate['PostHood'] = array(
    'conditions' => array('OR' => array(stuff)),
    'limit' => 5
);
$this->set('postsHood', $this->paginate('PostHood'));

In the view there is a foreach-loop using the $postsHood as $post. Maybe you have an idea, thanks in advance 🙂

EDIT 1: I got some error notices after changing the code. May be you have an idea what to do. Change of the AppModel:

class PostHood extends AppModel {
    var $name = 'Post';
    public $useTable = 'posts';
};

The Controller:

$this->loadModel('PostHood');
$this->paginate = array(
    'conditions' => array('OR' =>
    array(
        array('AND' => array(   
        array('PostHood.ZIPCODE LIKE' => $userArea . '%'),
            array('PostHood.ALTDATE >' => date("Y-m-d")),
        array('PostHood.AGENT' => '0'),
        array('PostHood.OWNER ' => $this->UserAuth->getUserId()),
        array('PostHood.PARENTID' => '0'),
        array('PostHood.ACCEPTED' => '0')
    )), [MORE AND ARRAYS]
$this->set('postsHood', $this->paginate('PostHood'));

The corresponding error in the view:

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Post.DATE' in 'order clause'

SQL Query: SELECT PostHood.id, PostHood.B/S, PostHood.H, PostHood.CITY, PostHood.MARKET, PostHood.DATE, PostHood.ALTDATE, PostHood.TIME, PostHood.INCOME, PostHood.ZIPCODE, PostHood.ALIAS, PostHood.VEHICLE, PostHood.DELIVERYAREA, PostHood.SPACE, PostHood.STREET, PostHood.HOUSENUMBER, PostHood.NAME, PostHood.CART, PostHood.TEL, PostHood.OWNER, PostHood.created, PostHood.modified, PostHood.AGENT, PostHood.EXTRADATA, PostHood.PARENTID, PostHood.BRATED, PostHood.SRATED, PostHood.REQUESTED, PostHood.ACCEPTED FROM usr_web126986_4.cake_posts AS PostHood WHERE 1 = 1 ORDER BY Post.DATE asc LIMIT 5

Obviously Cake tries to fetch data with “PostHood” but the table “posts” which I actually want to use is listening to “Post.field”. How can I fix that? Thanks 🙂

1. You should try something like this:

   $this->paginate = array(
       'conditions' => array('OR' => array(stuff)),
       'limit' => 5
   );
   $this->set('postsHood', $this->paginate('PostHood'));
   

   I am just curious as to why you want to structure your model extending another model.

2. Got it working with the great plugin DataTables. That overrides the cake-pagination and uses JSON to display the results.

   In order to implement it into cake you need

   1.) a cake component for interpreting sql-statements: https://github.com/cnizzdotcom/cakephp-datatable

   2.) the plugin: http://www.datatables.net/index

   3.) implementation:

   $this->paginate = array(
       'fields' => array('Model.field1', 'Model.field2'),
       'conditions' => array( stuff )
   );
   $response = $this->DataTable->getResponse();
   $this->set('response', $response);
   $this->set('_serialize','response');
   $encode = json_encode($response);
   $this->set('dataTablesData', $encode);
   

   Then you can grab the data in the view.