How can I join a view?

Question

The sql statement is as follows:

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; 

I want to get rails of 3 sql statements equivalent to the above.

What I tried is given below:

The following sql query gives a 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) 

I tried joining this @temp view with posts table to get posts.title

I tried this but could not.

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

So how can I join a posts table with a @temp view?

  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) 

comments

  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 :comments , comment model, belongs_to :post

Answer 1

The question author needs to read the basic Rails and activerecord utilities before upgrading to SQL. You need to understand how Activerecord models your data and how to use it. First find out what you want to do in a common language, and then see how you can use what exists for this.

Rails does not know the structure of your @temp table. It has only a set of results and, as I understand it, AREL does not create logic from a set of results. It is built from the schemas that it extracts for active record models.

You cannot create a view from this data, so your only option is to use the standard connection parameters with the activerecord classes or make custom sql.

In Rails 3, ActiveRecord relational algebra is very advanced and simplifies queries.

Comment.order("#{Comment.table_name}.created_at desc').limit(5).joins(:posts).order("#{Post.table_name} created_at desc")