We are trying to create a query for the following table :
CREATE TABLE `Action` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `vendor_Id` int(10) unsigned DEFAULT NULL, `name` varchar(60) NOT NULL, `assigned_To` varchar(40) DEFAULT NULL, `updated_At` datetime(3) NOT NULL, `completed_At` datetime(3) DEFAULT NULL, `deleted_At` datetime(3) DEFAULT NULL, `created_At` datetime(3) NOT NULL, `deadline` datetime(3) NOT NULL, `notes` varchar(400) DEFAULT NULL, `completed` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), KEY `action_vendor_id_foreign` (`vendor_Id`), CONSTRAINT `action_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`) ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1
We want to show the Actions using pagination based on deadline field in Ascending order. But deadline can have same values and is creating problem for us to implement cursor based pagination on deadline field.
Action can be created for any future date. We thought of using
deadline but it fails as we can create Action on 22 Nov with deadline 25 Nov and another action on 23 Nov with deadline 24 Nov. I mean we there is no relationship between two of them. and exactly similar thing happens with the
How can I tackle this problem of showing Actions using pagination with deadline in ASC order.
ORDER BY deadline ASC, id ASC will be “deterministic” (assuming
id is the
PRIMARY KEY for the table). And include
More discussion of pagination and the evils of
If there is any chance of adding or deleting rows while the user is paging through the list, items can be skipped or duplicated in the paginated list. “Remembering where you left off” (see link) solves the problem. However, it is a little tricky to say
> when two columns are involved.