Rails PostgreSQL - Multiple Keyword Search using Full Text Search-CodePudding

I have a simple Ruby on Rails app CookBook, with recipes. I have 2 tables (recipes and ingredients). One recipe has many ingredients.

I am trying to implement a simple search box so that I can filter recipes containing certain ingredients....

These are the tables:

create table recipes (id int, recipe_name varchar(100));

create table ingredients(id int, ingredient_name varchar(100), recipe_id int);

insert into recipes values 
(1, 'my recipe 1'),
(2, 'my recipe 2');
(3, 'my recipe 3');
(4, 'my recipe 4');

insert into ingredients values
(1, 'Banana', 1), 
(2, 'Milk', 1),
(3, 'Banana', 2), 
(4, 'Milk', 2),
(5, '4 ½ teaspoons baking powder', 2);
(6, '1 ½ cups whole wheat flour', 4),
(7, 'Heavy Cream', 4);
(8, '⅓ cup packed brown sugar', 3),
(9, 'Milk', 3);
(10, '2 teaspoons packed brown sugar', 4);

Let's say I type in search box two ingredients, for example "banana" and "milk". Then I am expecting to get recipes with rows from ingredients table that contain those words...

This is the code in my recipes_controller I currently have:

  def search
    if params[:search].blank?
      redirect_to recipes_path and return
    else 
      @parameter = params[:search].downcase
      @recipe_ids = Ingredient.search_ingredient(@parameter).pluck(:recipe_id)
      @results = Recipe.where(id: @recipe_ids).paginate(page: params[:page], per_page: 26)
    end
  end

code in ingredient.rb:

class Ingredient < ApplicationRecord
  belongs_to :recipe
  include PgSearch::Model
  pg_search_scope :search_ingredient, 
                  against: { ingredient_name: 'A' },
                  using: { 
                    tsearch: { 
                      dictionary: 'english', tsvector_column: 'searchable' 
                    } 
                  }
end

The problem with this is that if I type 'Banana milk' in search box, I will not get anything in result set, because they are in separate rows in ingredients table, belonging to a recipe. In this case, I should get at least recipes with id 1 and 2, because they both have those ingredients

If I search for only one ingredient, like "milk" then I get all the recipes containing that ingredient.

How do I accomplish this?

Thank you in advance

CodePudding user response：

I advise you to use any_word attribute, look at the documentation here - https://github.com/Casecommons/pg_search#any_word

pg_search_scope :search_ingredient, 
                  against: { ingredient_name: 'A' },
                  using: { 
                    tsearch: { 
                      any_word: true,
                      dictionary: 'english',
                      tsvector_column: 'searchable'
                    } 
                  }