Querying SQL Database in Rails in irb

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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")



--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/d83efdba-feb9-4da2-b56e-f30a8009d2a7%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Querying SQL Database in Rails in irb

Phil Edelbrock

> On Jun 23, 2019, at 10:32 PM, David Merrick <[hidden email]> wrote:
>
> 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")


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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/3D5684BB-6CB0-4161-8DC6-03369EEFBCC0%40gmail.com.
For more options, visit https://groups.google.com/d/optout.