Querying Multiple Database table and display results in index.html.erb

12 views
Skip to first unread message

David Merrick

unread,
May 22, 2019, 11:09:20 PM5/22/19
to Ruby on Rails: Talk
I am trying to Querying Multiple Database table and display results in races index.html.erb

Schema is this

ActiveRecord::Schema.define(version: 2019_05_21_043953) do

  create_table "days", force: :cascade do |t|
    t.date "day"
    t.integer "season_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["season_id"], name: "index_days_on_season_id"
  end

  create_table "races", force: :cascade do |t|
    t.boolean "display"
    t.text "racename"
    t.text "class"
    t.integer "season_id"
    t.integer "day_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["day_id"], name: "index_races_on_day_id"
    t.index ["season_id"], name: "index_races_on_season_id"
  end

  create_table "seasons", force: :cascade do |t|
    t.integer "year"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

A typical query in rails console --sandbox Querying all the races for that day

SELECT day,class,racename FROM RACES
INNER JOIN DAYS on days.id = races.day_id
INNER JOIN SEASONS on days.season_id = seasons.id
WHERE days.id = '46'

"2019-04-20" "Pee Wee" "Pee Wee Div 2"
"2019-04-20" "Pee Wee" "Pee Wee Div 1"
"2019-04-20" "Juniors" "Juniors Div 2 Shilo Tocher Cup"
"2019-04-20" "Juniors" "Juniors Div 1 Gavin Tavendale Cup"
"2019-04-20" "Solo" "Solo's Robin McKinnon Plate"
"2019-04-20" "Side Cars" "Sidecars Tony Schafer Cup"


I have this as the race model

class Race < ApplicationRecord
  belongs_to :season
  belongs_to :day

  class << self

  def with_season(seasons)
  joins(:seasons).where(seasons: {id: seasons})
  end

  def with_day(days)
  joins(:days).where(days: {id: days})
  end 

  def with_race(races)
  joins(:races).where(races: {id: races})
  end
  end
end

I want to select the Season ,the day and the race

with something like this in races index.html.erb

<div class="control-group">
  <%= f.label :day_id , class: 'control-label' %>
  <div class='controls'>
    <%= collection_select(:race, :day_id, Day.all, :id, :day, {}, {:multiple => false}) %>
  </div>
</div>

So far I have this for races index.html.erb

<p id="notice"><%= notice %></p>

<h1>Races</h1>

<table>
  <thead>
    <tr>
      <th>Display</th>
      <th>Racename</th>
      <th>Class</th>
      <th>Season</th>
      <th>Day</th>
      <th colspan="3"></th>
    </tr>
  </thead>

  <tbody>
    @races = Race.with_season(params[:season_id]).with_day(params[:day_id]).with_race(params[:_id])
    <% @races.each do |race| %>
      <tr>
        <td><%= race.display %></td>
        <td><%= race.racename %></td>
        <td><%= race.class %></td>
        <td><%= race.season %></td>
        <td><%= race.day %></td>
        <td><%= link_to 'Show', race %></td>
        <td><%= link_to 'Edit', edit_race_path(race) %></td>
        <td><%= link_to 'Destroy', race, method: :delete, data: { confirm: 'Are you sure?' } %></td>
      </tr>
    <% end %>
  </tbody>
</table>

<br>

<%= link_to 'New Race', new_race_path %>

Any Questions or suggestions just post please


Cheers Dave

Ariel Juodziukynas

unread,
May 22, 2019, 11:28:15 PM5/22/19
to rubyonra...@googlegroups.com
Do you have an error or something?

I don't get why you do that `joins(:races)`, maybe I'm missing something but sound like you could just do `where(id: races)`.

You can call `to_sql` on the query to see the generated SQL query if you want to check what's going on.

I would move this `@races = Race.with_season(params[:season_id]).with_day(params[:day_id]).with_race(params[:_id])` to the controller.

Personally, I would use named scopes instead of class methods

scope :with_season, -> (season_id) { includes(:seasons).where(seasons: {id: season_id}) }
#etc 

Anyway, I don't understand if you have an error or something or what.

--
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 rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/f0edb9b1-7d29-4e30-bbaf-fa6061744f4f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

David Merrick

unread,
May 23, 2019, 12:47:06 AM5/23/19
to Ruby on Rails: Talk
This is the error I get 'undefined method `fetch_value' for nil:NilClass'

MoorePark Results.png

This is what I ultimately want the page to look like See attacked picture


I have not include the last Database Table yet in my code. I know what the SQL statements look like. I just don't know how to do the select from the form and to get data to the Database and retrieve the results and display them

Ariel Juodziukynas

unread,
May 23, 2019, 9:00:25 AM5/23/19
to rubyonra...@googlegroups.com
Which line throws the error? show the error stack

I'm not sure you can have a column named "class" while using activerecord. "class" is a reserved word (race.class should return the Race class, not an attribute value). It could be causing weird hidden problems.

I still don't understand why you do `joins(:races)` since your Race model doesn't have an association with another race object.

show the server log, you are omitting a lot of information

--
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 rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.

David Merrick

unread,
May 23, 2019, 6:27:52 PM5/23/19
to Ruby on Rails: Talk
I will repost after I have changed the Race Table

Cheers Dave


For more options, visit https://groups.google.com/d/optout.


--
Dave Merrick

TutorInvercargill


Email merri...@gmail.com

Ph   03 216 2053

Cell 027 3089 169
Reply all
Reply to author
Forward
0 new messages