How to query many_to_many associations with Sequel gem and Postgres

I'm using the Sequel gem to connect to my Postgres database - I've got Playlists and Tracks, which have a many_to_many relationship.

I'm having a hard time wrapping my head around the most effective way to query the top 10 most frequent tracks seen on playlists. Can someone give me an example of how to pull this off?

Solution

Probably best to use a model for the join table:

class PlaylistTrack < Sequel::Model(:playlists_tracks)
  many_to_one :playlist
  many_to_one :track

  def self.top10_tracks
    group_and_count(:track_id).
      reverse(:count).
      limit(10).
      eager(:track).
      all.
      map(&:track)
  end
end

Note that this does two queries, but it makes sure the resulting array of tracks is ordered by popularity.

If you want to, you can do it in a single query without a join table model:

def Track.top10
  join(:playlists_tracks, :track_id=>:id).
    group{tracks[:id]}.
    select_all(:tracks).
    select_append{count(tracks[:id])}.
    order{count(tracks[:id]).desc}.
    limit(10).
    all
end

Note that this uses non-standard SQL, since it selects non-aggregate expressions you aren't grouping by, but it does work in PostgreSQL. You could have this work with standard SQL by grouping by all columns in the tracks table.