Album Rating

Download and get help for different MediaMonkey for Windows 4 Addons.

Moderators: Peke, Gurus

Risser

Album Rating

Post by Risser »

This may be old hat to people, but I see that folks are putting "album rating" in a custom field. It struck me that really, your album should be rated by averaging all the tunes on it and maybe I should write a script to do that.

But, of course, I found that I could do just such a thing with Magic Nodes.
This might be old hat to people, but here it is:

Code: Select all

Album Rating|icon:top level\<Album|sort by:avg(rating)|sort order:desc>
That's pretty neat.
It'd be cool if I could somehow weight it by time (that is, a 4-star 20 minute song is weighted higher than a 4-star one minute song), and if I could round to 1/2s. (average * 2, round to integer, divide by 2)

Maybe someday I'll get around to it. In the meantime, this gives a nice view about what I *really* think about an album.

Peter
sadao
Posts: 191
Joined: Tue Nov 09, 2004 7:44 pm
Location: Arlington, VA

Post by sadao »

Correct me if I'm wrong, but this doesn't actually show the album rating, right? Perhaps an addition that would be great is to have more flexibility in the node structure mask:

Code: Select all

<display item|group field| other qualifiers>
this would transalte to:

Code: Select all

Album Rating|icon:top level\<concatenate(Album," ", avg(rating))|Album|sort by:avg(rating)|sort order:desc>
Just a thought.
Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Post by Pablo »

Sadao, may be I don't understand your idea but it seems to me that Risser's mask does exactly what you are proposing. It does display the album rating to the left of the album (just like using sort by:max(year) displays the year).

A different functionality would be to use the album rating as a field in itself (i.e. display only the album rating - without the corresponding album). Or, more generally, to allow aggregate functions to be used as fields. That's something I'm considering for future verstions although I'm not sure what is the best interface for it.
Last edited by Pablo on Thu Feb 24, 2005 7:06 pm, edited 1 time in total.
Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Re: Album Rating

Post by Pablo »

Risser wrote:It'd be cool if I could somehow weight it by time (that is, a 4-star 20 minute song is weighted higher than a 4-star one minute song), and if I could round to 1/2s. (average * 2, round to integer, divide by 2)
Peter, it's not difficult to modify the code to get either of these behaviors (or both). If you're interested and know some vbscript, or vb, let me know and I'll tell you where to look.
sadao
Posts: 191
Joined: Tue Nov 09, 2004 7:44 pm
Location: Arlington, VA

Post by sadao »

Pablo wrote:Sadao, may be I don't understand your idea but it seems to me that Risser's mask does exactly what you are proposing. It does display the album rating to the left of the album (just like using sort by:max(year) displays the year).
Heh, I assumed that the magic node described would not display the rating...
risser

Re: Album Rating

Post by risser »

Pablo wrote: Peter, it's not difficult to modify the code to get either of these behaviors (or both). If you're interested and know some vbscript, or vb, let me know and I'll tell you where to look.
Okay, I've hacked your script into the bare minimum.
It now creates a root node called Album Rating and fills the level below that with Albums, displaying them like so:
- [4.00] Yes: Fragile
- [3.00] Brian Wilson: SMiLE
- [2.00] Art Mooney: Cha Cha Cha
etc.

So, I've been able to include the Album Artist, and I am able to exclude albums that have absolutely no rating info on any songs.

What I'd like to be able to do, that I can't is:
- Determine how many songs in an album are "unrated". Then I could weight the average based on a pre-determined number, or exclude them all together by doing the average (SUMs and COUNTs) myself. I tried to use DCOUNT, but couldn't get it to work... This is pretty new stuff to me.
- Weight the average by the time of the song. I can't think of how to do this without actually opening a new SQL Query for each album and going through each song individually...)

Any suggestions, anyone?

Thanks!
Peter
Guest

Post by Guest »

I asked about and then worked on an album rating category in magic nodes. It has been recommended that an album rating be just an average of title ratings, however I had something different in mind. To me album rating speaks of how the album fits together as a whole and carries a theme; also album art; then the quality of the production as a whole. A good example I think is Moby's 18, which has an excellent theme and is best enjoyed if listened to as a whole instead of basing our opinion of it on individual tracks.

Anyway, that's my view for what its worth. :wink:
pah68
Posts: 1504
Joined: Wed Apr 07, 2004 5:26 pm
Location: Sydney, Australia

Post by pah68 »

Anonymous wrote:I asked about and then worked on an album rating category in magic nodes. It has been recommended that an album rating be just an average of title ratings, however I had something different in mind. To me album rating speaks of how the album fits together as a whole and carries a theme; also album art; then the quality of the production as a whole. A good example I think is Moby's 18, which has an excellent theme and is best enjoyed if listened to as a whole instead of basing our opinion of it on individual tracks.

Anyway, that's my view for what its worth. :wink:
For me it's totally unrelated to anything other than a preference for the album as a whole. I have several albums where I quite like the tracks but there is nothing that I'm going to go looking for on a regular basis, however the album is rated quite highly.

e.g. would be some of my mood albums. The individual tracks are nothing special (4 star) but as a whole album, played as intended I might give it 4.5 or 5




~EDIT~
I just re-read your post and apparently I agree with you. :)
Guest

Post by Guest »

Yes, I know what you mean.

BTW I am the user Abrazor, but for some reason I can't login to the forum. After I go to log in and put in my details and click log in it just sends me back to the log in page. I am using Opera and not used to its interface yet, so maybe its something in my settings. Dont know.

Abrazor (Abe)
Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Re: Album Rating

Post by Pablo »

risser wrote:What I'd like to be able to do, that I can't is:
- Determine how many songs in an album are "unrated". Then I could weight the average based on a pre-determined number, or exclude them all together by doing the average (SUMs and COUNTs) myself. I tried to use DCOUNT, but couldn't get it to work... This is pretty new stuff to me.
- Weight the average by the time of the song. I can't think of how to do this without actually opening a new SQL Query for each album and going through each song individually...)

Any suggestions, anyone?

Thanks!
Peter
I'm not sure this specifically addresses your questions, but it may give you some hints. The following SQL query computes a rating average weighted by song length, ignoring songs with no rating assigned (albums for which no song is rated are skipped):

Code: Select all

SELECT Albums.Id, SUM(Songs.SongLength * Songs.Rating/20)/SUM(Songs.SongLength) as WeightedRatingAvg 
FROM Songs, Albums
WHERE Songs.IdAlbum = Albums.Id AND  Songs.Rating >= 0
GROUP BY Albums.Id
The following does the same but ignores albums for which any track is unrated:

Code: Select all

SELECT Albums.Id, SUM(Songs.SongLength * Songs.Rating/20)/SUM(Songs.SongLength) as WeightedRatingAvg 
FROM Songs, Albums
WHERE Songs.IdAlbum = Albums.Id 
GROUP BY Albums.Id
HAVING Min(Songs.Rating)>=0
The following does the same but it only considers albums for which at least 50% of the tracks are rated:

Code: Select all

SELECT Albums.Id, SUM(IIF(Songs.Rating>=0,Songs.SongLength * Songs.Rating/20,0))/SUM(IIF(Songs.Rating>=0,Songs.SongLength,0)) as WeightedRatingAvg 
FROM Songs, Albums
WHERE Songs.IdAlbum = Albums.Id 
GROUP BY Albums.Id
HAVING Sum(IIF(Songs.Rating>=0,1,0))/Count(*) >= 0.5
Risser

Re: Album Rating

Post by Risser »

Pablo wrote: I'm not sure this specifically addresses your questions, but it may give you some hints.
Ooh, that's plenty to go on.
Thanks!
Peter
Lowlander
Posts: 56491
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Post by Lowlander »

Well the discussions about album rating have been about that difference.

Some users would like to see an album rating that is independent from the track rating. This would require a seperate field that would allow rating albums. Problems here would be ID3 standards (not sure if it allows for this field). If ID3 doesn't use this field it would need to be added as a DB only thing. This means it isn't transferable.

Some users would like to see it as an average of the track rating. This one is easy as it would be a computational value, no need to store the info in the tags.
Risser

Post by Risser »

Here's what I came up with. Shamelessly cribbed from Pablo's Magic Nodes script.

Code: Select all


' AlbumRating Script
' Version 1.0
' Inspired by and Shameless ripped off from Pablo's Magic Nodes script
' This script creates a root node in the tree, under the library node, called "Album Rating"
' (or whatever you change that too).  Beneath this node, it lists all albums that contain rated
' songs, based on the options chosen below.  Albums with no rated songs at all are left out.
'
' This script can be freely used and modified.
' This is an early release and there may be bugs. If it's causing you problems, simply delete 
' it or move it out of the scripts\auto folder.
'
' The script does not modify the database, registry or INI file.

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'	   Global Variables and Declarations
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Option Explicit

' %%% If Unrated songs are to be included in the rating, they should receive this value as a default
Const UnratedValue = 3

' %%% If this is true, ignore any unrated songs when calculating the average.
'     If false, use the 'unrated value' above.
Const ignoreUnrated = True

' %%% Only display albums that have all the tracks.  This compares the highest #'d track to the total 
'     number of tracks.  If they are even, it assumes the album is complete.  However, if you only have
'     track #1 of an album, or are missing the last track(s), it will still count the album as complete.
'     Oh well.
Const onlyWholeAlbums = False

' %%% This weights the ratings by the length of the song.  So, if you have a 30 minute 4-star song and a
'     one minute one-star song, the 4 star song is weighted 30 times as strongly.  I think this reflects
'     a more true view of the quality of the album.
Const weightByTime = True

' %%% If true, it rounds the rating and turns it into stars: ***½.
'     If false, it just displays the average rating, rounded to 2 places.
Const showStars = False

' %%% The caption for the root node.
Const RootNodeCaption = "Album Rating"

' %%% The display format of the album node.  The artist is actually "Album Artist", so 'various' will display, etc.
Function formatCaption(score,artist,album)
	formatCaption = "[" & score & "]  " & artist & ": " & album
End Function




'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'		The Meat.  Don't change anything under here.
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'A procedure that does nothing
Sub DummyFunct(Argument)
End Sub

Function starString(score)
	score = FormatNumber(score * 2, 0)
	If score = "0" Then
		starString = "--"
	ElseIf score = "1" Then
		starString = "½"
	ElseIf score = "2" Then
		starString = "*"
	ElseIf score = "3" Then
		starString = "*½"
	ElseIf score = "4" Then
		starString = "**"
	ElseIf score = "5" Then
		starString = "**½"
	ElseIf score = "6" Then
		starString = "***"
	ElseIf score = "7" Then
		starString = "***½"
	ElseIf score = "8" Then
		starString = "****"
	ElseIf score = "9" Then
		starString = "****½"
	ElseIf score = "10" Then
		starString = "*****"
	End If
End Function


'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'		Creation of Node Hierarchy
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


Sub FillStandardProperties(parentNode, childNode)
	With childNode
   		.CustomNodeId = parentNode.CustomNodeId
   		.CustomDataId = parentNode.CustomDataId + 1
   		.UseScript = Script.ScriptPath
   	End With
End Sub

Sub FillCustomNode(Node)

   Dim SQLLinking			' Piece of SQL Code (WHERE statement) defining the relations between tables
   Dim Tree, newNode, nextIsLeaf
   Dim scoreField 	
   Dim idIndex
   Dim albumIndex
   Dim artistIndex
   Dim scoreIndex
   Dim albumName, artistName, avgScore, formattedScore
   Dim SELECT_Clause, FROM_Clause, WHERE_Clause, GROUP_BY_Clause, ORDER_BY_Clause, HAVING_Clause
   Dim SQLStatement	  		' SQL query to the database
   Dim EscapedId	  		' Used to escape a text value in the database to be used as a test
   Dim Iter		  			' SDBD Iterator obtained by running the SQL query to get the nodes
   Dim SQLKnown

   
   SQLLinking = " Songs.IdAlbum = Albums.ID and Artists.Id = Albums.IdArtist "
   	SQLKnown = "Songs.IdAlbum > 0 "
   nextIsLeaf = True

   	idIndex = 0

   	albumIndex = 1
   	artistIndex = 2
   	scoreIndex = 3
   
	If weightByTime Then
		scoreField = "SUM(Songs.SongLength * iif(Songs.Rating > -1,Songs.Rating/20,"& UnratedValue &"))/SUM(Songs.SongLength)"
	Else
		scoreField = "sum(iif(Songs.Rating > -1,Songs.Rating/20,"& UnratedValue &"))/count(*)"
	End If


   Set Tree = SDB.MainTree
   Node.HasChildren = false   

   
   SELECT_Clause = " SELECT DISTINCT Songs.IdAlbum, Albums.Album, Artists.Artist, " & scoreField
   FROM_Clause = " FROM Songs, Albums, Artists "
   WHERE_Clause = "WHERE " & SQLLinking & " AND " & SQLKnown
	If ignoreUnrated = True Then
		WHERE_Clause = WHERE_Clause & " AND Songs.Rating >= 0"
	End If

   GROUP_BY_Clause = " GROUP BY Songs.IdAlbum, Albums.Album, Artists.Artist"
   HAVING_Clause = " HAVING avg(Songs.Rating) > -1 " 
	If onlyWholeAlbums = True Then
		HAVING_Clause = HAVING_Clause & " AND (MAX(Songs.SongOrder)+1) <= Count(*)"
	End If
   
   	ORDER_BY_Clause = " ORDER BY " & scoreField & " DESC, Albums.Album, Artists.Artist"
   			
   SQLStatement = SELECT_Clause & FROM_Clause & WHERE_Clause & GROUP_BY_Clause & HAVING_Clause & ORDER_BY_Clause

   
   ' Fill regular subnodes  
   Set Iter = SDB.Database.OpenSQL(SQLStatement)   
   While Not Iter.EOF
   	
   	Set NewNode = Tree.CreateNode
   	NewNode.iconIndex = 16
   	
	albumName = Iter.StringByIndex(albumIndex)
	artistName = Iter.StringByIndex(artistIndex)
	avgScore = Iter.StringByIndex(scoreIndex)
	
	If showStars Then
		formattedScore = starString(avgScore)
	Else
		formattedScore = FormatNumber(avgScore, 2)
	End If
	NewNode.Caption = formatCaption(formattedScore, artistName, albumName)

   	escapedId = Iter.StringByIndex(0)
   	newNode.CustomData = SQLKnown & "AND Songs.IdAlbum=" & escapedId & "@@" & SQLLinking & "@@Songs, Albums, Artists"
   	
   	
   	FillStandardProperties node,newNode 
	newNode.onFillTracksFunct = "FillCustomLeaf"
   
   	Tree.AddNode Node, NewNode, 3

	newNode.hasChildren = Not nextIsLeaf	
   	
   	Iter.Next
   Wend

