CakePHP paginate and order by

Question

It feels like I've tried everything so I now come to you.

I am trying to order my data but it isn't going so well, kinda new to Cake.

This is my code:

$this->set('threads', $this->paginate('Thread', array(
        'Thread.hidden' => 0,
        'Thread.forum_category_id' => $id,
        'order' => array(
            'Thread.created' => 'desc'
        )
    )));

It generates an SQL error and this is the last and interesting part:

AND `Thread`.`forum_category_id` = 12 AND order = ('desc') ORDER BY `Thread`.`created` ASC LIMIT 25

How can I fix this? The field created obviously exists in the database. :/

score 9 · Answer 1 · answered Dec 10 '12 at 20:41

You need to pass in the conditions key when using multiple filters (i.e. order, limit...). If you just specify conditions, you can pass it as second parameter directly.

This should do it:

$this->set('threads', $this->paginate('Thread', array(
        'conditions' => array(
            'Thread.hidden' => 0,
            'Thread.forum_category_id' => $id
        ),
        'order' => array(
            'Thread.created' => 'desc'
        )
    )));

or perhaps a little clearer:

$this->paginate['order'] = array('Thread.created' => 'desc');
$this->paginate['conditions'] = array('Thread.hidden' => 0, ...);
$this->paginate['limit'] = 10;
$this->set('threads', $this->paginate());

if you get an error, add public $paginate; to the top of your controller.

Just realized that public paginate was already set at the top with an order statement. Stirred myself blind there for a while. Thanks. — Ms01, Dec 10 '12 at 20:54

Valera Leontyev · Accepted Answer · 2012-12-10T20:34:38.937

3

Try

$this->set('threads', $this->paginate('Thread', array(
        'Thread.hidden' => 0,
        'Thread.forum_category_id' => $id
    ),
    array(
        'Thread.created' => 'desc'
    )
));

I'm not a Cake master, just a guess.

EDIT. Yes, thats right. Cake manual excerpt:

Control which fields used for ordering ... $this->paginate('Post', array(), array('title', 'slug'));

So order is the third argument.

edited Dec 10 '12 at 20:34

answered Dec 10 '12 at 20:29

Valera Leontyev

1,191
6
14

the query runs, but it doesn't order it :/ – Ms01 Dec 10 '12 at 20:35
@cubsink can you look at new generated SQL? – Valera Leontyev Dec 10 '12 at 20:37
accepted your answer since it was correct and you were first :) – Ms01 Dec 10 '12 at 20:55
It's little bit wrong answer. Third parameter is `whitelist` so you can specify only couple of fields that can be ordered thru named param `sort` in your pagination helper. But actual sorting param still needs to be set in options array (second param) or in $this->pagination['Model']['order'] – Yaroslav Oct 08 '14 at 21:04

score 0 · Answer 3 · answered May 24 '18 at 04:53

0

try

$all_threads = $this->Threads->find('all', 
            array(
                'order' => 'Threads.created'
            )
        );
        $saida = $this->paginate($all_threads,[
            'conditions' => ['Threads.hidden' => 0]
        ]);

answered May 24 '18 at 04:53

Marcos Vicente Siqueira

1
1

score 0 · Answer 4 · answered Jul 12 '18 at 00:36

There are a few things to take note of in paginate with order. For Cake 3.x, you need :

1) Ensure you have included the fields in 'sortWhitelist'

$this->paginate = [

        'sortWhitelist' => [
            'hidden', 'forum_category_id', 
        ],


    ];

2) for 'order', if you put it under $this->paginate, you will not be able to sort that field in the view. So it is better to put the 'order' in the query (sadly this wasn't stated in the docs)

$query = $this->Thread->find()
->where( ['Thread.hidden' => 0, 'Thread.forum_category_id' => $id, ] )
->order( ['Thread.created' => 'desc'] );

$this->set('threads', $this->paginate($query)

CakePHP paginate and order by

4 Answers4

Linked