Topic: Advanced query: Joining two tables and multiple selects

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

Two questions.

1. Is there a way to do this in SQL without making another table alias (e.g., t1 and t2) for every new conditional?
2. Does Rails facilitate anything like this by the :includes statement?

Thanks in advance,
Dustin

Last edited by dustin (2007-04-30 01:22:56)

Re: Advanced query: Joining two tables and multiple selects

hey dustin


you dont need any of what you have written there in Rails.


Create two models: Object and Attribute. Then, set the relations in each model:
# object model

class Object < ActiveRecord::Base
  has_many :attributes
end

# attribute model
class Attribute < ActiveRecord::Base
  belongs_to :object
end


Then you can query:
# in your controller

# first get your object
@object = Object.find(your_object_id)

# now find all attributes that are associated with this object
@attributes = @object.attributes.find_all(:conditions => "value IN ('green', 'colorless')")


You can shorten this even... However, you may have problems using "Object" and "Attribute" as your model/table names as it very likely is a reserved word in Rails!

Other than this... if you still want the plain SQL:

SELECT * 
FROM objects
LEFT OUTER JOIN attributes
ON attributes.object_id = objects.id
WHERE attributes.value IN ('green', 'colorless')

Hope I did not make a mistake :-)

Re: Advanced query: Joining two tables and multiple selects

Hi -- Thanks for your reply,

I couldn't get the rails code to work, but I believe the design is different.  I want to return ALL of the Objects that match based on a list of Attributes.

The SQL query has that idea, but the "WHERE attributes.value IN ('X','Y')" returns the Objects that have *either* X or Y, but not both.


(Yes, these names are not real, I tried to use them for simplification)

Re: Advanced query: Joining two tables and multiple selects

http://rafb.net/p/8fWQOF27.html

Re: Advanced query: Joining two tables and multiple selects

tricky problem

Guess I misunderstood you there. You'll ave to join attributes two times then... won't get around it I think. I have not tested it, but it should look as the following:

SELECT *
FROM objects as o
LEFT OUTER JOIN (attributes as a1, attributes as a2)
ON (a1.object_id = o.id AND a2.object_id = o.id)
WHERE a1.value = 'green'
AND a2.value = 'colorless'

Not relaly sure about this as I have not tested it, but thats basically your first query :-S