mysql - select pagination chunk that includes a given id

Question

...before the pagination chunks have been determined.

I know you can do this in multiple statements, but there must be a better way.

my results are returned ordered by date and I want to return the pagination chunk that contains a given id. So I could, for example, select the date of the given id and then select a chunk of results where date is less than or greater than the date. That would work. But is there some native mysql method of doing this sort of thing in one statement? It just seems reasonable to expect that we could ask for X results in which a given id exists if results are ordered by date.

Answer 1

I believe that this cannot be done by one simple statement. But there is a solution to use stored procedures and define your own function composed of more statements.

Unfortunately, for SELECT ... LIMIT x,y you have to use just integers, x and y cannot be expressions therefore you cannot limit results by paging criterion.

Answer 2

SELECT *
FROM
  (
  SELECT @r := COUNT(*),
         @up := @r,
         @down := @r
  FROM items
  WHERE (timestamp, id) < (SELECT timestamp, id FROM items WHERE id = @id)
  ) i,
  (
  SELECT id, timestamp, st, c
  FROM (
    SELECT @down := CASE WHEN (@down % 50) <> 0 THEN @down := @down - 1 ELSE @down END AS c,
           @stop := CASE WHEN (@down % 50) <> 0 THEN 0 ELSE 1 END AS st,
           id, timestamp
    FROM  items
    WHERE (timestamp, id) <= (SELECT timestamp, id FROM items WHERE id = @id)
    ORDER BY
      timestamp DESC, id DESC
    LIMIT 50
    ) down
  UNION ALL
  SELECT id, timestamp, st, c
  FROM (
    SELECT @up := CASE WHEN (@up % 50) <> 0 THEN @up := @up + 1 ELSE @up END AS c,
           @stop := CASE WHEN (@up % 50) <> 0 THEN 0 ELSE 1 END AS st,
           id, timestamp
    FROM  items
    WHERE (timestamp, id) > (SELECT timestamp, id FROM items WHERE id = @id)
    ORDER BY
      timestamp, id
    LIMIT 50
    ) up
  ) r
WHERE NOT st
ORDER BY timestamp, id