Get count and specify range in a supabase js query-CodePudding

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)

CodePudding user response：

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("recipies")
  .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 recipies selected based on values of occasions.

CodePudding user response：

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