0

In our application, the Recipe model has many ingredients (many-to-many relationship implemented using :through). There is a query to return all the recipes where at least one ingredient from the list is contained (using ILIKE or SIMILAR TO clause). I would like to pose two questions:

  1. What is the cleanest way to write the query which will return this in Rails 6 with ActiveRecord. Here is what we ended up with
ingredients_clause = '%(' + params[:ingredients].map { |i| i.downcase }.join("|") + ')%'
recipes = recipes.where("LOWER(ingredients.name) SIMILAR TO ?", ingredients_clause)

Note that recipes is already created before this point.

However, this is a bit dirty solution. I also tried to use ILIKE = any(array['ing1', 'ing2',..]) with the following:

ingredients_clause =  params[:ingredients].map { |i| "'%#{i}%'" }.join(", ")  
recipes = recipes.where("ingredients.name ILIKE ANY(ARRAY[?])", ingredients_clause)

This won't work since ? automatically adds single quotes so it would be ILIKE ANY (ARRAY[''ing1', 'ing2', 'ing3'']) which is of course wrong.

Here, ? is used to sanitise parameters for SQL query, so avoid possible SQL injection attacks. That is why I don't want to write a plain query formed from params.

Is there any better way to do this?

  1. What is the best approach to order results by the number of ingredients that are matched? For example, if I search for all recipes that contains ingredients ing1 and ing2 it should return those which contains both before those which contains only one ingredient.

Thanks in advance

aldm
  • 343
  • 1
  • 11

1 Answers1

0

For #1, a possible solution would be something like (assuming the ingredients table is already joined):

recipies = recipies.where(Ingredients.arel_table[:name].lower.matches_any(params[:ingredients]))

You can find more discussion on this kind of topic here: Case-insensitive search in Rails model

You can access a lot of great SQL query features via #arel_table.

#2 If we assume all the where clauses are applied to recipies already:

recipies = recipies
  .group("recipies.id")
  # Lets Rails know you meant to put a raw SQL expression here
  .order(Arel.sql("count(*) DESC"))
patrickmcgraw
  • 2,465
  • 14
  • 8
  • `matches_any` will not prepend/append the percent signs so the end user would stilm have to do that first. Additionally I believe the second one can be `Arel.star.count.desc` – engineersmnky Feb 03 '22 at 22:53
  • Correct @engineersmnky, so again I need to use string building adding '%' at the start and end of it. Any neater way? – aldm Feb 05 '22 at 13:14