DEV Community

Discussion on: 1 SQL Query You Should Stop Using

Collapse
 
gabbersepp profile image
Josef Biehler • Edited

I already know about the internals of pagination. But how can we avoid this if we have a Pool of items with one to ten filters where the user can decide which filter he combines and how he sorts . Is there a solution available that gives me always ten items per page (except the last page of course)?

Collapse
 
abdisalan_js profile image
Abdisalan • Edited

As long as your results are ordered, you can use this method with any number of filters. You'll have to get creative on how to add the filters, because you don't want to create 2^10 SQL statements for every combination of filters.

The solution would be to pick 1 or 2 fields you can order your results by and then apply your filters as well. You can query the next page based on the field you are ordering your data by.

Example query for employees where the filter is the department and whether they are on vacation and order by birthdays

#page 1
SELECT * FROM employees
WHERE
  department != 'finance' AND
  on_vacation = false
ORDER BY birthday DESC
LIMIT 10

# page 2
SELECT * FROM employees
WHERE
  department != 'finance' AND
  on_vacation = false AND
  birthday <= $last_birthday_on_page_1
ORDER BY birthday DESC
LIMIT 10
Enter fullscreen mode Exit fullscreen mode

Hope that helps