https://supabase.com/ logo
Join Discord
Powered by
Hoping for a little advice to set up infinite scro...
# sql
s
Hoping for a little advice to set up infinite scroll 🙏
GOAL Set up infinite scroll for improved performance DETAILS * SvelteKit (this part appears to be straightforward, example: https://svelte.dev/repl/4863a658f3584b81bbe3d9f54eb67899?version=3.32.3) * Querying my 
public.jobs
table CURRENT IMPLEMENTATION Retrieves 100% of the data from 
public.jobs
that is 'valid' (not closed, validity ends some time in the future), and lists items first if they have remuneration details (remuneration != null). This is the main bit of my current plpgsql function.
Copy code
sql
...
return query
  ...
  where j.remuneration_min is not null
    and j.closed = false
    and j.valid_until > now()
  ...
  UNION ALL
  ...
  where j.remuneration_min is null
    and j.closed = false
    and j.valid_until > now()
  ...
This setup becomes unwieldy as the number of jobs grows (mostly from a DOM rendering perspective...), resulting in a bad user experience (at ~100 jobs the query takes ~710ms to come back from supabase). As this is on my index page, this results in an unnecessarily long initial render. SOLUTION..? I think the solution is the seek method w/ index (based on https://wiki.postgresql.org/images/3/35/Pagination_Done_the_PostgreSQL_Way.pdf) but I'm not sure, and I haven't figured out how to do the index when I need to pre-sort based on remuneration `null`/`not null`
I'm very new to all this and would appreciate if someone with experience would share their wisdom 🙂
s
I don't have enough experience to help with the query, but in my wisdom (might be stupidity) avoid infinite scroll, use pagination instead.
s
Oh? Why is that?
It's a job site so I thought infinite scroll would be "nicer" from a ux perspective
s
Try sharing a link to a bunch of jobs that are infinite scrolled and see what happens
s
Wouldn't you link to a specific job not the listing?
s
A lot of times I link to listings as there might be multiple jobs on a particular page that are relevant
s
Hmm ok that's a use case I have not thought of.
s
I don't even know how we got to infinite scroll becoming a thing
s
I have tags etc so you can link to pre-filtered lists
s
Personal opinion, but to date I have never seen a case where infinite scroll has been better than pagination in a listings website
You can add a 
LIMIT
to your query and pass in the values to get it working with infinite scrolling
s
I need 
LIMIT
and also to check what the last item was in the previous result set... so it would be something like this (don't think it's valid sql though?)
Copy code
sql
create or replace function list_jobs(prev_date timestamp with timezone)
...
return query
  ...
  where j.remuneration_min is not null
    and j.closed = false
    and j.valid_until > now()
  ...
  UNION ALL
  ...
  where j.remuneration_min is null
    and j.closed = false
    and j.valid_until > now()
  ...
  where valid_until < prev_date
  limit 10;
I guess the question is, how do I do further conditionals (
where
) on the result of a 
UNION ALL
?
For future reference, it was actually even simpler than above... Simply pass in two number parameters and call 
limit
and 
offset
with those numbers. So the code would be:
Copy code
sql
create or replace function list_jobs(
  limit_num integer,
  offset_num integer
)
...
return query
  ...
  where j.remuneration_min is not null
    and j.closed = false
    and j.valid_until > now()
  ...
  UNION ALL
  ...
  where j.remuneration_min is null
    and j.closed = false
    and j.valid_until > now()
  ...
  limit limit_num offset offset_num;
PreviousNext
Powered by