Paginator Cursor Missing Items

Questions / Help
,
1

I have a query containing a relatively complicated join and there are 85 items total.

Strangely, after sending the second after cursor no more items return(Paginator never returns more than 60 items).

This is the query:

  defp list_media_items_by_channel_slug_query(channel_slug, :feed) do
    from(m in MediaItem,
      join: con in ContentMediaItems,
      on: m.id == con.media_item_id,
      join: chan in assoc(con, :channel),
      where: chan.slug == ^channel_slug,
      preload: [:mediaitemartifacts],
      order_by: [{:desc, :inserted_at}]
    )
  end

Called like:

        %{entries: entries, metadata: metadata} =
          Repo.paginate(
            query,
            include_total_count: true,
            after: after_cursor,
            sort_direction: sort_direction,
            cursor_fields: cursor_fields,
            limit: 30
          )

The first cursor returns another 30, but the second cursor returns nothing, but when I get the full list there are 85 items(so the cursor should return 25, but actually returns 0).

Repo.all(query) # returns 85

I’m baffled. The 25 items are there but I don’t get them returned?

Elixir 1.16.1 (compiled with Erlang/OTP 26)
ecto 3.11.2
paginator, 1.1.0

2

What values are being passed here? What SQL gets executed?

The described symptom makes me suspect that something is misaligned, so that the “next” page isn’t using the right query.

3

It looks like this:

        %{entries: entries, metadata: metadata} =
          Repo.paginate(
            query,
            include_total_count: true,
            after: after_cursor,
            sort_direction: :desc,
            cursor_fields: [:inserted_at],
            limit: 30
          )
4

So if I sort by title I don’t see the same problem. Is it possible that if there are inserted_at dates that are identical/very similar the cursor can start to fail?

5

I found a fix, and you led me down the trail, thanks.

cursor_fields: [:inserted_at, :title],

If I use these two columns, it gives the paginator enough to work with and I receive the correct result sets. I think a lot of the inserted_at dates are too similar.

1 Like