On Monday, September 16, 2013 2:39:43 PM UTC-7, Jarrod Manzer wrote:
>
> 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
>
You should probably either change :devices to :albums, or add a
:class=>:Album
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');
>
That's not valid SQL, unless you switch D to A. You can get something like
it via:
DB[:albums___a].
left_join(:albumstags___at, :albumid=>:id).
left_join(:tags___t, :id=>:tagid).
where(:a__county=>%w'us uk', :a__style=>%w'rap country', :t__name=>%w'red
blue').
select_map(:a__fqdn)
Thanks,
Jeremy
--
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.
