Rails在jsonb多个where条件中查找子字符串

Question

假设我有一个表recipes，列为ingredients,jsonb列类型。示例记录:

{
id: 1,
ingredients: [
'eggs',
'fragrant bread',
'fresh tomatoes'
]
}

如何在where条件下检索具有子字符串的记录?例如:

ingredients = ['egg', 'tomato']
Recipe.where('ingredients ?& array[:keys]', keys: ingredients)

I was trying:

ingredients = ['egg', 'tomato']
Recipe.where("ingredients @> ARRAY[?]::varchar[]", ingredients).count

但是我得到这个错误:

ERROR:  operator does not exist: jsonb @> character varying[] (PG::UndefinedFunction)

Answer 1

我真的会考虑只使用两个表，因为这是不必要的JSON反模式。

class Recipe
has_many :recipe_ingredients
has_many :recipes, through: :recipe_ingredients
def self.with_ingredients(*ingredients)
binds = Array.new(ingredients.length, '?')
sql = "ingredients.name ILIKE ANY(ARRAY[#{binds.join(,)}])"
joins(:ingredients)
.where(
sql,
*ingredients.map { |i| "'%#{i}%'" }
)
end
end

class RecipeIngredient
belongs_to :ingredient
belongs_to :recipe
end

class Ingredient
has_many :recipe_ingredients
has_many :recipes: through: :recipe_ingredients
end

这使您可以在recipies.name上使用有效的索引，合理的查询并避免反规范化。