Item position in a list

SQL++
,
1

Here is the use case in details if you want to learn more :

Back story

On a web page I have a list of items. Those items are paginated through “infinite query”/“lazy loading” : only the first 20 items are shown, when the user scrolls down we load more and so on.
We want to create a permalink to that item (website.com?itemId=) so you can share a link that will load the page to that particular item. Initially we thought about adding the page number in the url, the issue is that item can move from one page to another because items are added or removed. So we need to get the position of the item in the list before we can actually load the page.

I need to get an item position in a list, to then load a page (meaning the list with a LIMIT and an OFFSET) that will contain the item.

I have seen this query from SO :

SELECT ARRAY_POSITION(allPoints, {"userId":"user123"})
LET allPoints = (SELECT userId from stuff WHERE ... ORDER BY points DESC, userId ASC)

Which is a nice trick but I’m concerned about the performance if the initial list contains thousands of elements, also I would love to do that in one query or at least one network trip.
Maybe using a COVERING INDEX will allow me to get the position for cheap and then I perform a regular query ?

2

OLAP functions (EE Only)

SELECT userId , ROW_NUMBER() OVER (ORDER BY points DESC, userId ASC) AS rown
FROM  stuff  AS s
WHERE ... 
ORDER BY rown

SELECT u.*, UNNEST_POSITION(u) AS pos
FROM 1 AS d
UNNEST (SELECT userId from stuff WHERE ... ORDER BY points DESC, userId ASC) AS u

OR

FIRST pos FOR pos:v IN allPoints WHEN v.userId = "user123" END

3

I understand the first one, but it returns all the results right ? And if I add a an OFFSET or a LIMIT I might not find my item.
The second one I guess is just to find a position into a nested document.

For the last one we are back to fetching everything, am I right ?

4

You can always add WHERE clause. OLAP query you need to filter in parent.

Unless you store position, you need to get the documents and calculate on the fly (that may need fetch all do sort).

SELECT u.*, UNNEST_POSITION(u) AS pos
FROM 1 AS d
UNNEST (SELECT userId from stuff WHERE ... ORDER BY points DESC, userId ASC) AS u
WHERE v.userId = "user123";

Your original query or This might better over UNNEST if you need only one entry

WITH allPoints AS (SELECT userId from stuff WHERE ... ORDER BY points DESC, userId ASC) 
SELECT  FIRST pos 
                   FOR pos:v IN  allPonts 
                  WHEN v.userId = "user123" END AS pos;
5

My “original” query was merely an example from StackOverflow.
OK so whatever the solution I will have to get all the documents. With your latest query, if the sub-query is covered by index, can we expect the whole thing to be cheap or this kind of operation will always be expensive ?

6

If it is via bucket and you should see if keyset pagination fits for your need. (If changing data LIMIT, OFFSET can miss or duplicate items between calls)

If where clause is simple on equality the moment you have range predicate things makes complex.

7

This is an amazing blog post, thank you for sharing !