Cakephp: Paginator 'order' multiple fields not working on second page...

[urionlinejudge]

Hello,

I am migrating from Cake 2 to 3 and I am unable to make this part work. When I have multiple order by on the paginator and click on next, only the first parameter is sent in the URL. Is this no longer possible on the nee version?

       $this->paginate = [
            'order' => [
                'Products.color' => 'DESC',
                'Products.total' => 'DESC',
                'Products.name' => 'ASC'
            ],
            'limit' => 30
        ];

[markstory]

I don't think we ever supported sending multiple sort conditions in the URL. But you are correct that the sort URL parameter in 3.x only support a single column name.

edit: corrections.

[urionlinejudge]

Hi Mark,

I can tell you it worked fine on the previous version. However, the parameters were not sent in the URL (at least the default ones you define on the paginator settings). However, in Cake 3 even when you don't use the sort and just skip to the next page those parameters are included. It this behaviour correct?

[markstory]

As far as I know adding the sort key/direction is correct, as that is how the result set was originally sorted.

[urionlinejudge]

Hi Mark,

That is the thing! It works only for the first page (it sorts correctly). However, when I go to the second page, the URL changes and the original sorting (from the Controller) does not persists. It ignores the other fields...

[markstory]

Yes, but we don't currently have a way to represent multiple sort conditions in the query parameters, so only one sort field can be used on subsequent pages.

[urionlinejudge]

Hmm, that is not good! =/

I will have to come up with some alternative because several pages of our previous version (Cake 2) depend on the multiple sorting persisting...

[markstory]

You could always fix the paginator helper and submit a pull request. I doubt you will be the only one who wants multiple sort conditions. I know it has come up in the past. Alternately, the sort condition could be omitted if the default sort is being used.

[jeanbez]

I manage to get it working for now, without the URL in the parameters when the sort is the default one by not providing the complete name of the field on the sortWhitelist. You should use on the field name 'name' instead of 'Product.name'. Also this means you can sort by associated data, as mentioned in other tickets.

[arilia]

+1 for me.
cake 2 paginator does not add the sort key/direction to the next() link when the order option is set in the paginator array. So following @urionlinejudge example

echo $this->Paginator->next()

in cake 2 simply leads to

index/page:2

while cake3 produces

 index/?page=2&sort=Products.color&direction=desc

and this overwrites the default order conditions set in the controller per page 2.

I don't need cake to be able to manage multiple sort conditions in URLs. But it seems to me that cake2 behavior it would be more consistent. So it would be great if there were a way to force cake3 not to append the pagination order key as cake2 used to do

[krunaldave]

i am using default paginator function $this->paginate for display data with using join. its working well but between pagination i found some duplicate record in listing.it also displaying total no of record correct..and my database table has no dupliate record..dont know what is solution?? Here is my code:

