Question 90337 in PostgreSQL Question posted in
The official documentation can be found here.

Get count and specify range in a supabase js query – Postgresql

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)
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search