CODEWITHSUNDEEP
ruby-on-rails-4
user2493145
user2493145

Reputation: 41

Ruby on Rails joining multiple tables and how to extract data

I am trying to make a booking system for restaurants in Ruby on Rails. I have three tables: users, tables and reservations. 'users' has the columns 'id', 'name', 'email', 'phone' and 'totalPersons'. 'table' has the columns 'id' and 'seats'. 'reservations' has the columns 'id', 'user_id', 'table_id' and 'begin_datetime'.

The models look like this:

class User < ActiveRecord::Base
  has_many :reservations
  has_many :tables, :through => :reservations
end

class Table < ActiveRecord::Base
  has_many :reservations
  has_many :users, :through => :reservations
end

class Reservation < ActiveRecord::Base
  belongs_to :table
  belongs_to :user
end

I was able to join the tables and reservations tables but I was unable to join the three together.

I am trying to show a full reservation with name and at what table the user is.

<table>
  <thead>
    <tr>
      <th>Reserverings ID</th>
      <th>Tafel nummer</th>
      <th>Seats</th>
      <th>User</th>
      <th>Begint om</th>      
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <% @tables.each do |table| %>
      <tr>
        <td><%= reservation.id %></td>
        <td><%= reservation.table_id %></td>
        <td><%= table.seats %></td>
        <td><%= user.name %></td>
        <td><%= reservation.begin_datetime %></td>
        <td><%= link_to 'Show', table %></td>
        <td><%= link_to 'Edit', edit_table_path(table) %></td>
        <td><%= link_to 'Destroy', table, method: :delete, data: { confirm: 'Are you sure?' } %></td>
      </tr>
    <% end %>
  </tbody>
</table>

In the controller my join looks like

@reservations = reservation.joins(:tables, :users)

Can you help me? Rails version 4.0

EDIT: On http://guides.rubyonrails.org/ I saw

    Post.joins(:comments => :guest)

The SQL that is produced is:

    SELECT posts.* FROM posts
    INNER JOIN comments ON comments.post_id = posts.id
    INNER JOIN guests ON guests.comment_id = comments.id

I guess my SQL code for the all reservations with users and the table they booked would look like

    SELECT * FROM reservations, users, tables
    INNER JOIN reservations ON reservations.user_id = users.id
    INNER JOIN reservations ON reservations.table_id = tables.id

Maybe that will clarify things for you. So now I need to know how that is produced in ruby on rails.

Upvotes: 4

Views: 27883

Answers (2)

Edgars Jekabsons
Edgars Jekabsons

Reputation: 2853

Rails allows You to write custom join conditions inside a string, like this:

Reservation.joins('INNER JOIN "tables" ON "tables"."id" = "reservations"."table_id" INNER JOIN "users" ON "users"."id" = "reservations"."user_id"')

or much cleaner just specify several join clauses:

Reservation.joins(:user).joins(:table)

To extract data you need just apply a few custom select clauses, like this:

Reservation.joins(:user).joins(:table).select("tables.seats AS table_seats")

Same as with join, you can apply several of those. Each of the returned objects in collection will have a same name method as You defined the column name after "AS" (in this case it will be . There is a catch though - all these will be returned as strings because AR can't really guess what those are.

Upvotes: 7

davidfurber
davidfurber

Reputation: 5528

You should use "includes" instead of "joins". Also when you're looping through @tables to print the reservations, reservation and user aren't defined. You need to at least say:

@tables.each do |table|
  reservation = table.reservation
  user = reservation.user
  ...
end

Upvotes: 1

Related Questions