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

Answer 1

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)