$options['condition'] =some condition;
$options['joins'] = array(
            array(
                'table' => 'hoarding_categories',
                'alias' => 'HoardingCategory',
                'type' => 'LEFT',
                'conditions' => array(
                    'HoardingCategory.hoarding_id = Hoarding.id'
                )
            ),
            array(
                'table' => 'general_categories',
                'alias' => 'GeneralCategory',
                'type' => 'LEFT',
                'conditions' => array(
                    'HoardingCategory.category_id = GeneralCategory.id'
                ),
                'fields' => array('GeneralCategory.name')
            ),
            array(
                'table' => 'hoarding_images',
                'alias' => 'HoardingImage',
                'type' => 'LEFT',
                'conditions' => array(
                    'HoardingImage.hoarding_id = Hoarding.id',
                    'HoardingImage.status = 1',
                ),
                'fields' =>      array('HoardingImage.image_name','HoardingImage.status')
            ),
            array(
                'table' => 'contract_hoardings',
                'alias' => 'ContractHoarding',
                'type' => 'LEFT',
                'conditions' => array(
                    'ContractHoarding.hoarding_id = Hoarding.id',
                    'ContractHoarding.status = 1'
                )
            ),
            array(
                'table' => 'contracts',
                'alias' => 'Contract',
                'type' => 'LEFT',
                'conditions' => array(
                    'Contract.id = ContractHoarding.contract_id'
                ),
                'fields' => array('Contract.contractID')
            ),
            array(
                'table' => 'plan_hoardings',
                'alias' => 'PlanHoarding',
                'type' => 'LEFT',
                'conditions' => array(
                    'PlanHoarding.hoarding_id = Hoarding.id',
                    'PlanHoarding.is_deleted = 0',
                )
            ),
            array(
                'table' => 'plans',
                'alias' => 'Plan',
                'type' => 'LEFT',
                'conditions' => array(
//                    'Plan.id = Hoarding.plan_id',
                    'Plan.id = PlanHoarding.plan_id',
                    'Plan.is_plan_deleted = 0',
                    'Plan.campaign_status != 3'
                ),
                'fields' => array('Plan.display_name')
            )
            , 
            array(
                'table' => 'hoarding_mountings',
                'alias' => 'HoardingMountings',
                'type' => 'LEFT',
                'conditions' => array(
                    'HoardingMountings.hoarding_id = Hoarding.id',
                    'Plan.is_plan_deleted = 0'
                ),
                'fields' => array('HoardingMountings.id', 'HoardingMountings.hoarding_id')
            )
            , 
            array(
                'table' => 'location_employees',
                'alias' => 'LocationEmployee',
                'type' => 'LEFT',
                'conditions' => array(
                    'LocationEmployee.location_id = Hoarding.city_id',

                ),
                'fields' => array('LocationEmployee.*')
            ), 
            array(
                'table' => 'users',
                'alias' => 'User1',
                'type' => 'LEFT',
                'conditions' => array(
                    'User1.id = LocationEmployee.employee_id',
                ),
                'fields' => array('User1.name')
            )
        );

$fields = array('Hoarding.*','Hoarding.minimum_cost as total_minimum_cost','Hoarding.kiosk_qty as assigned_qty', 'Contract.contractID as contract_id', 'Plan.display_name as plan_name', 'HoardingImage.image_name as hoarding_image', 'HoardingImage.status as hoarding_image_status', 'GeneralCategory.name as category_id', 'User1.name as branch_manager_id');

$options['fields'] = $fields;
        $options['limit'] = $page_limit;
        $options['page'] = $page;
        $options['group'] = array('Hoarding.id');

$this->paginate = $options;//            
$data = $this->paginate('Hoarding'); ///Hoarding is my model name

dont know why some duplicate record happened randomly some times??also tried virtual fields,but it also give duplicate record some times and database table have no duplicate record?? thanks in advance..

[lorenzo]

Please don't use old issue to discuss your current one.

The reason you have duplicate records is because SQL joins duplicate rows pear each match using the conditions. If there is only one match for the given conditions, then no duplicates will be generated

[urionlinejudge]

@lorenzo That could also be related to new information included in the paginator link by Cake 3. I also noticed this because as you click on next, other sort conditions (the default ones you set on the controller paginator options) that were respected on previous versions are kept. If you manually remove the sort parameters added on these URLs (when you click on the link for page 2, for instance) you get the default sort of the controller, which is the expected and correct behaviour. This change of the sort conditions make some records appear in two pages because the sort order defined in the controller are not respected due to the URL parameters that were automatically included. I am not sure I was clear :)

[lorenzo]

@urionlinejudge in what cakephp version?

I mean, it would help to know the exact cake version you are using

[urionlinejudge]

@lorenzo Sure, I am using version 3.2.0-RC1

[lorenzo]

@urionlinejudge do you mind trying with the latest 3.2? I remember the default query string in the paginator was removed recently

[thenephie]

I am using 3.2.5 and i just ran into this problem

[lorenzo]

For what query?

[thenephie]

I'm setting the default ordering of a pagination to multiple fields
['Tickets.priority_id' => 'desc','Tickets.modified' => 'desc']

This works for the first page, and generates this part of the query

ORDER BY 
  `Tickets`.`priority_id` desc, 
  `Tickets`.`modified` desc 

The previous and next links that are generated contain directives for the order but only for 1 field
<a rel="next" aria-label="Next" href="/tickets/index/1?page=2&amp;sort=Tickets.priority_id&amp;direction=desc"><span aria-hidden="true">volgende &gt;</span></a>

