Ruby on Rails 连接多个表以及如何提取数据 [英] Ruby on Rails joining multiple tables and how to extract data
问题描述
我正在尝试使用 Ruby on Rails 制作餐厅预订系统.我有三个表:用户、表和预订.'users' 包含 'id'、'name'、'email'、'phone' 和 'totalPersons' 列.'table' 包含 'id' 和 'seats' 列.reservations"包含id"、user_id"、table_id"和begin_datetime"列.
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'.
模型如下所示:
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
我能够加入 tables
和 reservations
表,但我无法将这三个表合并在一起.
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)
你能帮我吗?Rails 4.0 版
Can you help me? Rails version 4.0
在 http://guides.rubyonrails.org/ 我看到了
Post.joins(:comments => :guest)
产生的SQL是:
SELECT posts.* FROM posts
INNER JOIN comments ON comments.post_id = posts.id
INNER JOIN guests ON guests.comment_id = comments.id
我想我的所有用户预订的 SQL 代码和他们预订的桌子看起来像
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
也许这会为你澄清一些事情.所以现在我需要知道它是如何在 ruby on rails 中产生的.
Maybe that will clarify things for you. So now I need to know how that is produced in ruby on rails.
推荐答案
Rails 允许您在字符串中编写自定义连接条件,如下所示:
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)
要提取数据,您只需应用一些自定义的 select
子句,如下所示:
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")
与 join 一样,您可以应用其中的几个.集合中的每个返回的对象都将具有与您在AS"之后定义的列名相同的名称方法(在这种情况下它将是 .不过有一个问题 - 所有这些都将作为字符串返回,因为 AR 不能真的猜猜那些是什么.
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.
这篇关于Ruby on Rails 连接多个表以及如何提取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!