sql - Make a paginated table without offset limit - Stack Overflow

admin管理员组

文章数量:1125748

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

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, cpany,
      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 = upany_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)?

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

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)?

• sql
• postgresql
• pagination

Share Improve this question Follow edited Jan 9 at 7:17 VLAZ 28.8k99 gold badges6262 silver badges8282 bronze badges asked Jan 9 at 4:33 wepro01wepro01 4144 bronze badges 2

• @DaleK cursor lol – wepro01 Commented Jan 9 at 6:47
• 1 29 pages x 5 rows each = 145 rows. I would use frontend pagination instead of backend pagination in this case. Even if you need to load a few thousand rows, it still easier to do it in the UI. No need to hit the database each time the user moves around pages or re-sorts the data for the 20th time. – The Impaler Commented 2 days ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

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:

• Simply don't show the number of pages. Provide a way to browse to the next page, 10 pages or so ahead and (perhaps) the last page.

• Use EXPLAIN (FORMAT JSON) on the query, then you can easily extract the estimated row count from the result. That allows you to say "page 1 of approximately 29" and should be good enough in most cases.

• If you really, truly need an exact row count, don't calculate that immediately. Rather, provide a button "exact row count" and calculate the (slow) result only when the user insists.

This article my be interesting for you.

本文标签： sqlMake a paginated table without offset limitStack Overflow