Using .sum

Rails
1

I have three tables: artists, albums, songs. What I want is to find the
total duration of all songs in a given album.

Artist.first.albums.first.songs.collect{|x|x.duration}

Of course, the above generates an array containing the durations of all
songs in the first album of the first artist:

[“4.05”, “2.36”, “3.31”, “3.25”, “2.45”, “4.58”]

What is the method I can use to total these?

2

Pale H. wrote:

I have three tables: artists, albums, songs. What I want is to find the
total duration of all songs in a given album.

Artist.first.albums.first.songs.collect{|x|x.duration}

Of course, the above generates an array containing the durations of all
songs in the first album of the first artist:

[“4.05”, “2.36”, “3.31”, “3.25”, “2.45”, “4.58”]

What is the method I can use to total these?

You could use the class methods provided by ActiveRecord to do this,
which will use the database’s aggregate features. This should also
provide better performance.

total_duration = Song.sum(:duration, :conditions => [ ‘album_id = ?’,
album ])

SQL: SELECT sum(“songs”.duration) AS sum_duration FROM songs WHERE
(album_id = 1)

Personally, I would also add this as a method on Album for a nice clean
API:

my_song.duration
=> 4.05

my_album.duration
=> 20.00

class Album < ActiveRecord::Base
def duration
Song.sum(:duration, :conditions => [ ‘album_id = ?’, self.id ])
end
end

3

Sorry, I wasn’t thinking at all. I’ve changed the duration to a time
field now. I did have the field as a decimal with a precision of 5 and a
scale of 2 but clearly this is not the way to measure total time.

What would you suggest for totalling time fields?

4

Pale H. wrote:

Sorry, I wasn’t thinking at all. I’ve changed the duration to a time
field now. I did have the field as a decimal with a precision of 5 and a
scale of 2 but clearly this is not the way to measure total time.

Why not?

What would you suggest for totalling time fields?

I think I would suggest storing times in seconds in the DB and using
composed_of to convert. I think DB time types tend to be meant for time
stamps, not time intervals. Of couse, if your DB has a time interval
type, then use it!

Best,
–Â
Marnen Laibow-Koser
http://www.marnen.org
[email protected]Â

5

Marnen Laibow-Koser wrote:

Pale H. wrote:

Sorry, I wasn’t thinking at all. I’ve changed the duration to a time
field now. I did have the field as a decimal with a precision of 5 and a
scale of 2 but clearly this is not the way to measure total time.

Why not?

+1

Time fields are bad for storing time intervals.

What would you suggest for totalling time fields?

I think I would suggest storing times in seconds in the DB and using
composed_of to convert. I think DB time types tend to be meant for time
stamps, not time intervals. Of couse, if your DB has a time interval
type, then use it!

Or milliseconds if you need more precision. Consider how UNIX tracks
time, which is the number of milliseconds since January 1, 1970 stored
in a “long” data type (32 bit integer). You wont really need that much
for your case album durations aren’t going to be that long. Store the
duration as a simple integer and format it for your views.

If you try to use time fields you’ll run into insane issues with
timezone changes, daylight savings, etc. It just a bad idea.