List Info

Thread: Complex query with joins
Complex query with joins
country flaguser name
Germany		 2007-04-30 21:48:05 
Hi everyone,

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

Objects
-------------
|ID
|Name
-------------

Attributes
-------------
|ID
bject_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

-- 
Posted via http://www.ruby-forum.com
/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Deploying Rails" group.
To post to this group, send email to
rubyonrails-deploymentgooglegroups.com
To unsubscribe from this group, send email to
rubyonrails-deployment-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/rubyonrails-deployment
?hl=en
-~----------~----~----~----~------~----~------~--~---
Re: Complex query with joins
country flaguser name
Germany		 2007-04-30 21:55:06 
Sorry, this is posted in the wrong section.


-- Please delete --

-- 
Posted via http://www.ruby-forum.com
/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Deploying Rails" group.
To post to this group, send email to
rubyonrails-deploymentgooglegroups.com
To unsubscribe from this group, send email to
rubyonrails-deployment-unsubscribegooglegroups.com
For more options, visit this group at http://groups.google.com/group/rubyonrails-deployment
?hl=en
-~----------~----~----~----~------~----~------~--~---
Re: Complex query with joins
user name
 2007-05-08 16:11:05 

  


  

    On 4/30/07, Dustin < ruby-forum-incomingandreas-s.net">ruby-forum-incomingandreas-s.net> wrote:

I want to be able to search for Objects by (potentially many)
combinations of Attribute values.&nbsp; 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?



How about something like?

class Objects
&nbsp; def self.all_attrs_cond_sql(attrs)
&nbsp;   attrs.map { <<-SQL }.join(9; and ')
&nbsp; &nbsp; &nbsp;   exists (
 &nbsp; &nbsp;   ; &nbsp; select null from attributes
 &nbsp; &nbsp; &nbsp; &nbsp;  where 
attributes.object_id = objects.id
 &nbsp; &nbsp; &nbsp;   ; and attributes.val = ? )
 &nbsp; &nbsp;  SQL
  end

 ; def self.find_with_all_attributes(attrs=[],*more_args)
&nbsp; &nbsp; find(:all, :conditions => [ all_attrs_cond_sql(attrs), *attrs ], *more_args)

  end
end

Use eg:
  found_objects = Objects.find_with_all_attributes(['green',';colorless&#39;], :limit => 100)



--~--~---------~--~----~------------~-------~--~----~

You received this message because you are subscribed to the Google Groups "Deploying Rails"; group. 
 To post to this group, send email to rubyonrails-deploymentgooglegroups.com 
 To unsubscribe from this group, send email to rubyonrails-deployment-unsubscribegooglegroups.com 
 For more options, visit this group at http://groups.google.com/group/rubyonrails-deployment?hl=en 
 -~----------~----~----~----~------~----~------~--~---




  

  
  
   
     
    






 [1-3]











   
 





about | contact  Other archives ( Real Estate discussion Medical topics )