which is different that the order that was used for that page.

[arilia]

excuse me if I repeat myself: I think it would be great if there just was an option to tell paginatorHelper not to add the order parameters at all. This is in fact the way it used to work in cake2

Of course in future having multiple sort conditions in the query parameters would be great

But for now I just need that the query for page 2 is simply

index/page:2

instead of

index/?page=2&sort=Products.color&direction=desc

I guess that @urionlinejudge is saying the same thing

[lorenzo]

Does anyone volunteer to send a pull request?

[urionlinejudge]

@arilia Yes! That is exactly what I am saying. I believe and expected this to be normal behaviour, even in version 3. After all, when you click next you expect the default sorting order to be maintained, since you did not explicitly said otherwise.

[lorenzo]

Curently in my cake 3.2 app I'm not getting the default sort parameters in page 2. I remember those were recently removed @arilia

[arilia]

@lorenzo sadly I'm stuck with 3.1.11 because I don't maintain the server and can't update to PHP 5.5

could be this feature be backported to 3.1?

[lorenzo]

We cannot introduce new features to past releases, but you can definitely apply the patches that made this feature happen in 3.2.

Have you considered installing your application in a host that supports 5.5? I can help you decide for one

[urionlinejudge]

@lorenzo I have just updated the Cake version to the latest release and indeed this was fixed, so I believe we can close this issue, right?

[lorenzo]

Well, this issue is about multiple sort orders in the url, but you are the creator, so it's up to you :)

[arilia]

@lorenzo I definitively can't move my application but I appreciate your help. I'll ask (again) to update the PHP version and till then I'll wait.

[urionlinejudge]

Actually the main problem was keeping the default multi-order defined in for the first page, as Cake 2 did. So, I would say that part is solved. But yes, we could leave it open because of the multiple sort orders in the url. :)

[urionlinejudge]

@lorenzo @arilia Little problem, actually... the updated fixed this, if and only if the default order set in the paginator settings is regarding to only one field. Multiple fields keep not working, I am sure this worked in the previous version because I have it also running in another VM to test.

[thenephie]

The latest release did not fix this issue (as per @urionlinejudge last comment)

When you set the default order of the paginator to multiple fields there are serious inconsistencies.

The first page is rendered with this order applied (as it is simply passed to the query, which does support multiple order fields)

The "previous", "next", and "number" links in this page have the wrong sort options applied to their url. This is apparently done because passing multiple order fields through the url is not supported.

The fact that is not supported is however not the problem, the "solution" for that limitation that was implemented in CakePHP3 (and not in previous version) is the actual problem.

Instead of just not passing the sort options to those links if the sorting hasn't changed from the default, the code now applies only the first values of the array which, as explained above, gives a wrong result.

If those sort options where not applied to those links in that situation there would be no problem. The url does not need to support passing multiple sort fields.

As i see it there are 2 options:
1) don't support multiple sort fields for pagination completely. That means not applying them to the first page! I think this would be a poor choice as it would remove functionality that is needed by (some) people

2) Don't apply the sort options to those links in that specific situation.

The code responsible for this problem starts in the PaginatorComponent (paginate function)

if (!empty($defaults['order']) && count($defaults['order']) == 1) {
            $sortDefault = key($defaults['order']);
            $directionDefault = current($defaults['order']);
        }

        $paging = [
            'finder' => $finder,
            'page' => $page,
            'current' => $numResults,
            'count' => $count,
            'perPage' => $limit,
            'prevPage' => ($page > 1),
            'nextPage' => ($count > ($page * $limit)),
            'pageCount' => $pageCount,
            'sort' => key($order),
            'direction' => current($order),
            'limit' => $defaults['limit'] != $limit ? $limit : null,
            'sortDefault' => $sortDefault,
            'directionDefault' => $directionDefault
        ];

The function key() and current() are used to set the sorting, completely ignoring the possibility of multiple sort fields.

I have now extended this component along with the PaginatorHelper to change this to:

