Get count and specify range in a supabase js query

Question

Im making a recipe website with nextjs and supabase. I have an occasions table which has a foreign key to many recipes. I can easily query the occasions for all recipe in that occasion however I want to paginate so I need a count and a range. How would I go about doing this or would I have to make an RPC instead?

Here is my current query that returns all recipes in an occasion - comments in the query is what I want to put a count/range on

    .from("occasions")
    .select(
      `*,
      recipes_occasions(
        recipes( 
// I WANT TO GET EXACT COUNT OF ALL RECIPES IN AN OCCASION HERE
          recipeId, title, slug, totalTime, yield, ingredients,
          recipes_categories (
            categories(title, slug)
          ),
          recipes_diets (
            diets(title, slug)
          ),
          recipes_cuisines (
            cuisines(title, slug, code)
          )
        ),
        
      )`
    )
    .eq("slug", slug )
    .range(0,4)
// I WANT TO TAKE SPECIFIC RANGE HERE (e.g. range(12, 24) or range(200, 212)

What columns are included in `occasions`? You want to paginate the recipes, and not the occasions, correct? — dshukertjr, Dec 31 '22 at 04:23
Yes I want to paginate the recipes. I have a recipes_occasions table with occasion_id and recipe_id. The above works perfectly fine but is showing all recipes in an occasion. Ideally Id want to paginate so that only 12 are shown at a time. — Yesthe Cia, Dec 31 '22 at 14:01
Thanks for all the info. And what columns are included in occasions? What are occasions in this context? — dshukertjr, Dec 31 '22 at 14:38
The occasion is just occasionId, name and image. For simplicity I have a recipe table with recipeId, name and image. I have occasions with occasionId, name and image and I have recipes_occasions with recipeId and occasionId. The original code works and returns everything as expected. I just want to add a range to the recipes so I can take records x to y, but also a full count of all the recipes that have a relationship with that occasion so that I know where to stop the pagination. — Yesthe Cia, Dec 31 '22 at 14:45
I'm beginning it might be better to have 2 queries. First to get the occasioId from the slug - then a second to do the rest of the query on recipes_occasions using the occasion_id rather than the occasion slug.. that way I'd be 1 level shallower and doing the meaty query on .from(recipes_occasions) if that makes sense — Yesthe Cia, Dec 31 '22 at 14:56

score 1 · Accepted Answer · edited Jan 01 '23 at 13:53

1

There are multiple ways you could solve this problem. Splitting it up to two separate queries is certainly one option.

Another option might be to flip the query and get the recipes along with it's associated occasions.

const { data, error } = supabase
  .from("recipes")
-- Edit above fixed recipe typo
  .select(
    `*,
    recipes_categories (
      categories(title, slug)
    ),
    recipes_diets (
      diets(title, slug)
    ),
    recipes_cuisines (
      cuisines(title, slug, code)
    ),
    occasions!inner(*)`,
    { count: 'exact' }
  )
  .eq("occasions.slug", slug )
  .range(0,4)

count: 'exact' will get the number of recipes so that you can use it to paginate.

The !inner keyword used after occasions allows you to filter the results of recipes selected based on values of occasions. -- Edit above fixed recipe typo

edited Jan 01 '23 at 13:53

Yesthe Cia

528
1
7
20

answered Dec 31 '22 at 15:04

dshukertjr

15,244
11
57
94

Interesting - Getting an error now though in my terminal ```{ code: 'PGRST200', details: null, hint: "Verify that 'recipes' and 'occasions' exist in the schema 'public' and that there is a foreign key relationship between them. If a new relationship was created, try reloading the schema cache.", message: "Could not find a relationship between 'recipes' and 'occasions' in the schema cache" }``` Any ideas? – Yesthe Cia Dec 31 '22 at 17:07
@YestheCia I can verify that it works on my end, so make sure there are no typos. Anyway, it might be simpler to just use two separate queries as suggested in your answer. When you use `{count: 'exact'}` though, it returns the count of the row of the table in the `from()` statement. If you want to paginate `recipies`, you would need to use `recipies` in your `from()` statement instead of `recipes_occasions `. – dshukertjr Jan 01 '23 at 11:13
1

Thanks mate - I really appreciate your help. I fixed the typo in your answer and accepted it as it does work perfectly. My code as below correctly returns the count of recipes (not the recipes_occasions count) – Yesthe Cia Jan 01 '23 at 12:56

Yesthe Cia · Answer 2 · 2022-12-31T17:53:58.353

I managed to get the range working by first getting the occasionID from the slug and then another query to get the recipes with that occasion ID like so. Added count to destructure also.

const { data, error } = await supabase
  .from("occasions")
  .select(`occasionId`)
  .eq("slug", context.query.slug )

  const occasionId = await data[0].occasionId;

  const { data: recipes, count, recipeError } = await supabase
  .from('recipes_occasions')
  .select(`*, recipeDetails: recipes(title)`, {count: 'exact'})
  .eq('occasion_id', occasionId)
  .range(0,1)

Result in my terminal

 [
  {
    id: 134,
    created_at: '2022-12-13T18:17:35.433285+00:00',
    recipe_id: 'd7c493ff-6aae-4929-bf74-c6d44e2eb8f7',
    occasion_id: '1c907e40-4717-4c22-aeb6-c51044fb276a',
    recipeDetails: { title: 'Easter egg cheesecake' }
  },
  {
    id: 135,
    created_at: '2022-12-13T18:18:39.011853+00:00',
    recipe_id: '30254e94-6692-4a57-a173-c5ccee758d59',
    occasion_id: '1c907e40-4717-4c22-aeb6-c51044fb276a',
    recipeDetails: { title: 'Rhubarb & custard tart' }
  }
]
13

Get count and specify range in a supabase js query

2 Answers2