Cake Php paginator issue

Question

I am facing a weired issue. I have written code for pagination. Everything is working as expected, but only conditions are not working(only certain conditions) Here is my code for pagination.

//declaration 
public $paginate = array(
        'limit' => 50,
        'paramType' => 'querystring'
    ); 
//use in action
$this->paginate['ApiLog'] = array('limit' => 50, 'order' => 'ApiLog.id DESC', 'paramType' => 'querystring');

$this->paginate['ApiLog']['conditions'] = array('ApiLog.log_type' => 2);
$this->paginate['ApiLog']['joins'] = array(               
            array(
                'table' => 'users',
                'alias' => 'User',
                'type' => 'LEFT',
                'conditions' => array('User.id = ApiLog.user_id')
            )                
        );
$this->Paginator->settings = $this->paginate['ApiLog'];
$apilogs = $this->Paginator->paginate('ApiLog');

This code is working prefect on development environment and return logs that has type 2, but in production it return only logs that has type 1. I have spent whole day to figure out issue. If i do add any other condition in conditions array that does take effect but not log_type. I print Query logs, in where clause it always show log_type = '1' I have cleared cache as well. Any help appreciated, thanks.

Christchurch

Try to delete your tmp/cache folder – Geminius

Answer 1

I have fixed issue, This issue was due to data type declared into database. In production it was tinyint(1) and in development it given int(1). It is saving correct value in database in both environment but in condition tinyint(1) is working with only 0 & 1 not with 2.

I didn't understand reason behind this.

Christchurch

Answer 2

If your code working fine in local machine but not working in production then you have to clear all files in tmp/cache/model and tmp/cahe/persistance. make sure you have given writable permission to tmp folder.

It means you have to update Cake Model schema when you add new field, remove existing field or make any changes in database schema. In production mode If schema cache file not found then it will create new cache files based on current schema on database. In development mode on every execution it will update schema cache.

Still its issue in pagination then you have to follow CakePHP 2.x documentation. Here I assume your code is in ApiLogsController and method is index. as per documentation your code should be.

public function index() {
    $this->Paginator->settings = array(
        'limit' => 50,
        'order' => 'ApiLog.id DESC'
        'paramType' => 'querystring',
        'conditions'=>  array('ApiLog.log_type' => 2),
        'recursive'=>-1, // should be used with joins
        'joins'=>array(
            array(
                'table'=>'users',
                'type'=>'LEFT',
                'alias'=>'User',
                'conditions'=>array('User.id = ApiLog.user_id')
            )
        )
    );
    $data = $this->Paginator->paginate('ApiLog');
    $this->set(compact('data'));
}

OR

// your default setting in ApiLogController class
public $components = array('Paginator');
public $paginate = array(
    'limit' => 50,
    'paramType' => 'querystring'
);

public function index() {
// overriding/extending default settings

$this->Paginator->settings['order']='ApiLog.id DESC';
$this->Paginator->settings['conditions']=array('ApiLog.log_type' => 2),
$this->Paginator->settings['recursive']= -1;
$this->Paginator->settings['joins']=array(
        array(
            'table'=>'users',
            'type'=>'LEFT',
            'alias'=>'User',
            'conditions'=>array('User.id = ApiLog.user_id')
        )
    );

$data = $this->Paginator->paginate('ApiLog');
$this->set(compact('data'));
}

Mufti

i have already cleared cache. Also my Paginator settings are correct. I do add any other condition in settings then it does work – Christchurch

Check in production with debug level 2 and display query bottom of your layout. – Mufti

There are four ways to show queries: This will show the last query executed of user model: debug($this->User->lastQuery()); This will show all executed query of user model: $log = $this->User->getDataSource()->getLog(false, false); debug($log); This will show a log of all queries: $db =& ConnectionManager::getDataSource('default'); $db->showLog(); If you want to show all queries log all over the application you can use in view/element/filename.ctp. <?php echo $this->element('sql_dump'); ?> – Mufti

i have seen query and it always include ApiLog.log_type = '1' in where clause – Christchurch

which code you are using your or mine option 1 or option 2? – Mufti