CakePHP order not working

4
votes

Hi i am using CakePHP version - 2.5.5.

I have a table name chat_ategory_mages I want to get Average number of Frequency Order by Descending. Know about the Frequency please check - How to get Average hits between current date to posted date in MySQL?

chat_ategory_mages

id        chat_category_id    hits       created
------------------------------------------------
1         5                  10       2014-11-07 11:07:57
2         5                  8        2014-11-10 05:10:20
3         5                  70       2014-10-04 08:04:22

Code

$order=array('Frequency' => 'DESC');
$fields=array(
    'ChatCategoryImage.id',
    'ChatCategoryImage.chat_category_id',
    'ChatCategoryImage.created',
    'ChatCategoryImage.hits',
    'hits/(DATEDIFF(NOW(),created)) AS Frequency',
);

QUERY-1

$rndQry=$this->ChatCategoryImage->find('all',array('conditions'=>array('ChatCategoryImage.chat_category_id'=>$cetegory_id), 'fields'=>$fields, 'order'=>$order, 'limit'=>10));
pr($rndQry); //WORKING FINE

QUERY-2

//THIS IS NOT WORKING
$this->Paginator->settings = array(
        'conditions'=>array('ChatCategoryImage.chat_category_id'=>$cetegory_id),
        'fields'=>$fields,
        'limit' => 10,
        'order' => $order,
);
$getCategoryImages = $this->Paginator->paginate('ChatCategoryImage');
pr($getCategoryImages); //NOT WORKING

Above table if i write simple cakephp query the order is working fine but when i am using cakephp pagination it is not working. If i am using $order=array('hits' => 'DESC'); this its woring perfect. Showing result 70,10,8 consistently but when i am adding Frequency it the result not coming the descending order.

Mysql Query

QUERY-1 :

SELECT ChatCategoryImage.id, ChatCategoryImage.chat_category_id, ChatCategoryImage.hits, ChatCategoryImage.created, hits/(DATEDIFF(NOW(),created)) AS Frequency, FROM myshowcam.chat_category_images AS ChatCategoryImage WHERE ChatCategoryImage.chat_category_id = 5 ORDER BY Frequency DESC LIMIT 10

QUERY-2 :

SELECT ChatCategoryImage.id, ChatCategoryImage.chat_category_id, ChatCategoryImage.hits, ChatCategoryImage.created, hits/(DATEDIFF(NOW(),created)) AS Frequency, FROM myshowcam.chat_category_images AS ChatCategoryImage WHERE ChatCategoryImage.chat_category_id = 5 LIMIT 10

What is the problem and why its not coming ORDER BY Frequency in the second query?

Thanks chinu

3
phpmysqlcakephp

3 Answers

1
votes

You can use virtualFields

$this->ChatCategoryImage->virtualFields = array('Frequency' => 'hits/(DATEDIFF(NOW(),created))');

changing the way of order

$order = array('Frequency' => 'desc');
0
votes

This happened to me to. You have to add to the paginate function the third parameter $whitelist. For example.

$this->Paginator->settings = array(
        'conditions'=>array('ChatCategoryImage.chat_category_id'=>$cetegory_id),
        'fields'=>$fields,
        'limit' => 10,
        'order' => $order,
);
$scope =  array();
$whitelist = array('ChatCategoryImage.id', ...); //The fields you want to allow ordering.

$getCategoryImages = $this->Paginator->paginate('ChatCategoryImage', $scope, $whitelist);
pr($getCategoryImages);

I do not know why this is happening. I tried to see the code inside the paginate function but i could not figure it out.

-1
votes

Your code was lil wrong

    $this->paginate = array(
                   'conditions' => array('ChatCategoryImage.chat_category_id'=>$cetegory_id), 
                   'limit' => 10, 'order' => 'Frequency' => 'DESC');
    $getAllCourses = $this->paginate('ChatCategoryImage');