My bad, I did not completely scrub the example of my original table/column
names. Your example definitely helped as I came up with this working query.
Also I did not know about the ___ notation as I had not noticed it in the
docs before. The use of %w is definitely cleaner here as well.
irb(main):086:0> ap
DB[:albums___a].left_join(:albumsstags___at,:albumid=>:id).left_join(:tags___t,:id=>:tagid).where(:country=>%w'us
uk',:style=>%w'rap country',:name=>%w[red blue]).select(:title).sql
"SELECT `title` FROM `albums` AS `a` LEFT JOIN `albumsstags` AS `at` ON
(`at`.`albumid` = `a`.`id`) LEFT JOIN `tags` AS `t` ON (`t`.`id` =
`at`.`tagid`) WHERE ((`country` IN ('us', 'uk')) AND (`style` IN ('rap',
'country')) AND (`name` IN ('red', 'blue')))"
Thank you, I learned much about sequel in getting through this:)
As another question, should this be done with models and if so what would
that look like?
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
>
> 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.
