On Thursday, April 16, 2015 at 8:36:25 AM UTC-7, Andrew Burleson wrote:
>
> Sorry to ask what is probably a very basic SQL question...
>
> Let's say you have a two-level one to many association:
>
> Artist.one_to_many :albums
> Album.one_to_many :songs
>
> How could you select all songs that belong to the artist? And how could
> you order that query by number of songs?
>
All songs for an artist:
Song.where(:album_id=>artist.albums_dataset.select(:id))
or via an association:
Artist.many_to_many :songs, :join_table=>:albums, :right_key=>:id,
:right_primary_key=>:album_id
artist.songs
If you are selecting all songs, it doesn't make sense to order by the
number of songs. However, if you want to select albums for an artist and
order by the number of songs, you have a few approaches:
1) Join then count
artist.albums_dataset.
association_left_join(:songs).
group_and_count(*Album.columns.map{|c| Sequel.qualify(:albums, c)}).
reverse(:count)
2) Count then join:
artist.albums_dataset.
join(Song.group_and_count(:album_id), :album_id=>:id).
reverse(:count)
3) Subselect in select:
artist.albums_dataset.
select_append{Song.where(:album_id=>:albums__id).select{count{}.*}.as(:count)}.
reverse(:song_count)
You may want to test each and see which is fastest in your case.
And for artists with a minimum number of songs, here's one way:
artist.association_left_join(:albums=>:songs).
select_group(*Artist.columns.map{|c| Sequel.qualify(:artists, c)}).
having{count(:songs__id) > 20}
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/d/optout.
