I have been experiencing great difficultly in constructing a query using
the two defined models as seen below. I was lucky enough to have access to
a nice DBA who provided me the SQL form of the query I want which follows
at the bottom of this post. I also included the migration steps for
good measure. My scenario is normalized to albums and tags which seems to
be the norm. What would the query look like in Ruby to select based on the
following criteria:
attr = {
:style => ['rap','country'],
:country => ['us','uk']
}
tags = {
:name => ['red','blue']
}
So from the above hashes I want to retrieve the titles of albums that have
one of the matching styles and countries in addition to one of the matching
tags. So attr applies to Album models and tags applies to Tag models.
Here is the migration steps.
Sequel.migration do
change do
create_table(:albums) do
primary_key :id
String :title
end
create_table(:tags) do
primary_key :id
String :name
end
create_table(:albumstags) do
primary_key :id
foreign_key :albumid, :albums
foreign_key :tagid, :tags
end
end
end
Here is the model definitions.
class Album < Sequel::Model
many_to_many :tags,
:left_key => :albumid,
:right_key => :tagid,
:join_table => :albumstags
end
class Tag < Sequel::Model
many_to_many :devices,
:left_key => :tagid,
:right_key => :albumid,
:join_table => :albumstags
end
This is the SQL our DBA said would do what I need. How do I convert that
into Sequel code?
SELECT D.fqdn
FROM albums AS A
LEFT JOIN albumstags AS AT ON A.id = AT.albumid
LEFT JOIN tags AS T ON AT.tagid = T.id
WHERE D.country IN ('us', 'uk')
AND A.style IN ('rap', 'country')
AND T.name IN ('red', 'blue');
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/groups/opt_out.
