# Is this possible? select unique records from multiple joins

**URL:** <https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753>\
**Category:** Rails\
**Created:** [October 30, 2007, 5:33pm UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753 "2007-10-30T17:33:19Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![betame342](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/90db22/32.png) [@betame342](https://www.ruby-forum.com/u/betame342)\
**Post date:** [October 30, 2007, 5:33pm UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753/1 "2007-10-30T17:33:19Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![betame342](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/90db22/32.png) [@betame342](https://www.ruby-forum.com/u/betame342)\
**Post date:** [October 31, 2007, 1:38am UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753/2 "2007-10-31T01:38:55Z")

</div>

On 10/30/07, Beta B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> " 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/](http://talklikeaduck.denhaven2.com/)

---

<div class="post-metadata">

**Author:** ![betame342](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/90db22/32.png) [@betame342](https://www.ruby-forum.com/u/betame342)\
**Post date:** [October 31, 2007, 5:43am UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753/3 "2007-10-31T05:43:25Z")

</div>

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/](http://talklikeaduck.denhaven2.com/)

---

<div class="post-metadata">

**Author:** ![betame342](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/90db22/32.png) [@betame342](https://www.ruby-forum.com/u/betame342)\
**Post date:** [October 31, 2007, 1:03pm UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753/4 "2007-10-31T13:03:15Z")

</div>

On 10/31/07, Beta B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Article3  
> 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/](http://talklikeaduck.denhaven2.com/)

---

<div class="post-metadata">

**Author:** ![betame342](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/90db22/32.png) [@betame342](https://www.ruby-forum.com/u/betame342)\
**Post date:** [October 31, 2007, 4:29pm UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753/5 "2007-10-31T16:29:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![betame342](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/90db22/32.png) [@betame342](https://www.ruby-forum.com/u/betame342)\
**Post date:** [October 31, 2007, 5:00pm UTC](https://www.ruby-forum.com/t/is-this-possible-select-unique-records-from-multiple-joins/118753/6 "2007-10-31T17:00:08Z")

</div>

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