CakePHP paginate and order by

cakephp pagination search filter
cakephp 3 pagination sort associated model
unable to locate an object compatible with paginate.
cakephp query
cakephp pagination css
cakephp order by
paginate laravel
cakephp add condition to pagination

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. :/

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.

Pagination - 2.x, Custom Query Pagination; Control which fields used for ordering; Limit Pagination in CakePHP is offered by a component in the controller,  CakePHP eases the burden on the developer by providing a quick, easy way to paginate data. Pagination in CakePHP is offered by a component in the controller, to make building paginated queries easier. In the View View\Helper\PaginatorHelper is used to make the generation of pagination links & buttons simple.

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.

Paginator - 3.8, The PaginatorHelper is used to output pagination controls such as page If the resultset is sorted 'asc' by the specified key the returned link will sort by 'desc'. Cake PHP Use Conditions and Order By in Paginate Tag: pagination , cakephp-2.0 I'm trying to use Conditions and Order By in cake PHP paginate function of the Paginator helper

try

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

CakePHP paginate and order by, Control which fields used for ordering $this->paginate('Post', array(), array('title', 'slug')); So order is the third argument. You need to pass in the conditions key when using multiple filters (i.e. order, limit). Today I’m going to show you how easy it is for CakePHP to do pagination, sorting and displaying data from the database (I’m using MySQL). Pagination is useful if you have many rows of data, image if you have thousands of records, your page would load slow and it will be very inconvenient for your users to browse that page.

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) 

Pagination: field names with `order` and `sortWhitelist` · Issue , I need some clarification for pagination, using models that have associations and a custom query. Example: I'm cakephp / cakephp · Watch 622 · Star Pagination: field names with `order` and `sortWhitelist` #10028. Closed. I need some clarification for pagination, using models that have associations and a custom query. Example: I'm paginating posts. Posts belongs to posts categories, belongs to users and have many tags.

Normally Pagination in CakePHP uses Named Parameters. There are times you want to use GET parameters instead. There are times you want to use GET parameters instead. While the main configuration option for this feature is in PaginatorComponent , you have some additional control in the view.

When an you click a sort link, this field is being sorted first. If the default sort order already contains the order field, it will not overwrite the sort defined by the query. The first item of the default sort order is always the one that is used for creating the URL, so it works for all pages.

CakePHP 4.0.0 is as tasty as other major CakePHP releases but will now require you to use PHP 7.2. With a refreshed application skeleton design, CakePHP 4.0.0 comes with a streamlined API making your development and application faster. While 4.0 contains a number of breaking changes we have prepared

Comments
  • the query runs, but it doesn't order it :/
  • @cubsink can you look at new generated SQL?
  • accepted your answer since it was correct and you were first :)
  • 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']
  • Just realized that public paginate was already set at the top with an order statement. Stirred myself blind there for a while. Thanks.