Pagination and sortBy REST API

REST
1

Hi,

I’ve noticed that using pagination with the sortBy parameter causes duplicate records to be sent as the offset parameter is modified. The below format seems to work great:

data/assets?where=keywords%20LIKE%20%27%25{terms}%25%27%20AND%20type%20%3D%20%27{type}%27&pageSize=15&offset={offset}

However, if you add the sortBy param like below, records with the same objectId are sometimes included in the response as the offset number changes:

data/assets?where=keywords%20LIKE%20%27%25{terms}%25%27%20AND%20type%20%3D%20%27{type}%27&sortBy=name%20asc&pageSize=15&offset=0

data/assets?where=keywords%20LIKE%20%27%25{terms}%25%27%20AND%20type%20%3D%20%27{type}%27&sortBy=name%20asc&pageSize=15&offset=15

It doesn’t seem like this should be the expected behavior. Please advise. Thank you!!

2

Hello @Sean_Butler

We are investigating it now. Thank you for contacting us.

Regards, Dima.

3

Our QA verifies a bug here, so I created an internal ticket to fix the issue. Thank you for helping us to get better!
We will notify you when it will be fixed.

Regards, Dima.

5

Hi @Sean_Butler

The reason for this behavior is that the database does not guarantee a specific output order for rows with identical values in the sorted columns. Therefore, the same record(s) may appear in different order in these queries.

There are several ways to address this and achieve predictable behavior:

  1. In your table, go to Schema → Constraints → Select constraints, and set ‘Indexed’ for the column you are sorting by. By the way, we always recommend setting an index for sorting.
  2. When sending a query with sorting, additionally specify sorting by the ‘objectId’ along with the specified column.

Regards,
Viktor