sqlpostgresqlpagination

Make a paginated table without offset limit

I use cursor pagination and want to display my data like this:

enter image description here

at the bottom you can go to page 1 until 29. To calculate my pages I have to count all my rows. Is that not heavy everytime to count all rows? Is there maybe another solution or I can't get away from it to count all rows?

I also don't use offset so how can I do it with cursor pagination?

  SELECT 
      wp.id,
      wp.updated_wst,
      u.id as user_id, u.firstname, u.lastname, 
      c.id as company_id, c.company,
      wst.id as wst_id,
      wst.start_time,
      wst.end_time,
      wst.send_start_at,
      wst.send_end_at,
      wst.accepted_by,
      wst.accepted_at,
      l.name as location
  FROM workers_plan wp
  LEFT JOIN users u
      ON u.id = wp.user_id  
  LEFT JOIN clients c
      ON c.id = u.company_id
  LEFT JOIN workers_send_times wst
      ON wst.workers_plan_id = wp.id
  LEFT JOIN location_orders lo
      ON lo.id = wp.location_orders_id
  LEFT JOIN location l
      ON l.id = lo.location_id  
  WHERE lo.id = $1 AND wp.tenant_id = $2 AND (wp.id, wp.updated_wst) > ($3, $4)          
  GROUP BY wp.id, wst.id, u.id, c.id, l.name
  ORDER BY wp.id, wp.updated_wst LIMIT 50

Thats my current code. But to display pages I have to count rows and then calculate it. How would I do it (the best performant way)?

Solution

  • If you need to know how many pages there will be, you need to know the total result set count, and calculating that is expensive. Moreover, the data may change, and then the 29 pages may actually become 28 or 30, so the page count is misleading.

    I want to encourage you to not calculate the number of result rows. Web search engines don't do it, and they know why. Here are some suggestions what to do instead:

    This article my be interesting for you.