I'm creating simple supabase application and I want to construct postgREST query to select recipes and order them ascending by number of matched_ingredients

Right now I'm stuck on this:

https://URL.supabase.co/rest/v1/recipes?
select=*,ingredients:recipes_ingredients(*,ingredient_id(*))
&ingredients.ingredient_id=in.(92cd47bc-14a2-4d54-84d7-36e9ae96873f)

Example: Some user have eggs, tomatoes and cheese in their fridge. When I execute query it will return

[
  {
    "id": "c89d8938-230c-11ed-861d-0242ac120002",
    "title": "Lasagne",
    "approximate_time": 90,
    "total_ingredients": 3,
    "matched_ingredient": 2,
    "ingredients": [
      ...
    ]
  },
  {
    "id": "8b38d376-230d-11ed-861d-0242ac120002",
    "title": "Pasta",
    "approximate_time": 20,
    "total_ingredients": 3,
    "matched_ingredient": 1,
    "ingredients": [
      ...
    ]
  }
]

Recipes table

Ingredients table

Recipe Ingredients table

CodePudding user response：

You should probably consider one of the following approaches to simplify your usage of PostgREST:

1. Create a SQL/PSQL function and call it using RPC.

2. Create a view directly in the database that combines all the information you have (and use RLS to filter out).

The view would look like this (please adapt it to the actual fields/your use cases):

CREATE TEMP VIEW matched_ingredients AS SELECT r.id, r.title, r.approximate_time, 
COUNT(re_ing.ingredient_id) as total_ingredients, COUNT(u.ingredient) as matched_ingredients
FROM recipes r INNER JOIN recipes_ingredients re_ing ON r.id = re_ing.recipe_id
RIGHT JOIN user_ingredients u ON u.ingredient = re_ing.ingredient_id
GROUP BY r.id
ORDER BY total_ingredients DESC;

The view above groups the recipes_ingredients by the receipt id, so you can have the total of ingredients in each recipe. Then it counts the number of ingredients that the user has for the matched column.

Notes: I am assuming you have a users table with users.ingredient.

PS: I am not considering the amount of each ingredient, please handle that yourself as well.