useOffsetInfiniteScrollQuery does not account for added/removed rows

[roobox]

Describe the bug I am using useOffsetInfiniteScrollQuery to achieve an Inifite-Scroll effect in my web application. The data is sorted by the created_at column (descending order) and loading more data (pageSize = 10) is working perfectly, until someone inserts or deletes a row. When I insert an row to my supabase DB using a stored procedure and then insert this manually to my SWR frontend cache via useUpsertItem this item gets added at index 0 to my data array, as it has the newest created_at value. It is also at position 0 in the DB table when sorting it using the created_at column. As the the "offset" value of the SQL query stayed the same, but in the sorted DB all rows were shifted one position down, the first row of the next page is the same as the last row of the previous page (so I am fetching 2 times the same row).

To Reproduce

1. Setup Infinite Scroll Query: const { data, loadMore, isValidating, error } = useOffsetInfiniteScrollQuery( supabase .from('books') .select('id, name, created_at') .order('created_at', { ascending: false }) //multiple rows can have the same created_at value, e.g. when using Batch inserts .order('id', { ascending: false }) { pageSize: 10 }, );

2. Add row to DB without this package by using a stored procedure

3. Insert the added row to the SWR Cache const upsertItem = useUpsertItem({ primaryKeys: ['id'], table: 'books', schema: 'public' }); upsertItem({id: '231c220d-f4d7-4ca9-8761-d1beee69cbb7', name: 'testbook', created_at: new Date().toISOString()})

4. Load more data loadMore() // This is the point where my described bug occurs. The last row of the first page and the first row of the second page are identical, as the offset-value has not changed, but so did the DB table.

Expected behavior I would expect that at some place I can "manually" adjust the offset. In my case where I add an element to the SWR cache I want to increase the Offset by one so instead of performing: SELECT "id", "name", "created_at" FROM "books" ORDER BY ... OFFSET 10 LIMIT 10

I want to perform: SELECT "id", "name", "created_at" FROM "books" ORDER BY ... OFFSET 11 LIMIT 10

And when I delete an Item via useDeleteItem I want to be able to decrease the offset by 1.

Additional context I cant use useCursorInfiniteScrollQuery, as the created_at column does not hold unique values (i.e. batch imports) and the package would need to be extended to support additional filters and also using >= created_at instead of > created_at (see https://the-guild.dev/blog/graphql-cursor-pagination-with-postgresql#cursor-pagination-with-additional-filters). Maybe this is also worth considering.

[pikameow420]

Hey @roobox,

I am running into the same issue. Were you able to find a workaround for this?

[psteinroe]

Hey! Thanks for opening the issue. I understand what's going wrong, but I don't have a good fix for this. For now, I would propose to just revalidate the query.

I was thinking about refactoring the pagination and infinite scroll hooks, and this is good input. Let me know if you have more!

[roobox]

Hi @pikameow420 ,

I endet up developing my own inifiniteScroll implementation to be able to handle "more complex" cases.

[psteinroe]

Hi @pikameow420 ,

I endet up developing my own inifiniteScroll implementation to be able to handle "more complex" cases.

can you share it? :)

[roobox]

Hi @pikameow420 , I endet up developing my own inifiniteScroll implementation to be able to handle "more complex" cases.

can you share it? :)

Sure! I am using SWR from Vercel. Here my code:

`import useSWR from 'swr' import useSWRMutation from 'swr/mutation' import { useState } from 'react' import supabase from '../utils/supabaseClient'

/**

• Fetch a list of books with an initial page load size. */ const getBooks = async ( url: string, searchTerm: string, sortBy: string, sortAscending: boolean, initialPageLoad: number, discountId: string | null ) => { // Example: cacheKey = books_<categoryId>_optionalDiscountId => extract categoryId const categoryId = url.split('_')[1] if (!categoryId || categoryId === 'null' || categoryId === 'root') { return [] }

let query = supabase .from('books_overview') .select('*') .eq('category_id', categoryId) .or(title.ilike.%${searchTerm}%,description.ilike.%${searchTerm}%) .order(sortBy, { ascending: sortAscending }) .range(0, initialPageLoad - 1)

// Optionally filter by discountId if (discountId !== null) { query = query.contains('book_meta', { discount_id: discountId }) }

const { data, error } = await query if (error) { throw new Error('Failed to retrieve Books') }

console.log('Fetched Books:', data) return data }

/**

• Fetch additional books for infinite scroll (subsequent pages). */ const loadMoreBooks = async ( url: string, searchTerm: string, sortBy: string, sortAscending: boolean, offset: number, successivePageLoad: number, discountId: string | null ) => { const categoryId = url.split('_')[1] if (!categoryId || categoryId === 'null' || categoryId === 'root') { return [] }

let query = supabase .from('books_overview') .select('*') .eq('category_id', categoryId) .or(title.ilike.%${searchTerm}%,description.ilike.%${searchTerm}%) .order(sortBy, { ascending: sortAscending }) .range(offset, offset + (successivePageLoad - 1))

if (discountId !== null) { query = query.contains('book_meta', { discount_id: discountId }) }

const { data, error } = await query;

if (error) { throw new Error('Failed to load more Books') }

return data }

/**

• Custom hook to manage infinite scroll of books from Supabase. */ export default function useBooks( categoryId: string | null, searchTerm: string, sortBy: string, sortAscending: boolean, initialPageLoad: number = 25, discountId: string | null ) { const [hasMore, setHasMore] = useState(true)

// Define how many items to load on each subsequent fetch const successivePageLoad = 20

// Build a cache key that includes discountId if it exists const cacheKey = discountId ? books_${categoryId}_${discountId} : books_${categoryId}

// 1) Load the initial set of books const { data: books, error: booksError, isLoading: booksIsLoading, mutate: booksMutate, } = useSWR( cacheKey, (url) => getBooks(url, searchTerm, sortBy, sortAscending, initialPageLoad, discountId), { fallbackData: [], onSuccess: (data) => { // If we got exactly initialPageLoad items, there's probably more setHasMore(data.length === initialPageLoad) }, } )

// 2) Load more books when scrolling const { trigger: loadMore, isMutating: loadMoreRunning, error: loadMoreError, } = useSWRMutation( cacheKey, (url, { arg }) => loadMoreBooks(url, searchTerm, sortBy, sortAscending, books.length, successivePageLoad, discountId), { // Populate SWR’s cache with the newly loaded items populateCache: (loadedBooks, currentBooks) => [...(currentBooks || []), ...loadedBooks], revalidate: false, rollbackOnError: true, onSuccess: (data) => { // If we got exactly successivePageLoad, there's probably more to load setHasMore(data.length === successivePageLoad) }, } )

// Optional helper to force-refresh the entire list const refreshBooks = async () => { await booksMutate( async () => getBooks(cacheKey, searchTerm, sortBy, sortAscending, initialPageLoad, discountId), { revalidate: false } ) }

if (!categoryId) { return { books: null, loadMore: () => null, booksError: null, booksIsLoading: null, booksMutate: null, refreshBooks: null, hasMore: false, } }

return { books, loadMore, hasMore, booksError, booksIsLoading, booksMutate, refreshBooks, loadMoreRunning, loadMoreError, } }`