Rails - Oct 2007 - Is this possible? select unique records from multiple joins

This will return articles includings its multiple assets
@articles = Article.find_by_sql("select
articles.id,articles.title,articles.summary,articles.body," +
  "articles.created_at,articles.updated_at,articles.created_by," +
   "articles.static_title,articles.duplicate,articles.url_source," +
   "users.login, assets.id as asset_id,assets.filename" +
   " from articles" +
   " LEFT JOIN users ON users.id = articles.created_by" +
   " left join assets ON attachable_id =articles.id")


From the @articles, I would like to select distinct values from all
articles field.

I try below code, it returns duplicates articles values
@articles_unique = @articles.map{|i|
   {:id=>i.id,:title=>i.title,
   :summary=>i.summary,
   :body=>i.body,
   :created_at=>i.created_at,
   :updated_at=>i.updated_at,
   :created_by=>i.created_by,
   :static_title=>i.static_title.to_s,
   :duplicate=>i.duplicate.to_s,
   :url_source=>i.url_source.to_s
   }
   }.uniq

This below is working fine, however I want it to return not just one
field.
@articles_unique = @articles.map{|i| i.id}.uniq

Also, I also want to be able to use the return @articles_unique as
ActiveRecords.

Is this possible?
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 10/30/07, Beta Beta
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
>
> This will return articles includings its multiple assets
> @articles = Article.find_by_sql("select
> articles.id,articles.title,articles.summary,articles.body," +
>   "articles.created_at,articles.updated_at,articles.created_by,"
+
>   
"articles.static_title,articles.duplicate,articles.url_source," +
>    "users.login, assets.id as asset_id,assets.filename" +
>    " from articles" +
>    " LEFT JOIN users ON users.id = articles.created_by" +
>    " left join assets ON attachable_id =articles.id")
>
>
> From the @articles, I would like to select distinct values from all
> articles field.
Articles.find(:all, :include => [:users, :assets])

This will return all Articles (you don''t have a where clause) and will
eagerly load any associated users and assets.

The Articles wil be distinct values (i.e. only one object will be
returned for each primary key value)

Is that what you are trying to do?

-- 
Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Thanks Rick for trying to answer my question, I appreciated.

1 Article => n Assets (1 Article can have many Assets)
Here are the overview what I am trying to do
-----------------------------
Article1
  -Asset1 -Asset2 -Asset3
Article2
  -Asset4 -Asset5
Article3
  -Asset6 -Asset7 -Asset8 -Asset9
-----------------------------
For each article row, I want to print All Assets that associated with 
it. (1 Articles can have multiple Assets).
Basically, I try to prevent multiple call to database to get All Assets 
associated with each Article. (It will be ideal with one call I am able 
to process the result)

I used the left join in the above example to get all articles including 
its multiple Assets. Then select distinct value for Articles. Then for 
each distinct Articles, I will get all its Assets.

Hopefully, I explain it clearly. And thanks again for trying to help.
>> From the @articles, I would like to select distinct values from all
>> articles field.
> 
> Articles.find(:all, :include => [:users, :assets])
> 
> This will return all Articles (you don''t have a where clause) and
will
> eagerly load any associated users and assets.
> 
> The Articles wil be distinct values (i.e. only one object will be
> returned for each primary key value)
> 
> Is that what you are trying to do?
> 
> --
> Rick DeNatale
> 
> My blog on Ruby
> http://talklikeaduck.denhaven2.com/
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 10/31/07, Beta Beta
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> Thanks Rick for trying to answer my question, I appreciated.
>
> 1 Article => n Assets (1 Article can have many Assets)
> Here are the overview what I am trying to do
> -----------------------------
> Article1
>   -Asset1 -Asset2 -Asset3
> Article2
>   -Asset4 -Asset5
> Article3
>   -Asset6 -Asset7 -Asset8 -Asset9
> -----------------------------
> For each article row, I want to print All Assets that associated with
> it. (1 Articles can have multiple Assets).
> Basically, I try to prevent multiple call to database to get All Assets
> associated with each Article. (It will be ideal with one call I am able
> to process the result)
>
> I used the left join in the above example to get all articles including
> its multiple Assets. Then select distinct value for Articles. Then for
> each distinct Articles, I will get all its Assets.
>
> Hopefully, I explain it clearly. And thanks again for trying to help.
And that''s EXACTLY what the :includes option on find is for. Assuming
that Article and Asset each have a name method which produces what you
want to print:

Article.find(:all, :include => :assets).each do | article|
   puts article.name
  # The following will not perform an additional query since the
assets were preloaded
   puts article.assets.map {|asset| "- #{asset.name}"}.join("
")
end

Of course the name method is just an example, the point is that
:include will preload the assets so you can use them without a
separate sql query to get the assets for each Article.
-- 
Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Excellent, you are the man Rick DeNatale. After you write this loop
code, it makes sense now. Thanks, this is what I am looking for.
> 
> Article.find(:all, :include => :assets).each do | article|
>    puts article.name
>   # The following will not perform an additional query since the
> assets were preloaded
>    puts article.assets.map {|asset| "-
#{asset.name}"}.join(" ")
> end
> 
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Sorry for additional comment, you just save 2 days of my time finding 
the right solution. :)

> Excellent, you are the man Rick DeNatale. After you write this loop
> code, it makes sense now. Thanks, this is what I am looking for.
> 
>> 
>> Article.find(:all, :include => :assets).each do | article|
>>    puts article.name
>>   # The following will not perform an additional query since the
>> assets were preloaded
>>    puts article.assets.map {|asset| "-
#{asset.name}"}.join(" ")
>> end
>> 
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---