End Sub

Sub FillCustomLeaf(Node)

   Dim SplitCustomData, Tracks
   Dim SQLCondition, SQLLinking
   Dim SELECT_Clause, FROM_Clause, WHERE_Clause
   
   SplitCustomData = Split(Node.CustomData,"@@")

   ' condition includes album ID
   SQLCondition = SplitCustomData(0) 
   SQLLinking = " Songs.IdAlbum = Albums.ID and Artists.Id = Albums.IdArtist "
  
   SELECT_Clause = " SELECT Songs.Id "
   FROM_Clause = " FROM Songs, Albums, Artists "
   WHERE_Clause = " WHERE " & SQLLinking & " AND " & SQLCondition
  
   Set Tracks = SDB.MainTracksWindow
   
   Tracks.AddTracksFromQuery("AND Songs.ID IN (" & SELECT_Clause & FROM_Clause & WHERE_Clause & ")")
   Tracks.FinishAdding

End Sub



'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'					Startup Function
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Sub onStartUp
	Dim Tree, AlbumRatingNodeRoot
	Dim SQLFilter
   	Dim SQLLinking, SQLTables

	Set Tree = Sdb.MainTree
	Set AlbumRatingNodeRoot = Tree.createNode

	SQLFilter = ""
	SQLLinking = " Songs.IdAlbum = Albums.ID and Artists.Id = Albums.IdArtist "
	SQLTables =  " Songs, Albums, Artists "
	
	AlbumRatingNodeRoot.Caption = RootNodeCaption
	AlbumRatingNodeRoot.IconIndex = 33
	AlbumRatingNodeRoot.UseScript = Script.ScriptPath
	AlbumRatingNodeRoot.onFillChildren = "FillCustomNode"
	AlbumRatingNodeRoot.customData = SQLFilter & " @@" & SQLLinking & "@@" & SQLTables

	Tree.AddNode Tree.Node_Library, AlbumRatingNodeRoot, 1
	AlbumRatingNodeRoot.hasChildren = True
	SDB.Objects("AlbumRatingNodeRoot") = AlbumRatingNodeRoot 
End Sub
Thanks for all your help!
Peter
Spazz
Posts: 243
Joined: Tue Jan 11, 2005 9:49 pm

Post by Spazz »

Works like a charm. Only one thing with it though. It would be nice if you could have it only use albums with above X songs. For instance, I have alot of singles that happen to be song 1 so the whole album option doesn't catch them.

Lots of bands show up as 5 star when I only have one song and hate all the others. :-?
Risser
Posts: 184
Joined: Thu Mar 03, 2005 11:28 am

Post by Risser »

Spazz wrote:Works like a charm. Only one thing with it though. It would be nice if you could have it only use albums with above X songs. For instance, I have alot of singles that happen to be song 1 so the whole album option doesn't catch them.
I think if you add:

Code: Select all

AND count(*) >= ###
to the HAVING_CLAUSE, where ### is the minimum number of tracks you want.

I didn't try that, but I think it will work.
Peter[/code]
Post Reply