# Complex query with joins

**URL:** <https://www.ruby-forum.com/t/complex-query-with-joins/103043>\
**Category:** Deployment\
**Created:** [May 1, 2007, 4:48am UTC](https://www.ruby-forum.com/t/complex-query-with-joins/103043 "2007-05-01T04:48:05Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dustin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5daacb/32.png) [@dustin](https://www.ruby-forum.com/u/dustin)\
**Post date:** [May 1, 2007, 4:48am UTC](https://www.ruby-forum.com/t/complex-query-with-joins/103043/1 "2007-05-01T04:48:05Z")

</div>

Hi everyone,

My problem involves two tables, Objects and Attributes. (These are not  
the real names)

## Objects

|ID

| Name |
| --- |

## Attributes

|ID  
|Object\_ID (foreign key references Objects.id)

| Value |
| --- |

I want to be able to search for Objects by (potentially many)  
combinations of Attribute values. To show all of the unique Objects  
that have Attributes ‘green’ AND ‘colorless’, I am currently writing  
this mess of code:

SELECT DISTINCT objects.\*  
FROM attributes t1, attributes t2, objects  
WHERE t1.val = ‘green’  
AND t2.val = ‘colorless’  
AND t1.object\_id = t2.object\_id  
AND t1.object\_id = objects.id

Is there a way I can search make queries like (‘Show me all of the  
records that have ALL of these attributes’) and just feed it a list of  
attributes?

The technique I am using requires that I make a new table alias for each  
new attribute. Does rails have some built-in join mechanism to do  
queries like this?

Thanks in advance,  
Dustin

---

<div class="post-metadata">

**Author:** ![dustin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5daacb/32.png) [@dustin](https://www.ruby-forum.com/u/dustin)\
**Post date:** [May 1, 2007, 4:55am UTC](https://www.ruby-forum.com/t/complex-query-with-joins/103043/2 "2007-05-01T04:55:06Z")

</div>

Sorry, this is posted in the wrong section.

– Please delete –

---

<div class="post-metadata">

**Author:** ![dustin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5daacb/32.png) [@dustin](https://www.ruby-forum.com/u/dustin)\
**Post date:** [May 8, 2007, 11:28pm UTC](https://www.ruby-forum.com/t/complex-query-with-joins/103043/3 "2007-05-08T23:28:06Z")

</div>

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

> AND t1.object\_id = t2.object\_id  
> AND t1.object\_id = objects.id
> 
> Is there a way I can search make queries like (‘Show me all of the  
> records that have ALL of these attributes’) and just feed it a list of  
> attributes?

How about something like?

class Objects  
def self.all\_attrs\_cond\_sql(attrs)  
attrs.map { \<\<-SQL }.join(’ and ')  
exists (  
select null from attributes  
where attributes.object\_id = objects.id  
and attributes.val = ? )  
SQL  
end

def self.find\_with\_all\_attributes(attrs=[],\*more\_args)  
find(:all, :conditions =\> [all\_attrs\_cond\_sql(attrs), \*attrs],  
\*more\_args)  
end  
end

Use eg:  
found\_objects =  
Objects.find\_with\_all\_attributes([‘green’,‘colorless’],  
:limit =\> 100)