I have a cursor pagination implemented with MySQL (version 8.0), which works fine as long as there are no null
values involved.
Here's my example data (id
is a random UUID, date
is date, time
is time):
id | date | time
--------------------------
68 | 2017-10-28 | 22:00:00
d3 | 2017-11-03 | null
dd | 2017-11-03 | 21:45:00
62 | 2017-11-04 | 14:00:00
a1 | 2017-11-04 | 19:40:00
The cursor
I use always consists of all three columns.
I use this query to get the next results (after the cursor
):
SELECT * FROM table
WHERE (date > cursor.date)
OR (date = cursor.date AND time > cursor.time)
OR (date = cursor.date AND time = cursor.time AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC
And this query for prev results (before the cursor
):
SELECT * FROM table
WHERE (date < cursor.date)
OR (date = cursor.date AND time < cursor.time)
OR (date = cursor.date AND time = cursor.time AND id < cursor.id)
ORDER BY date DESC, time DESC, id DESC
When using the prev query with cursor [id = dd, date = 2017-11-03, time = 21:45:00]
it won't return the row with id = d3
, because time
is null
, and this won't get selected by time < cursor.time
.
Though I tried using time < cursor.time OR time IS NULL
instead of time < cursor.time
to include rows with null
values. Which seems to fix this particular problem, but then creates a new problem: When using the prev query with cursor [id = d3, date = 2017-11-03, time = null]
, because now the result contains the row of the provided cursor.
I hope there's an easy solution for this. There seems to be no examples or tutorials on the web that deal with null
values in cursor pagination.
Note: For the solution it doesn't matter if null
will be sorted before or after non-null
values, as long as it's consistent. (MySQL's default ordering is null < non-null
)
I am not going to touch the topic of using cursors for pagination. There are alternatives, such as limit
/offset
.
But my recommendation for your queries is to use coalesce()
, assigning a fake time for the comparison. MySQL makes this somewhat simple, because it supports time
values in excess of 24 hours. And those would not be valid values for a date
/time
combination.
So:
SELECT *
FROM table
WHERE (date > cursor.date) OR
(date = cursor.date AND COALESCE(time, '24:00:00') > COALESCE(cursor.time, '24:00:00')) OR
(date = cursor.date AND COALESCE(time, '24:00:00') = COALESCE(cursor.time, '24:00:00') AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC
A more concise WHERE
clause would be:
WHERE (date, COALESCE(time, '24:00:00'), id) > (cursor.date, COALESCE(cursor.time, '24:00:00'), cursor.id)
Add another column to the table. Make it a DATETIME
. Combine date
and time
into it when not NULL; combine date
with some particular time when NULL. Then your cursor has two columns to work with and no nulls.
If you have a reasonably recent version of MySQL, you can use a "generated stored" column, thereby avoiding any code changes.
And be sure to have INDEX(datetime, id)
.
If you are using MySQL 8.0 then you can consider to use row_number() window funciton create an unique sequential id (rn) for each row. Then just pass the rn
for current row to get the previous rows.
Schema and insert statements:
create table cursortable( id varchar(10), date date, time time);
insert into cursortable values('68' , '2017-10-28' , '22:00:00');
insert into cursortable values('d3' , '2017-11-03' , null);
insert into cursortable values('dd' , '2017-11-03' , '21:45:00');
insert into cursortable values('62' , '2017-11-04' , '14:00:00');
insert into cursortable values('a1' , '2017-11-04' , '19:40:00');
Query to get the result for fist time:
select *,row_number()over(order by date,time,id)rn from cursortable
Output:
id | date | time | rn |
---|---|---|---|
68 | 2017-10-28 | 22:00:00 | 1 |
d3 | 2017-11-03 | null | 2 |
dd | 2017-11-03 | 21:45:00 | 3 |
62 | 2017-11-04 | 14:00:00 | 4 |
a1 | 2017-11-04 | 19:40:00 | 5 |
Query to get the previous rows for cursor [id = dd, date = 2017-11-03, time = 21:45:00, rn=3]
with only cursor [rn=3]
:
with cte as
(
select *,row_number()over(order by date,time,id)rn from cursortable
)
select * from cte where rn<3
Output:
id | date | time | rn |
---|---|---|---|
68 | 2017-10-28 | 22:00:00 | 1 |
d3 | 2017-11-03 | null | 2 |
db<>fiddle here
If you don't want to introduce an computed column into your code then please try the below solution considering all three columns cursor [id = dd, date = 2017-11-03, time = 21:45:00]
Query:
with cte as
(
select *,row_number()over(order by date,time,id)rn from cursortable
)
,cte2 as
(
select * from cte where id='dd' and date= '2017-11-03' and time= '21:45:00'
)
select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn
Output:
id | date | time |
---|---|---|
68 | 2017-10-28 | 22:00:00 |
d3 | 2017-11-03 | null |
db<>fiddle here
Your code would be like below:
with cte as
(
select *,row_number()over(order by date,time,id)rn from cursortable
)
,cte2 as
(
select * from cte where id=cursor.id and date= cursor.date and time= cursor.time
)
select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn