Meridion wrote:
> I am now trying to use the "Database Query" plugin to create a more
> detailed CSV export. But I am no SQL expert at all...
>
> Is there a SQL expert, who can help me writing a SQL query which outputs
> track data (album title, artist, album artist, track title, track
> number, track path/file, trackstat information) to a CSV file? Maybe it
> would make sense to just export tracks wih trackstat data available.
>
Install the Database Query version I published a few minutes ago and
then setup a free form query using an SQL like this:
Code:
--------------------
select albums.title 'album', group_concat(artists.name,';') 'artists',
group_concat(albumartists.name,';') 'album artists',
group_concat(composers.name,';') 'composers', group_concat(conductors.name,';')
'conductors', tracks.title, tracks.disc, tracks.tracknum, path(tracks.url) as
'path', track_statistics.rating, track_statistics.playcount
from tracks
left join contributor_track as cta on cta.track=tracks.id and cta.role
in (1,6,4)
left join contributors as artists on artists.id=cta.contributor
left join contributor_track as ctaa on ctaa.track=tracks.id and
ctaa.role in (5)
left join contributors as albumartists on
albumartists.id=ctaa.contributor
left join contributor_track as ctcd on ctcd.track=tracks.id and
ctcd.role in (3)
left join contributors as conductors on conductors.id=ctcd.contributor
left join contributor_track as ctcp on ctcp.track=tracks.id and
ctcp.role in (2)
left join contributors as composers on composers.id=ctcp.contributor
left join track_statistics on tracks.url=track_statistics.url
left join albums on albums.id=tracks.album
where tracks.audio=1
group by tracks.id
order by albums.title,tracks.disc,tracks.tracknum
--------------------
If you only want rated tracks, you would change it to something like
this (different where directive):
Code:
--------------------
select albums.title 'album', group_concat(artists.name,';') 'artists',
group_concat(albumartists.name,';') 'album artists',
group_concat(composers.name,';') 'composers', group_concat(conductors.name,';')
'conductors', tracks.title, tracks.disc, tracks.tracknum, path(tracks.url) as
'path', track_statistics.rating, track_statistics.playcount
from tracks
left join contributor_track as cta on cta.track=tracks.id and cta.role
in (1,6,4)
left join contributors as artists on artists.id=cta.contributor
left join contributor_track as ctaa on ctaa.track=tracks.id and
ctaa.role in (5)
left join contributors as albumartists on
albumartists.id=ctaa.contributor
left join contributor_track as ctcd on ctcd.track=tracks.id and
ctcd.role in (3)
left join contributors as conductors on conductors.id=ctcd.contributor
left join contributor_track as ctcp on ctcp.track=tracks.id and
ctcp.role in (2)
left join contributors as composers on composers.id=ctcp.contributor
left join track_statistics on tracks.url=track_statistics.url
left join albums on albums.id=tracks.album
where tracks.audio=1 and track_statistics.rating>0
group by tracks.id
order by albums.title,tracks.disc,tracks.tracknum
--------------------
Or if you want tracks with TrackStat information like playcounts to be
included even if the track isn't rated, you would use something like
this:
Code:
--------------------
select albums.title 'album', group_concat(artists.name,';') 'artists',
group_concat(albumartists.name,';') 'album artists',
group_concat(composers.name,';') 'composers', group_concat(conductors.name,';')
'conductors', tracks.title, tracks.disc, tracks.tracknum, path(tracks.url) as
'path', track_statistics.rating, track_statistics.playcount
from tracks
left join contributor_track as cta on cta.track=tracks.id and cta.role
in (1,6,4)
left join contributors as artists on artists.id=cta.contributor
left join contributor_track as ctaa on ctaa.track=tracks.id and
ctaa.role in (5)
left join contributors as albumartists on
albumartists.id=ctaa.contributor
left join contributor_track as ctcd on ctcd.track=tracks.id and
ctcd.role in (3)
left join contributors as conductors on conductors.id=ctcd.contributor
left join contributor_track as ctcp on ctcp.track=tracks.id and
ctcp.role in (2)
left join contributors as composers on composers.id=ctcp.contributor
left join track_statistics on tracks.url=track_statistics.url
left join albums on albums.id=tracks.album
where tracks.audio=1 and (track_statistics.rating>0 or
track_statistics.playcount>0)
group by tracks.id
order by albums.title,tracks.disc,tracks.tracknum
--------------------
Erland Isaksson ('My homepage' (http://erland.isaksson.info))
(Developer of 'many plugins/applets (both free and commercial)'
(http://wiki.slimdevices.com/index.php/User:Erland).
If you like to encourage future presence on this forum and/or third
party plugin/applet development, 'consider purchasing some plugins'
(http://license.isaksson.info))
*Interested in the future of music streaming ? 'ickStream - A world of
music at your fingertips'
(http://forums.slimdevices.com/showthread.php?98467-Pre-Announcement-ickStream&p=743516)*.
------------------------------------------------------------------------
erland's Profile: http://forums.slimdevices.com/member.php?userid=3124
View this thread: http://forums.slimdevices.com/showthread.php?t=20533
_______________________________________________
plugins mailing list
[email protected]
http://lists.slimdevices.com/mailman/listinfo/plugins
