CODEWITHSUNDEEP
ruby-on-railsselect
user429400
user429400

Reputation: 3325

ruby on rails: how to include a local variable in a select statement

I'm trying to iterate over recipes extracted from my DB, parse the ingredients out of each recipe and insert the ingredients into their own table. The problem is that I don't know how to include the current ingredient variable in my inner select: currently I'm getting the following error:

ExtractIngredients.rb:21:in `query': Unknown column 'ing' in 'field list' (Mysql::Error)

Could you please tell me how to include "ing" in my select statement?

Thanks, Li

begin

db = Mysql.new 'localhost', 'root', 'pass', 'recs'
results = db.query "SELECT id, freeText FROM recipes"

nRows = results.num_rows

for i in 0..nRows-1
    curRow = results.fetch_row
recipeInd = curRow[0]
    recipeFreeText = curRow[1]

    ingredients = getIngredients(recipeFreeText).to_s.scan(/\'(\w+)\'/).flatten

    ingredients.each do |ing|
            db = Mysql.new 'localhost', 'root', 'pass', 'recs'

            # In the next awful select statement I'm trying to insert the current ingredient to the ingredient table if it isn't already there
         db.query "INSERT INTO ingredient(name, created_at, updated_at) SELECT ing, created_at, updated_at FROM dummytable WHERE (SELECT count(*) FROM ingredient WHERE Name = ing)=0"

        ingInd = db.query "SELECT id FROM ingredient WHERE name=ing"
            db.query "INSERT INTO ingredients_recipes(ingredient_id, recipe_id) ingInd, recipeInd"
    end     
end

end

Upvotes: 0

Views: 2211

Answers (2)

Ismael
Ismael

Reputation: 16720

By using interpolation

"..... WHERE Name = '#{ing}' ...."

If you are using Rails, why don't you use the Models for that task, instead of raw sql?

Upvotes: 0

Dave Newton
Dave Newton

Reputation: 160191

Use string interpolation like any other Ruby string: #{ing}

That said, if this is Rails, why are you doing anything like this? And creating a new MySQL per ingredient? Most of this is baked in to the framework.

Upvotes: 1

Related Questions