Supabase beekend pagination

[ytrewq]

I used WeWeb with Xano as the backend, and I’ve always used backend pagination (https://m.youtube.com/watch?v=CSNCVPYc7wE&t=142…).

However, for my new project, I’ve chosen Supabase.
How does backend pagination work with Supabase?

[Broberto]

As there is no bindable offset and limit in the current Supabase Plugin in WeWeb @Alexis would be lovely to have this.

You can do a query like this, which creates a function actually, which you then can call with GraphQL maybe, and with REST API for sure.

You can create the function by inputting a function like this into your SQL Editor

CREATE OR REPLACE FUNCTION get_available_jobs(pages integer, page integer) 
RETURNS TABLE (id int8, is_emergency boolean, company text, address text, city text, last_modified date) 
SECURITY INVOKER  -- Use SECURITY INVOKER to execute the function with the invoker's privileges
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
 SELECT
  j.orders_proxy_id, j.is_emergency, j."NAZEV_ODB", j."ULICE_ODB", j."MESTO_ODB", j."DATUM_SPL"
FROM
  orders j
WHERE -- optional
j.part is null -- optional
ORDER BY j."DATUM_SPL" DESC
LIMIT pages
OFFSET (page-1) * pages
;
END;
$$;

Fields and the variables are up to you.

Now that I’m looking at it, I see there is some pagination in the plugin, but I’m not sure if it’s on the backend? @Joyce?

[Joyce]

Hi @ytrewq

The pagination you setup here in a Supabase collection will be a backend pagination because you are configuring the query before the data is fetched:

That pagination should work well with the Paginator element.

Depending on your use case, that may not be flexible enough and you might want to revert to @Broberto’s proposed solution.

By the way, the team is working on adding options to the Supabase plugin and does have this in mind

@Broberto

As there is no bindable offset and limit in the current Supabase Plugin in WeWeb @Alexis would be lovely to have this.

[Broberto]

I just noticed this also :)) Does this paginate on the backend? I’d love to see how it works.

[Joyce]

Yes it does paginate in the backend. See here the response sent by Supabase to my browser only includes 2 items:

No idea how it works under the hood though

[Broberto]

I think it actually does the operation of calculating the offset on the frontend. So the (page - 1) * pages part is done by WeWeb, after that limit is done in the call, together with the calculated offset.

Cool

[ytrewq]

Hello Joyce,

Thank you very much for the response. By the way, it’s exactly the answer I wanted to hear. So, that’s perfect.

Since configuring the query before the data is fetched works for pagination, can I apply the same concept for other types of filters?

[ytrewq]

Ciao Roberto

Thank you very much for your response.

As for pagination, @Joyce has addressed all my concerns, but the GraphQL query approach in Supabase is something I want to delve into because I need it for this project.

Do you have experience with it?

A.

[Joyce]

@ytrewq

Since configuring the query before the data is fetched works for pagination, can I apply the same concept for other types of filters?

If you’re configuring the filters in that “Query configuration” step of the collection interface, yes, Supabase should only send back the relevant data to WeWeb.