Has anyone successfully implemented custom backend pagination with the paginator component using Supabase? I’m not sure it’s possible because I believe you need to be able to return the total record count in the response body, but Supabase (using postgrest) only returns that value inside the response headers.
I may be able to add the count to the response through a custom function in Supabase/postgres but I’m not positive.
I’m wanting to do this because I want to dynamically choose the database to pull from based on the logged in user, so I’m using the REST API plugin instead of the Supabase’s (which works with the paginator component out of the box).
Hi, did you try to use the paginator in normal mode ? If I remember well we already handled this case, the paginator should work natively with your supabase collections, the pagination will apply backend side.
The paginator works out of the box if using the Supabase plugin, but I can’t use it as I want to be able to dynamically choose the table to pull from. I got it working with a custom paginator though!
For future reference:
First, I create a stored procedure in Supabase that returns the row count with the table data:
CREATE OR REPLACE FUNCTION get_table_with_count()
RETURNS TABLE (
id text,
full_name text,
email text,
total_rows int
) AS $$
DECLARE
total_count int;
BEGIN
SELECT COUNT(*) INTO total_count FROM my_table;
RETURN QUERY
SELECT my_table.id, my_table.full_name, my_table.email, total_count
FROM my_table;
END;
$$ LANGUAGE plpgsql;
I then used the following endpoint in Weweb:
‘https://my_id.supabase.co/rest/v1/rpc/get_table_with_count?limit=x&offset=y’ where x and y get bound to a paginator variable.
Finally, I essentially just followed the docs page that describes how to set up backend pagination with Xano: created a paginator variable and then set the paginator component to custom with a workflow that sets the paginator variable and fetches the collection. I used the total_rows column from my function for the Total Items value in the paginator component.
