How to query Supabase to filter with relationship table and pagination?

Question

I have two tables job and category with a 1:M relationship. I want to fetch up to 10 jobs at a time using pagination for a given category_name, which is a column from the table category. category_name value is of type JobCategoryEnum.

I have tried various ways to achieve this using .eq() filtering and .range() pagination but it fails for now:

async function fetchJobsByCategory(category:JobCategoryEnum, page = 1, pageSize = 10) {

    const { data, error } = await supabaseBrowserClient
            .from('job')
            .select(
                `*, 
                category(category_name)`
            )
            .eq('job_status', 'open')
            .eq('category:category_id (category_name)', category) 
            .range((page - 1) * pageSize, page * pageSize - 1);
    }

I also tried changing the filtering line to the following options:

.eq('category(category_name)', category) 
.eq('category.category_name', category) 
.eq('category:category_name', category)

I believe that I have to do this filtering before the pagination otherwise if I do the filtering client-side, I will get paginated results from my query and will miss results.

Any idea what I am doing wrong? I couldn't find an explicit example on the Supabase docs. Thank you

dshukertjr · Accepted Answer · 2024-03-07 00:48:31Z

4

To filter the referenced table, you can use the referencedTable.column syntax as described here. To apply the range() to a referenced table, you can add the referencedTable option to range().

const { data, error } = await supabaseBrowserClient
  .from('job')
  .select(`*, category!inner(category_name)`)
  .eq('job_status', 'open')
  .eq('category.category_name', category)
  .range((page - 1) * pageSize, page * pageSize - 1)

edited Mar 7, 2024 at 0:48

answered Feb 19, 2024 at 6:23

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

thib Over a year ago

Thank you, it works! I was missing the referencedTable in the range.

thib Over a year ago

Actually, only when I change .select(*, category(category_name)) to .select(*, category!inner(category_name)) is the range applied in my results @dshukertjr

dshukertjr Over a year ago

@thib Yeah, that makes sense. I misread the question and thought you wanted to paginate the categories, but yes if you want to paginate the job table, you would need the !inner keyword. In that case, you wouldn't need the referenceTable in the range() function either. I have updated my response just in case.

Collectives™ on Stack Overflow

How to query Supabase to filter with relationship table and pagination?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related