Get first association from a has many through association

Question

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.

What array of playlist you want to join to each song? All playlists that contain this song? — Leonel Galán, Mar 08 '13 at 19:46
Are you trying to search for all the playlists with a given name and a given song OR do you want to just output an array of all the playlist_names next to their first song? — rocket scientist, Mar 08 '13 at 19:50
@rocketscientist the later. playlist_names next to their first song — mrabin, Mar 08 '13 at 20:08
What do you mean by "efficient"? Does your query work? How does the DB know which song is first on your playlist? — Jonathan Allard, Mar 08 '13 at 20:19
@JonathanAllard I was hoping for something that did a join as opposed to using a block. I want the first song association with that playlist. so if p was one of the playlists, p.songs.first would be the first song. The query I provided works, but it doesn't do exactly what I want. If the one of the playlists has multiple songs, it returns a row for each song in the playlist. I want each row to have the a playlist name, and it's first song. — mrabin, Mar 08 '13 at 21:01

Harish Shetty · Accepted Answer · 2015-03-30T19:55:00.197

12

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

edited Mar 30 '15 at 19:55

answered Mar 14 '13 at 22:01

Harish Shetty

64,083
21
152
198

This is exactly what I was looking for, thank you. I've never done a bounty before. Do I accept now and give it to you or do people normally wait for the time to run out? – mrabin Mar 15 '13 at 00:28

rocket scientist · Answer 2 · 2013-03-12T00:30:57.470

2

What you are doing above with joins is what you would do if you wanted to find every playlist with a given name and a given song. In order to collect the playlist_name and first song_name from each playlist you can do this:

Playlist.includes(:songs).all.collect{|play_list| [playlist.name, playlist.songs.first.name]}

This will return an array in this form [[playlist_name, first song_name],[another_playlist_name, first_song_name]]

edited Mar 12 '13 at 00:30

answered Mar 08 '13 at 20:21

rocket scientist

2,427
1
19
28

This works, but I was expecting there to be some way to do this through a join. – mrabin Mar 08 '13 at 21:22
Above would be how I would do it but you can also use [.zip()](http://www.ruby-doc.org/core-1.9.3/Array.html#method-i-zip) if both your arrays are the same length and in the correct order. The `.join()` method for an array returns a string created by converting each element of the array to a string, separated by whatever you pass it. The method [.joins](http://guides.rubyonrails.org/active_record_querying.html#joining-tables) the way you were using it, is a finder method for specifying JOIN clauses in SQL. – rocket scientist Mar 08 '13 at 21:40
I meant .joins not .join. Sorry for the confusion. – mrabin Mar 08 '13 at 21:46
This is going to cause an N+1 query... If you are displaying 100 playlists, this will essentially result in 101 sql queries. – Shane Andrade Mar 12 '13 at 00:10
@ShaneAndrade, no, it wouldn't: Playlist.includes(:songs).all.collect{|playlist| [playlist.name, playlist.songs.first.name]} Playlist Load (0.3ms) SELECT `playlists`.* FROM `playlists` PlaylistSong Load (0.4ms) SELECT `playlist_songs`.* FROM `playlist_songs` WHERE `playlist_songs`.`playlist_id` IN (1, 2, 3) Song Load (0.2ms) SELECT `songs`.* FROM `songs` WHERE `songs`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9) But it gets all playlists and all songs. And there could be problem for example with Oracle as it accepts not more than 1000 elements inside IN braces. – Yuri Golobokov Mar 12 '13 at 07:37
Sorry, I didn't see the `includes` but you're still selecting songs you don't need, not just the first one. – Shane Andrade Mar 12 '13 at 16:41
See my answer on a possible solution that selects only the first song. – Shane Andrade Mar 12 '13 at 16:43

score 2 · Answer 3 · answered Mar 12 '13 at 00:26

2

I think this problem would be improved by having a a stricter definition of "first". I'd suggest adding a position field on the PlaylistSong model. At which point you can then simply do:

Playlist.joins(:playlist_song).joins(:song).where(:position => 1)

answered Mar 12 '13 at 00:26

Dave S.

6,349
31
33

I would rather not store the position in the PlaylistSong table. I'm defining "first song" as the song with the lowest id – mrabin Mar 12 '13 at 17:27
Surely your users would like the ability to reorder their playlists. Then you need to track the user-defined position. :) – Dave S. Mar 12 '13 at 17:30
You're right, in the future that may be true, but at the moment playlists and songs are stored as is and order does not change. I need a solution that will work with what we've got now. – mrabin Mar 12 '13 at 17:39

score 0 · Answer 4 · answered Mar 12 '13 at 00:20

I think the best way to do this is to use an inner query to get the first item and then join on it.

Untested but this is the basic idea:

# gnerate the sql query that selects the first item per playlist
inner_query = Song.group('playlist_id').select('MIN(id) as id, playlist_id').to_sql

@playlists = Playlist
              .joins("INNER JOIN (#{inner_query}) as first_songs ON first_songs.playlist_id = playlist.id")
              .joins("INNER JOIN songs on songs.id = first_songs.id")

Then rejoin back to the songs table since we need the song name. I'm not sure if rails is smart enough to select the song fields on the last join. If not you might need to include a select at the end that selects playlists.*, songs.* or something.

Yuri Golobokov · Answer 5 · 2013-03-12T08:13:57.820

Try:

PlaylistSong.includes(:song, :playlist).
find(PlaylistSong.group("playlist_id").pluck(:id)).each do |ps|
  puts "Playlist: #{ps.playlist.name}, Song: #{ps.song.name}"
end

(0.3ms)  SELECT id FROM `playlist_songs` GROUP BY playlist_id
PlaylistSong Load (0.2ms)  SELECT `playlist_songs`.* FROM `playlist_songs` WHERE `playlist_songs`.`id` IN (1, 4, 7)
Song Load (0.2ms)  SELECT `songs`.* FROM `songs` WHERE `songs`.`id` IN (1, 4, 7)
Playlist Load (0.2ms)  SELECT `playlists`.* FROM `playlists` WHERE `playlists`.`id` IN (1, 2, 3)

Playlist: Dubstep, Song: Dubstep song 1
Playlist: Top Rated, Song: Top Rated song 1
Playlist: Last Played, Song: Last Played song 1

This solution has some benefits:

Limited to 4 select statements
Does not load all playlist_songs - aggregating on db side
Does not load all songs - filtering by id's on db side

Tested with MySQL.

This will not show empty playlists. And there could be problems with some DBs when playlists count > 1000

m_x · Answer 6 · 2013-03-12T08:39:10.177

just fetch the song from the other side :

Song
  .joins( :playlist )
  .where( playlists: {id: [1,2,3]} )
  .first

however, as @Dave S. suggested, "first" song in a playlist is random unless you explicitly specify an order (positioncolumn, or anything else) because SQL does not warrant the order in which the records are returned, unless you explicitly ask it.

EDIT

Sorry, I misread your question. I think that indeed a position column is necessary.

Song
  .joins( :playlist )
  .where( playlists: {id: [1,2,3]}, songs: {position: 1} )

If you do not want any position column at all, you can always try to group the songs by playlist id, but you'll have to select("songs.*, playlist_songs.*"), and the "first" song is still random. Another option is to use the RANK window function, but it is not supported by all RDBMS (for all i know, postgres and sql server do).

jvnill · Answer 7 · 2013-03-12T09:00:08.357

0

you can create a has_one association which, in effect, will call the first song that is associated to the playlist

class PlayList < ActiveRecord::Base
  has_one :playlist_cover, class_name: 'Song', foreign_key: :playlist_id
end

Then just use this association.

Playlist.joins(:playlist_cover)

UPDATE: didn't see the join table.

you can use a :through option for has_one if you have a join table

