Pagination and bind value in where condition is not working CakePHP 4.3

Need Help
1

Hello,
it doesn’t seem possible to bind values in a WHERE condition and then apply pagination. The quasi exact problem was described here link years ago. Is there no solution or workaround for this? If the condition is hard coded there is no problem. Also the various BIND methods from the docs “bind()” and “bindValue()” don’t work. What can I do?

2

As you see in cakephp/QueryTest.php at 4.x · cakephp/cakephp · GitHub and e.g. the 4.3.9 github actions CI run here Update version number to 4.3.9 · cakephp/cakephp@6548d11 · GitHub you see these functions work without a problem.

So then now comes the real question: How do you use the ->bind() method inside your query? Maybe there is a special edge case we haven’t thought of yet and need to fix.

Can you please share more info on what exact Query builder functions you call so we can try to reproduce your error?

3

Couldn’t you just write a custom paginator, or extend cakes paginator.

For example, laravel has a “LengthAwarePaginator” where you manually calculate and apply

 "LIMIT {$offset}, {$perpage}"

as needed. Also if you have never written a paginator, I suggest learning how, it is very easy to write a custom paginator.

Doesn’t Cakephp also have the skip and take for custom cases.

4

Hello,
First of all, thanks for the answers, here is the code snippet of the query, as I said, it works, the first page is displayed, only the others are not…

class DataordersController extends AppController {

		/**
		* Index method
		*
		* @return \Cake\Http\Response|null|void Renders view
		*/

		public function index() {

				$dataorders = $this->Dataorders->find()
				->where(['currency LIKE'=>'%'.$currency.'%']) //or what ever
				->where(['Or'=>['ordersend BETWEEN :start AND :end']])
				->bind(':start',$start_date,'date')
				->bind(':end',$end_date,'date');

				$dataorders = $this->paginate($dataorders,['limit'=>'30']);
			}
			...			
		}

Unfortunately, the update is not working right now and this message comes up:

Installing dependencies from lock file (including require-dev)
Package operations: 0 installs, 2 updates, 0 removals
    Update of laminas/laminas-diactoros failed
    Update of cakephp/cakephp failed
    0 [--->------------------------]
In DownloadManager.php line 150:
                                                                                      
  Package laminas/laminas-diactoros-2.8.0.0 does not have an installation source set

maybe you have a solution for that too

Thank you very much!

5

I would personally go for something like that

$last_month = Carbon::now()->subMonth();
$today = Carbon::now();
$dataorders = $this->Dataorders->find();
$dataorders->where([
    'currency LIKE'=>'%'.$currency.'%'
    'OR' => [
        $dataorders->newExpr()->between('Dataorders.ordersend', $last_month, $today)
        // Add other Query Expressions here which should be OR'ed with the between
    ]
]);
$dataorders = $this->paginate($dataorders,['limit'=>'30']);

Checkout the Doc on Advanced Query Conditions HERE

And I don’t know what composer version you are using or what composer command you executed but make sure you are on the latest Composer 2 version (currently 2.3.5) and execute composer update -W
It should look something like this:

-> % composer update -W                     
Loading composer repositories with package information
Updating dependencies
Nothing to modify in lock file
Installing dependencies from lock file (including require-dev)
Nothing to install, update or remove
Package webmozart/path-util is abandoned, you should avoid using it. Use symfony/filesystem instead.
Generating autoload files
composer/package-versions-deprecated: Generating version class...
composer/package-versions-deprecated: ...done generating version class
89 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
6

The basic issue here is getting your bound values to persist through multiple Requests.

In cases where the values are encoded into the url (eg domain.com/controller/action?page=3&limit=10&currency=USD) you can find those values in the Request object for use in your bind statement.

If they are not in the url’s query arguments and you don’t want to add them to it, you’re going to have to persist them somewhere else. The Session would be one common choice. But you also have your DB or cache’s that might serve your purposes.

In a nutshell, this is not something that the framework does for you. You will need to implement it yourself.

However, The Pagination Component/Helper do an excellent job of managing all of the pagingation related values across multiple requests. This system uses the url query arguments. That’s why I often piggyback on the url with my own query args when I need something like this.