How to select and order rows based on number of matched elements in array from foreign table in postgREST

Question

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

Have you tried to append `&order=matched_ingredient.desc` to the request? — Mansueli, Aug 23 '22 at 18:55
No, but the problem is that I can't figure out how to create such a column. — spaceplacepl, Aug 23 '22 at 18:58

Mansueli · Accepted Answer · 2022-08-24T19:09:32.617

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

Create a SQL/PSQL function and call it using RPC.
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 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.

How to select and order rows based on number of matched elements in array from foreign table in postgREST

Recipes table

Ingredients table

Recipe Ingredients table

1 Answers1