class PlayList < ActiveRecord::Base
  has_one :playlist_song_cover
  has_one :playlist_cover, through: :playlist_song_cover, source: :song
end

edited Mar 12 '13 at 09:00

answered Mar 12 '13 at 08:24

jvnill

29,479
4
83
86

This won't work as there is no playlist_id field in Song model – Yuri Golobokov Mar 12 '13 at 08:53
sorry. my eyes totally skipped that join table. you can still use `has_one` though since it has a `through` option like in my updated answer. – jvnill Mar 12 '13 at 09:00
I like the direction on this, but I get the following error: ":through association 'Playlist#playlist_cover' were the :through association 'Playlist#playlist_songs' is a collection", which makes sense. Or where you suggesting to make a new table called playlist_song_cover? – mrabin Mar 12 '13 at 17:19
try having the through also pass from a has_one association. it actually makes sense to do this rather than have a has_one association through a collection. – jvnill Mar 12 '13 at 23:48

rbinsztock · Answer 8 · 2013-03-13T11:55:04.480

Playlyst.joins(:playlist_songs).group('playlists.name').minimum('songs.name').to_a

hope it works :)

got this :

Product.includes(:vendors).group('products.id').collect{|product| [product.title, product.vendors.first.name]}
  Product Load (0.5ms)  SELECT "products".* FROM "products" GROUP BY products.id
  Brand Load (0.5ms)  SELECT "brands".* FROM "brands" WHERE "brands"."product_id" IN (1, 2, 3)
  Vendor Load (0.4ms)  SELECT "vendors".* FROM "vendors" WHERE "vendors"."id" IN (2, 3, 1, 4)
 => [["Computer", "Dell"], ["Smartphone", "Apple"], ["Screen", "Apple"]] 
2.0.0p0 :120 > Product.joins(:vendors).group('products.title').minimum('vendors.name').to_a
   (0.6ms)  SELECT MIN(vendors.name) AS minimum_vendors_name, products.title AS products_title FROM "products" INNER JOIN "brands" ON "brands"."product_id" = "products"."id" INNER JOIN "vendors" ON "vendors"."id" = "brands"."vendor_id" GROUP BY products.title
 => [["Computer", "Dell"], ["Screen", "Apple"], ["Smartphone", "Apple"]]

Gui LeFlea · Answer 9 · 2013-03-13T20:38:55.537

You could add activerecord scope to your models to optimize how the sql queries work for you in the context of the app. Also, scopes are composable, thus make it easier to obtain what you're looking for.

For example, in your Song model, you may want a first_song scope

class Song < ActiveRecord::Base
  scope :first_song, order("id asc").limit(1)
end

And then you can do something like this

  playlists.songs.first_song

Note, you may also need to add some scopes to your PlaylistSongs association model, or to your Playlist model.

score 0 · Answer 10 · answered Mar 15 '13 at 00:02

You didn't say if you had timestamps in your database. If you do though, and your records on the join table PlaylistSongs are created when you add a song to a playlist, I think this may work:

first_song_ids = Playlist.joins(:playlist_songs).order('playlist_songs.created_at ASC').pluck(:song_id).uniq
playlist_ids = Playlist.joins(:playlist_songs).order('playlist_songs.created_at ASC').pluck(:playlist_id).uniq
playlist_names = Playlist.where(id: playlist_ids).pluck(:playlist_name)
song_names = Song.where(id: first_song_ids).pluck(:song_name)

I believe playlist_names and song_names are now mapped by their index in this way. As in: playlist_names[0] first song name is song_names[0], and playlist_names[1] first song name is song_names[1] and so on. I'm sure you could combine them in a hash or an array very easily with built in ruby methods.

I realize you were looking for an efficient way to do this, and you said in the comments you didn't want to use a block, and I am unsure if by efficient you meant an all-in-one query. I am just getting used to combining all these rails query methods and perhaps looking at what I have here, you can modify things to your needs and make them more efficient or condensed.

Hope this helps.

Get first association from a has many through association

10 Answers10