Pagination for queries with filters

[sebj54]

Feature Request

A new (or updated?) metadata to be able to get count of items matching the filter (when using a limit).

What problem does this feature solve?

Actually, the only metadatas we can get are:

• result_count - Number of items returned in this response ➡ When using a limit, it returns the limit if there are more results (not really useful)
• total_count - Total number of items in this collection ➡ When using a filter, it really returns the total count, without using the limit (not really useful either).

How do you think this should be implemented?

Maybe a new metadata (filter_count?) or use the same filters in total_count.
Why not also use MySQL's FOUND_ROWS() function? https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows
This can be really useful for complex queries (we only have to build and call query once).

Would you be willing to work on this?

Why not, but I'm actually very busy 😢

[wellingguzman]

We've been discussing this feature. We also opened a ticket for a related feature to pagination here: #340

I am leaning towards just use the total_count metadata, instead of a new one, because I am not seeing any reason why we would like to see both filter_count and total_count at the same time. Is there any useful reason?

[benhaynes]

That makes sense – I like it! We could use filter_count or filtered_count for this.

[benhaynes]

It might be nice to know how much your filter reduced the total dataset. So your filter is returning:

850/1000 items (filtered_count / total_count)

Normally I would be worried about performance, but all of these metadata fields should be off by default and can be individually included... so I think it's a useful option to have!

[sebj54]

@benhaynes That makes sense to me! And like you said, we can choose the meta that suits our need as they are optional.

I'm already trying to make it work because I really need it. I'll make a PR when it will be ready so we'll be able to discuss the details of the implementation at this time. I choose to implement it under the filtered_count meta so we'll be able to replace total_count if it doesn't seem necessary anymore. :)

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[benhaynes]

To achieve better clarity/visibility, we are now tracking feature requests within the Feature Request project board.

This issue being closed does not mean it's not being considered.

[benhaynes]

I've added this one to in progress @philleepflorence ... cool?

[romaiiinnn]

Hi all,
I started using the pagination with the filter_count meta params. I'm getting a performance issue that I would like to discuss.

Example

A collection of total 1068 items, my query has a total of 834 items filtered.

Requesting items with a limit of 50 is pretty fast:
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3 (~160ms)

Requesting the same result with all the metadata slows down the response a lot (~1500ms)
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3**&meta=***

Requesting the same result, with only metadata filter_count doesn't help (~1500ms)
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3**&meta=filter_count**

Requesting the same result, with only metadata for pagination (which is not documented btw) is also slow (~1700ms)
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3**&meta=page**

Requesting the same result, with only meta total_count (which get the whole collections count) is surprisingly pretty fast (~150ms)
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3**&meta=total_count**

So would there be a way to improve the performance of the query when requesting the metadata "filter_count" ?

[philleepflorence]

Hello @romaiiinnn ...

Requesting items with a limit of 50 is pretty fast:
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3 (~160ms)

Does this query return the pagination object?

Requesting the same result with all the metadata slows down the response a lot (~1500ms)
/_/items/content?filter[type][eq]=7&fields=id&limit=50&page=3**&meta=***

What is the purpose of page=3**?

If you could include the meta object for each query so I can debug further.

Thanks!