0

So I have a recipes table and an ingredients table. In the recipes table there is an ingredient column that is an array listing the ids of all the ingredients necessary for each recipe.

I want to search through that array to find if it contains one particular ingredient.

I found the include? method which works great in a case like :

Recipe.find(35).ingredient.include? params[:ingredient]

I'm trying to make that work with a where method to get something like this :

Recipe.where('ingredient.include?', params[:ingredient].to_i)

It seems pretty straightforward but it doesn't work. I tried removing the comma, same result.

I can't figure out the proper syntax for it to work.

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
Marine Be
  • 1
  • 1
  • 3

2 Answers2

1

Just a heads up, keeping a column with an array of ids is generally not the best solution!

The preferred approach is Rail's has_and_belongs_to_many association, which uses a join table (ala ingredients_recipes) to store the relation.

Storing ids in an array is a non-starter in many databases, as it simply isn't supported. It's possible in Postgres using the @> contains operator, but you lose the benefits of relational integrity (foreign key constraints) as well as sacrificing automatic management by Rails and other ORMs.

It's an open architectural question though; if you're dealing with high-volume data (millions of rows), arrays could potentially have performance benefits (see Using postgres arrays in relations). It usually isn't worth the hassle of giving up Rails' automatic management of the association though!

gmcnaughton
  • 2,233
  • 1
  • 21
  • 28
  • it's not a string column. – Sergio Tulentsev Oct 12 '16 at 11:09
  • Right you are, I didn't notice the postgresql tag. Thanks! – gmcnaughton Oct 12 '16 at 11:12
  • Thanks!So @gmcnaughton is it ok to have a column with an array of integer or do you still think I should havea join table (I did hesitate between the two options) – Marine Be Oct 12 '16 at 11:29
  • I initially made the same misstake when reading the question - you could still salvage this answer with an edit as its basically a good answer and does not deserve downvotes. – max Oct 12 '16 at 11:46
  • @MarineBe I would still recommend the join table approach over arrays, mostly to make your life easier! For instance, Rails will understand and manage the join table records for you. But it looks like this is an open architectural question with postgres (see http://dba.stackexchange.com/questions/11853/should-arrays-of-ids-stored-in-postgres-use-varchar-int-or-another-type for instance) – gmcnaughton Oct 12 '16 at 12:42
  • 1
    @gmcnaughton there are so many valid reasons why you might want to store an array of ids. So many, in fact, that the good folks who hack on Postgres provided us with an **array column type**. You'll note that in the documentation for the **postgres array column type** that it doesn't say anywhere not to use it. – Michael Johnston Dec 05 '17 at 19:10
  • 1
    Everybody's stackoverflow experience will be much more efficient if you save preaching for comments (like I'm doing!) and save **answers** for **actual answers** for the **actual question**. – Michael Johnston Dec 05 '17 at 19:13
  • @MichaelJohnston thanks for the feedback! I agree that stackoverflow is intended for answers, not coaching. http://wiki.c2.com/?MuAnswer – gmcnaughton Dec 06 '17 at 16:19
0

You would query an array by using any:

Recipe.where('WHERE ? = ANY (ingredient)', params[:ingredient])

Using an array here is hardly ideal though. There are cases where a simple array is suited but not when you should be using a proper relationship.

class Recipe
  has_many :recipe_ingredients
  has_many :ingredients, though: :recipe_ingredients
end

class RecipeIngredient
  belongs_to :recipe
  belongs_to :ingredient
end

class Ingredient
  has_many :ingredients
  has_many :ingredients, though: :recipe_ingredients
end

This will let you query from both ends:

Recipe.joins(:ingredients)
      .where(ingredients: { name: ['basil', 'thyme'] } )
Ingredient.joins(:recipes)
      .where(recipes: { main_type: 'dessert' } )

As well as attach data to the recipe_ingredients table such as the quantity (which is not possible with has_and_belongs_to_many) - it will also let you query the join table directly which can be very useful.

Why not use an array?

Because ActiveRecord is not built around using arrays to store associations. You basically lose all the benefits of ActiveRecord associations for little or no performance benefits. You cannot use foreign key constraints to ensure data integrity in the DB either.

Community
  • 1
  • 1
max
  • 96,212
  • 14
  • 104
  • 165
  • To search for a value in an array in Postgres you would do `SELECT * FROM recipies WHERE 1 = ANY (ingredient);` - https://www.postgresql.org/docs/current/static/arrays.html – max Oct 12 '16 at 11:35
  • Using a proper join table with a compound index on the foreign key columns might actually be more performant than doing array searches. – max Oct 12 '16 at 11:42
  • Thanks for the advice @max. I did try to do a join table but I couldn't figure out how to send data from my form to the two tables since some of the data belonged in the Recipe table and some ine the RecipeIngredient. That's why I gave up and went with the array. Any advice or useful link on that ? – Marine Be Oct 12 '16 at 12:03
  • My advice is to ask a new question and include an example of what you tried to do and what the problem is. – max Oct 12 '16 at 12:13
  • Otherwise it just leads to an unending number of comments. – max Oct 12 '16 at 12:15
  • Yes sir ! Will do. – Marine Be Oct 12 '16 at 12:18
  • here's three reasons when you would use an array to store ids: 1.) they're ids from a different database, and there is not a one-to-one mapping between the part of your model that is populated from data from the other model. 2.) You're developing an mvp and don't want to pay the db cost of fully modelled relationships until you actually develop the features that use them 3.) You need the relationships only for possible future auditing. Anyone who says "**never** use an array" just hasn't worked on enough apps. – Michael Johnston Dec 05 '17 at 19:25
  • I'm not saying never. I'm saying that arrays are not a suitible replacement for decent relational database design like the 99% of cases when noobs try to create many to many associations with them. – max Dec 07 '17 at 12:15