Get first association from a has many through association

associations, join, ruby, ruby-on-rails

Solution

Assuming you are on Postgresql

Playlist.
  select("DISTINCT ON(playlists.id) playlists.id, 
          songs.id song_id, 
          playlists.name, 
          songs.name first_song_name").
  joins(:songs).
  order("id, song_id").
  map do |pl|
    [pl.id, pl.name, pl.first_song_name]
  end

Problem

I'm trying to join the first song of each playlist to an array of playlists and am having a pretty tough time finding an efficient solution. I have the following models: ``` class Playlist < ActiveRecord::Base belongs_to :user has_many :playlist_songs has_many :songs, :through => :playlist_songs end class PlaylistSong < ActiveRecord::Base belongs_to :playlist belongs_to :song end class Song < ActiveRecord::Base has_many :playlist_songs has_many :playlists, :through => :playlist_songs end ``` I would like to get this: ``` playlist_name | song_name ---------------------------- chill | baby fun | bffs ``` I'm having a pretty tough time finding an efficient way to do this through a join. UPDATE **** Shane Andrade has lead me in the right direction, but I still can't get exactly what I want. This is as far as I've been able to get: ``` playlists = Playlist.where('id in (1,2,3)') playlists.joins(:playlist_songs) .group('playlists.id') .select('MIN(songs.id) as song_id, playlists.name as playlist_name') ``` This gives me: ``` playlist_name | song_id --------------------------- chill | 1 ``` This is close, but I need the first song(according to id)'s name.

Original source