Paginate a collection, or filter query results without turning into a collection?

Need Help
1

Hi,

I have an API endpoint that offers a lot of user customisable filters. One of the fields I am filtering is a virtual field calculated based on a few different fields and related fields. Rather than build this into the query I thought I’d just use CollectionInterface::filter() to match the user query. Here’s the code:

$invoices = $this->Invoices->find('filteredResults', filters: $query);
if (isset($query['status']) || isset($query['exclude'])) {
	$statusSearch = $query['status'] ?? null;
	$exclude = $query['exclude'] ?? null;
	$invoices->formatResults(function (\Cake\Collection\CollectionInterface $invoices) use ($statusSearch, $exclude) {
		return $invoices->filter(function ($invoice) use ($statusSearch, $exclude) {
			$include = $exclude ?
				!in_array($invoice->status, explode(",", $exclude)) :
				true;
			$inSearch = $statusSearch ?
				str_contains(strtolower($invoice->status_string), strtolower($statusSearch)) :
				true;
			return $include && $inSearch;
		})
			->toArray(false);
	});
}

$invoices = $this->paginate($invoices, $paginatorConfig);
$paging = $invoices->pagingParams();

So the initial issue I ran into is that filter returns a Collection, and paginate() doesn’t like that. So I wrapped it in a formatResults(), but the collection maintained the indexing of the original query result and returned a json index instead of an array (and my frontend is expecting an array).

This formatResults() with a filter() inside gets me the results I want - however the paginator remembers the original indexing of the prefiltered results… so I end up with like 3 results on page 1, 4 results on page 2, etc. instead of properly paged results.

Is there any way I can properly paginate these results? Should I just give up on using the filter and build the filter into the query?

Thanks

2

The paginator runs on queries, so it limits the results from the database. Anything looking at virtual fields necessarily must run on data already returned from the database. I don’t see how you can reconcile these two conflicting realities. Either you build your filtering into the query, or you get more (all?) results back from the database, filter that result, and then slice it to get just the page that you want. The latter is significant overhead in returning all results on every query, could get quite massive depending on the size of your database.

1 Like
3

Makes sense, I implemented it as a query. Wasn’t even difficult! Serves me right for trying to cut corners.

Thanks. :slight_smile: