Complex query with joins

Rails Deployment
1

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

2

Sorry, this is posted in the wrong section.

– Please delete –

3

On 4/30/07, Dustin [email protected] 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)