Fix created_at edge case for pagination cursor in queries

coder · mafredri · May 10, 2022 · May 5, 2022 · May 5, 2022 · May 5, 2022
commit 067f912a8fab01f08f48a9a99218b2e58470c9c1
diff --git a/coderd/database/databasefake/databasefake.go b/coderd/database/databasefake/databasefake.go
@@ -186,10 +186,10 @@ func (q *fakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams
 		return a.CreatedAt.Before(b.CreatedAt)
 	})
 
-	if params.AfterUser != uuid.Nil {
+	if params.AfterID != uuid.Nil {
 		found := false
 		for i, v := range users {
-			if v.ID == params.AfterUser {
+			if v.ID == params.AfterID {
 				// We want to return all users after index i.
 				users = users[i+1:]
 				found = true

diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/templateversions.sql b/coderd/database/queries/templateversions.sql
@@ -12,20 +12,17 @@ WHERE
 		WHEN @after_id :: uuid != '00000000-00000000-00000000-00000000' THEN (
 			-- The pagination cursor is the last ID of the previous page.
 			-- The query is ordered by the created_at field, so select all
-			-- rows after the cursor. We also want to include any rows
-			-- that share the created_at (super rare).
-				created_at >= (
-					SELECT
-						created_at
-					FROM
-						template_versions
-					WHERE
-						id = @after_id
-				)
-				-- Omit the cursor from the final.
-				AND id != @after_id
+			-- rows after the cursor.
+			(created_at, id) > (
+				SELECT
+					created_at, id
+				FROM
+					template_versions
+				WHERE
+					id = @after_id
 			)
-			ELSE true
+		)
+		ELSE true
 	END
 ORDER BY
     -- Deterministic and consistent ordering of all rows, even if they share

diff --git a/coderd/database/queries/users.sql b/coderd/database/queries/users.sql
@@ -77,23 +77,20 @@ WHERE
 		-- This allows using the last element on a page as effectively a cursor.
 		-- This is an important option for scripts that need to paginate without
 		-- duplicating or missing data.
-		WHEN @after_user :: uuid != '00000000-00000000-00000000-00000000' THEN (
-		    	-- The pagination cursor is the last user of the previous page.
-		    	-- The query is ordered by the created_at field, so select all
-		    	-- users after the cursor. We also want to include any users
-		    	-- that share the created_at (super rare).
-				created_at >= (
-					SELECT
-						created_at
-					FROM
-						users
-					WHERE
-						id = @after_user
-				)
-				-- Omit the cursor from the final.
-				AND id != @after_user
+		WHEN @after_id :: uuid != '00000000-00000000-00000000-00000000' THEN (
+			-- The pagination cursor is the last ID of the previous page.
+			-- The query is ordered by the created_at field, so select all
+			-- rows after the cursor.
+			(created_at, id) > (
+				SELECT
+					created_at, id
+				FROM
+					template_versions
+				WHERE
+					id = @after_id
 			)
-			ELSE true
+		)
+		ELSE true
 	END
 	-- Start filters
 	-- Filter by name, email or username

diff --git a/coderd/users.go b/coderd/users.go
@@ -115,7 +115,7 @@ func (api *api) users(rw http.ResponseWriter, r *http.Request) {
 	}
 
 	users, err := api.Database.GetUsers(r.Context(), database.GetUsersParams{
-		AfterUser: paginationParams.AfterID,
+		AfterID:   paginationParams.AfterID,
 		OffsetOpt: int32(paginationParams.Offset),
 		LimitOpt:  int32(paginationParams.Limit),
 		Search:    searchName,