Home > Net >  How to select and order rows based on number of matched elements in array from foreign table in post
How to select and order rows based on number of matched elements in array from foreign table in post

Time:08-24

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

id title approximate_time
c89d8938-230c-11ed-861d-0242ac120002 Lasagne 90
8b38d376-230d-11ed-861d-0242ac120002 Pasta 30

Ingredients table

id title
bdd52b0e-230d-11ed-861d-0242ac120002 Egg
c49ba170-230d-11ed-861d-0242ac120002 Flour
e886a0d0-230d-11ed-861d-0242ac120002 Tomato
ebee3af8-230d-11ed-861d-0242ac120002 Cheese

Recipe Ingredients table

id amount ingredient_id recipe_id
46ee3552-230e-11ed-861d-0242ac120002 2 bdd52b0e-230d-11ed-861d-0242ac120002 8b38d376-230d-11ed-861d-0242ac120002
4ad02ad6-230e-11ed-861d-0242ac120002 3 c49ba170-230d-11ed-861d-0242ac120002 8b38d376-230d-11ed-861d-0242ac120002
4e391bd8-230e-11ed-861d-0242ac120002 4 e886a0d0-230d-11ed-861d-0242ac120002 c89d8938-230c-11ed-861d-0242ac120002
52407550-230e-11ed-861d-0242ac120002 5 ebee3af8-230d-11ed-861d-0242ac120002 c89d8938-230c-11ed-861d-0242ac120002
563b99a0-230e-11ed-861d-0242ac120002 2 bdd52b0e-230d-11ed-861d-0242ac120002 c89d8938-230c-11ed-861d-0242ac120002

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.

  • Related