Joining tables and query design advice

Hi everyone,

My problem involves two tables, Objects and Attributes:

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs