Home of the Squeezebox™ & Transporter® network music players.
Results 1 to 3 of 3
  1. #1
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,295

    DBIx and Slim::Schema object help

    I'm not sure how to accomplish this, could someone give me some clue.

    I have a list of album identifiers and I like to retrieve and be able to retrieve them in one SQL and also be able to access its relations (artists) without doing separate SQL statements.

    Sample code:
    Code:
    my @albumIds = qw('306','328');
    my @albums = Slim::Schema->resultset('Album')->search({'me.id' => {'in' => \@albumIds}},{'join' => {'contributorAlbums' => 'contributor'},'distinct' => 1});
    Which results in the following SQL statement according to database.sql logging:
    Code:
    SELECT me.id, me.titlesort, me.contributor, me.compilation, me.year, me.artwork, me.disc, me.discc, me.musicmagic_mixable, me.titlesearch, me.replay_gain, me.replay_peak, me.musicbrainz_id, me.title FROM albums me LEFT JOIN contributor_album contributorAlbums ON contributorAlbums.album = me.id LEFT JOIN contributors contributor ON contributor.id = contributorAlbums.contributor WHERE ( me.id IN ( ?, ?) ) GROUP BY me.id, me.titlesort, me.contributor, me.compilation, me.year, me.artwork, me.disc, me.discc, me.musicmagic_mixable, me.titlesearch, me.replay_gain, me.replay_peak, me.musicbrainz_id, me.title: '306', '328'
    What I would like to do after this, is things like this without making more SQL statements execute:
    Code:
    my $album = pop @albums;
    my @contributorAlbums = $album->contributorAlbums;
    my @artists = $album->artists;
    my @contributors = $album->contributors;
    I can see from the SQL log that it does the join but it doesn't really seem to get the contributor_album and contributors table contents, so that's at least on problem.

    Is this possible to do this through DBIx ?
    Any clue how ?

    The reason I want to do this is that I like to be able to call Slim::Schema::Album::displayAsHTML without executing additional SQL statements. In addition to this I also want to access $album->artists and $album->contributors without executing additional SQL statements.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets
    Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

  2. #2
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,295
    I've got a little bit further by using the 'prefetch' keyword like this:

    Code:
    my @albumIds = qw('306','328');
    my @albums = Slim::Schema->resultset('Album')->search({'me.id' => {'in' => \@albumIds}},{
         'prefetch' => {'contributorAlbums' => 'contributor'},
    });
    This makes it work to do this:
    my @contributorAlbums = $album->contributorAlbums;

    However, it still doesn't work to use these functions, they still generate extra SQL statements:
    my @artists = $album->artists;
    my @contributors = $album->contributors;

    The result is that I can still not use Slim::Schema::Album::displayAsHTML without executing additional SQL statements.

    As usual, I can always copy the displayAsHTML code into my own plugin and change it to use $album->contributorAlbums instead of $album->artists but it would be kind of nice of not having to maintain all this Logitech code in my own plugin.

    Is there any way to either make the caching work or implement the methods on all Slim::Schema::* objects so they take advantage of prefetched data and doesn't retrieve it again ?

    For example, this works:
    Code:
    my @artists = ();
    for my $contributorAlbums ($album->contributorAlbums) {
    	if($contributorAlbums->role == 5) {
    		push @artists,$contributorAlbums->contributor;
    	}
    }
    And this doesn't work (generates extra SQL statements):
    Code:
    my @roles = (5);
    my @artists = $album
                      ->search_related('contributorAlbums', { 'role' => { 'in' => \@roles } })
                      ->search_related('contributor')->distinct->all;
    Of course, the first alternative will probably be slower if the data haven't been pre-fetched as it probably urns a separate query per contributor it iterates through.
    Erland Isaksson (My homepage)
    Developer of many plugins/applets
    Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

  3. #3
    Senior Member erland's Avatar
    Join Date
    Dec 2005
    Location
    Sweden
    Posts
    11,295
    Does anyone have answers to these questions ?
    Should I just assume that the Slim::Schema:* code isn't made to work with DBIx prefetching/caching and re-implement an improved version in my own plugins ?
    Erland Isaksson (My homepage)
    Developer of many plugins/applets
    Starting with LMS 8.0 I no longer support my plugins/applets (see here for more information )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •