Querying SQL Database in Rails in irb

[David Merrick]

Schema

create_table "days", force: :cascade do |t|

    t.integer "season_id"

    t.datetime "created_at", null: false

    t.datetime "updated_at", null: false

    t.date "raceday"

    t.index ["season_id"], name: "index_days_on_season_id"

  end

create_table "seasons", force: :cascade do |t|

    t.date "year"

    t.datetime "created_at", null: false

    t.datetime "updated_at", null: false

  end

@seasonmax = Season.last.id gives me me 7 the last id in the Seasons Table

@days = Day.find_by_sql("SELECT DISTINCT days.id,raceday FROM days INNER JOIN seasons on seasons.id WHERE days.season_id = @season_id") gives me an an empty list

@days = Day.find_by_sql("SELECT DISTINCT days.id,raceday FROM days INNER JOIN seasons on seasons.id WHERE days.season_id = 7") gives me the list I want

[#<Day id: 39, raceday: "2018-10-21">, #<Day id: 40, raceday: "2018-11-16">, #<Day id: 41, raceday: "2018-12-01">, #<Day id: 42, raceday: "2019-01-12">, #<Day id: 43, raceday: "2019-02-16">, #<Day id: 44, raceday: "2019-03-02">, #<Day id: 45, raceday: "2019-03-31">, #<Day id: 46, raceday: "2019-04-20">] 

 Why is the @seasonmax  variable not picked in the SQL  Query

@days = Day.find_by_sql("SELECT DISTINCT days.id,raceday FROM days INNER JOIN seasons on seasons.id WHERE days.season_id = @season_id")

[Phil Edelbrock]

Looks like a few things wrong with the techniques here, but the jist is that @season_id in SQL means nothing meaningful to the SQL backend. Perhaps something like:

@days = Day.find_by_sql("SELECT DISTINCT days.id,raceday FROM days INNER JOIN seasons on seasons.id WHERE days.season_id = "+(@season_id.to_s))

But, this seems rather odd and can open up some issues with over complicating the code and possible SQL injection, etc.

Hope this helps.


Phil