$orderDefault = $defaults['order'];
        if (!empty($defaults['order']) && count($defaults['order']) == 1) {            
            $sortDefault = key($defaults['order']);
            $directionDefault = current($defaults['order']);
        }

        $paging = [
            'finder' => $finder,
            'page' => $page,
            'current' => $numResults,
            'count' => $count,
            'perPage' => $limit,
            'prevPage' => ($page > 1),
            'nextPage' => ($count > ($page * $limit)),
            'pageCount' => $pageCount,
            'order' => $order,
            'sort' => key($order),
            'direction' => current($order),
            'limit' => $defaults['limit'] != $limit ? $limit : null,
            'orderDefault' => $orderDefault,
            'sortDefault' => $sortDefault,
            'directionDefault' => $directionDefault
        ];

And in the helper i change

$url = [
            'page' => $paging['page'],
            'limit' => $paging['limit'],
            'sort' => $paging['sort'],
            'direction' => $paging['direction'],
        ];

to

 $url = [
            'page' => $paging['page'],
            'limit' => $paging['limit'],
        ];
        if(count($paging['order']) == 1){
            $url += [
                'sort' => key($paging['order']),
                'direction' => current($paging['order']),
            ];
        }

(generateUrl function)

This made it so that if there are multiple sort fields and the sorting hasn't been changed the sort options are not applied to the links that shouldn't have them.

This is however a quick-and-dirty workaround, I'm sure that a more complete fix is possible

[markstory]

@thenephie Have you considered making a pull request out of the changes you think should be done? That might be a faster/better way to get the resolution you're looking for on this issue.

[urionlinejudge]

@thenephie Thanks for sharing the quick fix! Please, consider making a pull request as @markstory suggested. I am sure this will help others as well. :)

[bcarl314]

Might be late to the game here, but I found a work around for this. In your template file, add this:

$this->Paginator->options( [ 'url'=>[ 'sort'=>null, 'direction'=>null ] ] )

This will not generate any sort / direction params on the pagination link, and will retain the original order params in the finder query.

[castlino]

@bcarl314 That worked for me thanks.

[ltGuillaume]

@bcarl314 I had no idea how to use that piece of code, I tried to put it in every template, but nothing worked. But I think I've handled it pretty well via this small change: https://github.com/ltGuillaume/sonerezh/commit/bfb12291dc0b669d6bf300abd81a64bdb6780918

[Code-Working]

I would like to make a recommendation to solve this problem:

I extended the PaginatorComponent and changed its line 338 from
$options['order'] = [$options['sort'] => $direction];
to
$options['order'] = [$options['sort'] => $direction] + $options['order'];

This behaviour works for me as I would expect for some reasons:

1. When an you click a sort link, this field is being sorted first.
2. If the default sort order already contains the order field, it will not overwrite the sort defined by the query.
3. 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.

There might be other cases where this solution is not desired (e.g. if you want to make default sort order before the specified sort order), but for those cases, a simple method in the controller will solve this.

If this solution is accepted, I could make a pull request for this.

[markstory]

Sounds like that might work @Code-Working. A pull request with a test to 3.next would be great. :smile:

[kennedymarcos]

Just to mention for old versions of cakephp, i'm doing a maintenance in a system with cakephp version 2.5.3, and the change of line 377 of the PaginatorComponent
to $options['order'] = [$options['sort'] => $direction] + $options['order'];
as @Code-Working said before, worked fine here.

[markstory]

Closing as a pull request is up for 3.6.

[rajithwijepura]

@bcarl314 your solution saved me lot of reserch time. Thank you

P.S. oh eventhough it solved my issue, my get parameters were removed from second page onwards.

@Code-Working solution did the trick

[Tomicapo]

@bcarl314
This worked better for me:
<?php $this->Paginator->options(['url' => array_merge(['sort' => null, 'direction' => null], $this->request->params['?'])]) ?>
For those who don't know where to put this line: add it in the template. Probably the top is the best place.

I'm using PlumSearch plugin and also having trouble changing to page 2 while maintaining the order for pagination and the search filters.
$this->paginate['order'] = ['Provinces.name ASC', 'Locations.name ASC', 'Workshops.title ASC'];

Using latest version at the time (3.5.12).