如何加入派生表？ [英] How can i join with a derived table?

问题描述

sql语句如下：

select posts.id, posts.title 
from posts 
inner join (select distinct post_id,created_at  
            from comments 
            order by created_at DESC limit 5
            ) as foo 
        on posts.id=foo.post_id 
order by foo.created_at DESC; 

我想获得与上述代码等效的Rails 3 sql语句。

I want to get a rails 3 sql statement equivalent to the above one.

下面给出了我尝试过的操作：

What i tried is given below:

以下sql查询给出了相似的结果。

The following sql query gives similar result.

select distinct post_id, created_at  
from comments 
order by created_at DESC limit 5

@temp = Comment.select('distinct(comments.post_id),created_at').order('created_at DESC').limit(5)

我尝试将这个派生的@temp表与posts表连接起来以获取posts.title

I tried to join this derived @temp table with posts table to get the posts.title

我尝试了此操作但失败了。

I tried this but failed.

@temp2 = @temp.joins('posts')

所以，如何将发布表与派生@temp表连接起来？

So, How can i join posts table with derived @temp table ?

                                   Table "public.posts"
   Column    |          Type          |                     Modifiers                      
-------------+------------------------+----------------------------------------------------
 id          | integer                | not null default nextval('posts_id_seq'::regclass)
 title       | character varying(100) | not null
 content     | character varying(500) | not null
 created_at  | date                   | 
 updated_at  | date                   | 
 tags        | character varying(55)  | not null default '50'::character varying
 category_id | integer                | not null default 1
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)

评论

                                   Table "public.comments"
   Column   |          Type          |                       Modifiers                       
------------+------------------------+-------------------------------------------------------
 id         | integer                | not null default nextval('comments_id_seq'::regclass)
 post_id    | integer                | not null
 name       | character varying(255) | not null
 email      | character varying(255) | not null
 content    | character varying(500) | not null
 created_at | date                   | 
 updated_at | date                   | 
Indexes:
    "comments_pkey" PRIMARY KEY, btree (id)

帖子模型， has_many：评论，评论模型，属于：post

posts model, has_many :comments, comments model, belongs_to :post

推荐答案

在转入SQL之前，问题作者需要阅读基本的Rails和activerecord用法。需要了解Activerecord如何为您的数据建模以及如何使用它。首先找出要用通用语言进行的操作，然后查看如何使用现有的语言进行操作。

Question author needs to read up on basic Rails and activerecord usage before jumping into SQL. Need to understand how Activerecord models your data and how to use it. First figure out what you want to do in common language and then see how you can use what exists to do it.

Rails不知道@temp表的结构。它只有一个结果集，据我了解，AREL不会根据结果集构建逻辑。

Rails does not know the structure of your @temp table. It only has a result set and from what I understand, AREL does not build logic from the result set. It builds from schemas which it pulls for active record models.

您不能从此数据构建视图，因此唯一的选择是对activerecord类使用标准的联接选项。

You cannot build a view from this data, so your only option is to use the standard join options with activerecord classes or to do custom sql.

在Rails 3中，ActiveRecord关系代数非常先进，并且使查询非常容易。

In Rails 3, the ActiveRecord relational algebra is very advanced and has made queries very easy.

Comment.order（＃{Comment.table_name} .created_at desc'）。limit（5）.joins（：posts）.order（＃{Post.table_name} created_at desc ）

这篇关于如何加入派生表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！