Passing query options as paginator settings is no longer supported

[Zenzs]

Cakephp Version: 5.0.10

Hi,

Please be advised that I have asked this question on SO (cakephp - Paginating with sort and direction - Stack Overflow) but unfortunately I was unable to find a solution. And without a solution I’m not going to be able to upgrade to 5 hence my post here.

History - My Use Case

For the SOC (Seperation Of Concern) principal I separate the initial page load from the paginated page load in all my index methods with the following code:

Tickets Controller - Index Method

if (empty($this->request->getQuery('page'))
    && is_null($this->request->getQuery('sort'))
    && is_null($this->request->getQuery('direction'))) {

    // Initial Load
    $this->setSort('Tickets.date_created');
    $this->setDirection('desc');
    $this->setLimit($user->results_per_page->respp_ticket);

} elseif (($this->request->getQuery('sort') !== '')
    && is_string($this->request->getQuery('sort'))
    && !is_numeric($this->request->getQuery('sort'))
    && ($this->request->getQuery('direction') !== '')
    && is_string($this->request->getQuery('direction'))
    && !is_numeric($this->request->getQuery('direction'))) {

    // Paginated Load
    $this->setPage($this->request->getQuery('page'));
    $this->setSort($this->request->getQuery('sort'));
    $this->setDirection($this->request->getQuery('direction'));
} 

// Component here which returns a query object named $query

// This was how I used to code in the 4 branch - And this is what I need to refactor

$this->set('page', $this->getPage());
$this->set('sort', $this->getSort());
$this->set('direction', $this->getDirection());
$this->set('limit', $this->getLimit());

$settings = $this->paginate = [
    'sortableFields' => [
        'Tickets.id',
        'Tickets.date_created',
        'Tickets.priority',
        'Tickets.subject'
    ],
    'page' => $this->getPage(),
    'sort' => $this->getSort(),
    'direction' => $this->getDirection(),
    'limit' => $this->getLimit()
];

$tickets = $this->paginate($query, $settings);
$this->set(compact('tickets'));

---

With the changes in 5 I now code like this:

protected array $paginate = [
    'sortableFields' => [
        'Tickets.id',
        'Tickets.date_created',
        'Tickets.priority',
        'Tickets.subject'
    ],
    'order' => [
        'Tickets.date_created' => 'desc',
    ],
];

$this->set('page', $this->getPage());
$this->set('sort', $this->getSort());
$this->set('direction', $this->getDirection());
$this->set('limit', $this->getLimit());

$this->paginate = [
    'limit' => $this->getLimit()
];

$tickets = $this->paginate($query);
$this->set(compact('tickets'));

My Issue

The column sorts do sort the columns correctly but the page numbers no longer have the sort and direction in the url.

As far as I can tell the only other way to liaise with the paginator in this context is with pagination options in the index view like this:

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

But if I do this I obviously overwrite the url generated by the helper which means:

1. The column sorts always sort the same column - The Tickets.date_created column.
2. The page numbers do have the sort and direction in the url.

What I’ve Tried

$this->paginate = [
    'sort' => $this->getSort(),
    'direction' => $this->getDirection(),
    'limit' => $this->getLimit()
];

Result

1. The column sorts sort correctly on all columns.
2. The page numbers have the sort and direction in the url.

But this warning is displayed:

Warning (512) : Passing query options as paginator settings is no longer supported. Use a custom finder through the finder config or pass a SelectQuery instance to paginate(). Extra keys found are: sort, direction.

I understand query options is referring to the sort and direction. And for me using a finder in this case does not make sense.

Question

If passing query options as paginator settings is no longer supported what is the new v5 way.

Thanks Zenzs.

[KevinPfeifer]

First of all: Why are you manually setting the page, sort and direction? This automatically happens via the paginator, so you don’t have to do any of this if-else thumbling you are doing.

---

Second: You can get the pagination params in your view as well, you don’t need to pass them down. No idea why you need those variables in the view, but inside any template you can do:

$request = $this->getRequest();
$page = $request->getQuery('page');
$column = $request->getQuery('sort');
$dir = $request->getQuery('direction');

---

Third: The paginator will always overwrite the sort, page and direction given in the query you manually set in your controller. So even if you manually do

$query = $query->orderBy(['Tickets.date_created'  => 'DESC']);
$tickets = $this->paginate($query);

and THEN click on the link the Paginator creates for you via

$this->Paginator->sort('date_created')

you will still get a correctly ordered query. Click multiple times on the same link to switch between ascending and descending.

---

So i would recommend you remove all that manually setting the page, sort limit and direction nonsense you have and look at the default behavior cake gives you (basically what bin/cake bake all --everything does for you if your database schema is structured according to our conventions)

If you want to have a default ordering present just add

$query = $query->orderBy(['Tickets.date_created'  => 'DESC']);

(or whatever) before you paginate the query in your controller action.

[Zenzs]

Hi Kevin,

I set the page, sort and direction manually so I can send them to the view, then pass them to the elements then post them to my other methods. I then add them to the redirects in those methods which redirect back to the paginated section in index and the same result set is displayed.

For example: A user is on page 5 and clicks an asc sort on column abc which means its a different result set than the initial load. Then clicks a button at the end of the row and a form in an element posts those parameters to the relevant method. Then as mentioned adds them to the redirect so when index is reloaded it loads page 5 with an asc sort on column abc.

If I may check, you advice means that those parameters will persist if I don’t add them to the redirects and page 5 with an asc sort on column abc will be displayed in my use case.

It’s just that on the 3 and 4 branch it never persisted those parameters and reloaded the initial load result set which is why I ended up passing them around to ensure the last result set was displayed and not the initial one.

Thanks for your help.

[dereuromark]

You are overcomplicating it IMO
If you want to preserve those across requests, pass the query strings along with your links.

<?php echo $this->Html->link('Some link in your paginated view', 
    ['?' => $this->request->getQuery()]); ?>

If you have custom query strings:

<?php echo $this->Html->link('Some link in your paginated view', 
    ['?' => ['importantParam' => true] + $this->request->getQuery()]); ?>

Once you are done on that other page and want to redirect back, pass those to redirect().
Same goes for any links that are on that 2nd page.
As long as you add the getQuery() params for pagination into the link, you will end up with the same ordering etc when coming back from those.

Nothing else needed! Simple and effective.

If you want to make it more unobtrusive, you could also just filter out sort/pagination etc from the query strings and only pass those along. Maybe with a helper method of sorts.

[Zenzs]

Hi Mark,

Thanks for the advice. The only problem is that its only view that is a link. All the others are posted.

If you could visulise 2 glyphs on the index page at the end of the row.

1. Glyph 1 - A view link.
2. Glyph 2 - A post button to method 1.

I store the page, sort and direction in hidden fields in an element so when a user clicks glyph 2 these parameters are posted to method 1 then added to the redirect back to index in that method.

After the redirect I initialise them in the pagination section of index and apply them to the paginator like:

$settings = $this->paginate = [
    'sortableFields' => [
    'Tickets.id',
    'Tickets.date_created',
    'Tickets.priority',
    'Tickets.subject'
],
'page' => $this->getPage(), <------------- Here
'sort' => $this->getSort(), <------------- Here
'direction' => $this->getDirection(), <--- Here
'limit' => $this->getLimit()

];

Then the same result set is displayed. Well thats what I did in 4 and thats what I’m trying to replicate in 5.

Thanks again.

[dereuromark]

a) You can post also to URLs including those query strings, then it would not need to be in the POST body.

b) Nothing changed on the pagination really from 4 to 5. Just the way the defaults are working.

What you did was probably not a good design in the first place.
You should redirect then (PRG pattern) if you have a URL that contains them as POST data instead of query strings.
Otherwise your URLs are not consistent and clear.
So make a redirect then to the same action with them being back into the URL query strings and all should work out of the box again.

[Zenzs]

I think my question should of been different. I have the index records in a form which enables a user to select multiple records via checkboxes, choose an option from a select list at the top of the page and click the submit button. The checkboxes are posted to the method, then say deactivated and then redirected back to index.

So in the method I have no data apart from the select list option, the checkbox array and any values I’ve passed via hidden fields. This is why I pass the sort and direction via the hidden fields, pin them to the redirect and the same result set is displayed in index after the redirect.

And this as far as I know is a standard way of selecting multiple records, take action on them then redirect. (PRG). I’m not sure how else you’d do this? Is there another way of doing this?

The individual buttons on each row use the same form so I don’t have to use get and can avoid web crawlers.

This still leaves me with the same problem, persisting the sort and direction to enable displaying the same result set after redirecting.

I’m not sure where to go from here but if you could tell me if your above advice is still relevant to this new context at least I’ll have a starting point and know what I’m trying to do is doable.

Thanks, Zenzs.

[dereuromark]

None of that contradicts what I just gave you as concept.
Make sure to redirect back to the same URL (with query strings) and all is fine.
How to keep them I gave you two options.

[Zenzs]

Thanks for confirming its doable which means I am going to be able to upgrade to 5 with minimum refactoring required.

I’m posting a form to a method.

What I need to understand

(a) You can post also to URLs including those query strings

How to keep them I gave you two options.

If you want to preserve those across requests, pass the query strings along with your links.

I assume this is one of the options.

<?php echo $this->Html->link('Some link in your paginated view', 
    ['?' => $this->request->getQuery()]); ?>

But there are no links?

If I’m honest I’m not sure if I’m going to be able to solve it with this information at the moment so I’ll have to put it on the back burner and revisit it in the future when I’ve got more time.

Thanks for your help though.

[Zuluru]

What do you mean “there are no links”? That code should definitely generate some output, and that output should be a link. Is the link wrong? If it’s not generating any output, it seems most likely to me that the code isn’t actually being reached.

[Zenzs]

Hi Zuluru,

The foreach loop on the index page is in a form with a select list. I click a button and post a form to a method. The data available in the method is the select list value, the checkbox array and any data from hidden fields which I initiate in the method with getData(). I then populate the redirect back to index and I need the same result set displayed on index as there was before I clicked the button.

My form does not have any links.

What I was going to do was bake the tutorial and find out how exactly the sort and direction are being passed, as long as there in the url it should work.

I can always make it a link instead of a button or change the form to GET which would persist the url data so I should be able to get it working.

Thanks, Zenzs.

[Zuluru]

So, you’re building a form? A single form with lots of rows of data in it, and a Submit button at the bottom? Are you using $this->Form->create(...), $this->Form->control(...) and $this->Form->end(...) to do this?

But you also said earlier that there should be two links on each row of the table, which is quite different. Maybe show us a sketch of what you expect it to look like, and the template code you have so far to make it happen?

[Zenzs]

Yes I’m buliding a single form with lots of rows of data in it and a submit button at the bottom. And I’m using $this->Form->create(...), $this->Form->control(...) and $this->Form->end(...) to do it.

I admit the question has deviated quite a bit from its original which I why I thought I’d bake the tutorial, analyise the behaviour of the sort and direction as I pass them around with GET, POST and PUT. Then reformat a new question using the tutorial code if I can 't get it working.

I do appreciate your help so I apologise if it’s become a bit confusing.

Index View

At the top is $this->Form->create(...) then below that a select list using $this->Form->control(...) then the foreach loop then at the end $this->Form->end(...).

I check the checkboxes and click the submit button next to the select list. In the method which I posted to I have the select list value and the checbox array and I initialise them both with getData().

On each row of the table I have a view link to view the record and a button next to it which holds the records id. I click the button and the form is posted to the same method. I initialise the records id with getData().

And now I can select multiple records or a single record, POST them to a method, change a value then redirect back to index. On 4.5 it never displayed the same result set unless I passed the sort and direction around and set the pagination in index with:

$this->paginate = [
    'sort' => $this->getSort(),
    'direction' => $this->getDirection(),
    'limit' => $this->getLimit()
];

Then the same result set was displayed. This has been removed in 5 and in my scenerio I need to work out a new way to display the same result set.

Imagine edit on the tutorial, what I’m going to try is on submit changes PUTTING to a new method then redirecting back to index. If I was on page 4, sort column A and direction ASC before edit I need that result set displayed after the redirect.

Hopefully this has made it less confusing.

[Zuluru]

Have you tried, when you create the form, to put the current URL into a “redirect” parameter for the method you post to? That would have all your pagination details in it, and then your “post” method, after it’s done doing whatever it’s doing, can grab that parameter and redirect to that page. Or if you don’t like it in the URL for some reason, put it as a hidden input in your form.

[Zenzs]

That sound intriguing, bypass all the passing around completely and use a redirect parameter to persist the url parameters.

Unfortunately I had to put the upgrade to 5 on hold because of this I’m now in the middle of some other functionality which I need to finish first.

Thanks for the suggestion, I look forward to trying it out but I’d like to try it on a baked tutorial app when I have the time needed to look at it properly.