ROR:从多个表中选择
我有两个表:兴趣和Link_ui(这是用于记录用户和兴趣)
我想输入用户 ID 并显示用户拥有的所有兴趣名称。
在 Link_ui 控制器中:
def output
@interests = LinkUi.find_by_sql [ 'SELECT interests.name FROM link_uis, interests
WHERE link_uis.interest_id = interests.id AND link_uis.user_id=? ', params['user_id'] ]
和输入页面:
<%= form_tag :action => 'output', :method => 'post' %>
enter id.
<%= text_field_tag ':user_id', '', 'size' => 30 %>
什么也没有显示,但我确信数据库中有匹配的数据。如果我不输入参数只是设置 link_uis.user_id = 1,则会出现:
your search are [#<LinkUi >, #<LinkUi >, #<LinkUi >, #<LinkUi >]
这是什么问题..
I have two tables: interests and Link_ui (this is for recording user and interest)
I want to input the user id and show all interests name that user have.
In Link_ui controller:
def output
@interests = LinkUi.find_by_sql [ 'SELECT interests.name FROM link_uis, interests
WHERE link_uis.interest_id = interests.id AND link_uis.user_id=? ', params['user_id'] ]
And input page:
<%= form_tag :action => 'output', :method => 'post' %>
enter id.
<%= text_field_tag ':user_id', '', 'size' => 30 %>
It comes out nothing, but I am sure there is matched data in database. And if I don't input parameter just set link_uis.user_id = 1, it comes out:
your search are [#<LinkUi >, #<LinkUi >, #<LinkUi >, #<LinkUi >]
What's wrong with this..
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
嗯,LinkUi 模型上的
find_by_sql
期望您从link_uis
表返回列,而您只选择interests.name
。然而,您正在与 ActiveRecord 进行一些斗争。 :)您通常希望避免
find_by_sql
,而是让 ActiveRecord 为您生成 SQL。对于您的示例来说,最重要的可能是关联。在我看来,你有一群用户,和一群兴趣。您的LinkUis将这两者联系在一起(LinkUi属于用户和兴趣)。请随时纠正我;这是我从您的示例中收集到的您的业务逻辑。
这些类(我强调了其名称)是您的模型,在
app/models
目录中定义。它们之间的关联(关系)应该在这些类上定义。从您的 User 模型中的简单关联开始:
在您的 Interest 模型中:
然后是将其连接在一起的 LinkUi 模型:
现在,给定任何用户,您只需说出
user.link_uis.all
即可获取他/她的LinkUis,并且对于每个LinkUi >,您可以获得兴趣作为link_ui.interest
。您可以告诉 ActiveRecord 使用:include
尝试尽可能高效地一次获取这两个名称,并使用标准 Rubycollect 获取兴趣名称列表方法。然后它就变成:
你可以更进一步;对于任何用户,您都可以直接获取他/她的兴趣。一旦你建立了上述关联,你就可以将两个“步骤”合二为一,如下所示:
这可以将示例变成这样一句话:
Well,
find_by_sql
on a LinkUi model expects you to return columns from thelink_uis
table, whereas you're selecting justinterests.name
. However, you are picking a bit of a fight with ActiveRecord, there. :)You usually want to avoid
find_by_sql
, and instead let ActiveRecord generate your SQL for you. Probably most important for your example are associations.The way I see it, you have a bunch of Users, and a bunch of Interests. Your LinkUis tie these two together (a LinkUi belongs to a User and an Interest). Feel free to correct me on this; this is your business logic as I gather from your example.
These classes (whose names I've emphasized) are your models, defined in the
app/models
directory. The assocations (relationships) between them should be defined on those classes.Start of with a simple association in your User model:
And in your Interest model:
Then the LinkUi model that ties it together:
Now, given any User, you can get his/her LinkUis by simply saying
user.link_uis.all
, and for each LinkUi, you can get the Interest aslink_ui.interest
. You can tell ActiveRecord to try and fetch these two in one shot as efficiently as possible using:include
, and get a list of Interest names using the standard Rubycollect
method. It then becomes:You can take it one step further; for any User, you can directly get his/her Interests. Once you've set up the above associations, you can fold two ‘steps’ into one, like this:
Which could turn the example into this one-liner: