0

I have a database table which contains recipe information, I then have a second table which contains user information and finally I have another table which is a junction table between the two containing recipe_id and user_id which is used to hold the user's saved recipes.

My tables look like this:

|user |

|user_id | |user_name | |user_email | |user_password|

|recipe|

|recipe_id| |recipe_name| |recipe_descript| |category_id| | etc.|

|user_saved_recipes|

|user_saved_recipe_id| |user_id| |recipe_id|

|category|

|category_id| |category_name|

I want to be able to SELECT recipes from the recipe table that are LIKE recipes from the user_saved_recipes table based on a particular user. If anyone could help me go about this I would be very grateful as I have been struggling to find a solution.

Basically I want to be able to get a list of recipes that are similar to those recipes the user has saved thus creating suggestions for the user.

This is what I have tried so far:

SELECT * 
  FROM recipe r
  JOIN user_saved_recipes ur
    ON r.recipe_id = ur.recipe_id 
  JOIN user u
    ON ur.user_id = u.user_id 
 WHERE ur.user_id = 1;

I know I need a LIKE in there somewhere in order to return rows with a similar name but I am unsure of how to go about this as I only have recipe_id in the user_saved_recipes junction table.

Thanks!

seshkebab
  • 37
  • 6
  • You only have 2 columns of text in the recipe table to base "similarity" that is probably inadequate for the task. Do you have an ingredients table? do you categorize recipes (e.g. soups, meat, dessert). I think much more information is needed to answer thsi question - but perhaps this will help: https://stackoverflow.com/questions/5322917/how-to-compute-similarity-between-two-strings-in-mysql – Paul Maxwell Mar 26 '21 at 00:19
  • @PaulMaxwell Yes, I do categorise recipes. Sorry, I should have included that in my example. I have a categories table and there is a FK link to it in the recipes table. Ideally I want to be able to find the top category the user's saved recipes fall into and then display other recipes within this category. – seshkebab Mar 28 '21 at 20:42

1 Answers1

0

Recipes that are "like" other recipes is a very vague request. You have to consider - and specify - how you will calculate "like" in this case. What are some typical use cases you wish to meet? e.g.

Consider that someone really likes rabbit as an ingredient; "You may also like" suggestions from your software should include other rabbit recipes.

So matching on ingredients (particularly the "hero" ingredient) is very likely to part of your algorithm.

Similarly, if a person has saved several cakes in their favourites, then the category cake will likely be a useful part of that algorithm.

Basically you need to compute a rank you can apply to recipes not already in their favourites that share some characteristics with those within the favourites.

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51