[Dbix-class] Prefetching resulset subqueries

[thilo.fester@googlemail.com]

Hello Peter,

thanks for your comment.

When I want to reconstruct my situation with the data structure given in
https://metacpan.org/pod/DBIx::Class::Manual::Example my search call would
be ...

    my $track_list_rs = $schema->resultset( 'Track' )->search( {},
        {
            join => { 'cd' => 'artist' },
            '+select' => [ 'artist.artistid' ],
            '+as' => [ 'artistid' ],
            alias   => 'track_list_sub_rs'
        }
    );

    my $rs = $schema->resultset('Artist')->search(
        { 'me.name' => 'Michael Jackson' },
        {
            from  => [
                { 'me'    => 'artist' },
                [
                    { 'tracklíst' => $track_list_rs->as_query },
                    { 'me.artistid' => { -ident => 'tracklíst.artistid' } },
                ]
            ],
            '+columns' => [ qw/ tracklíst.title / ],
            collapse => 1,
            result_class    => 'DBIx::Class::ResultClass::HashRefInflator',
        }
    );

    while( my $michael_jackson = $rs->next ) { # there can be only one...
        print Dumper( $michael_jackson );
    }

Here I simply want to build a list of tracks per artist and connect it to
the artist directly.

It is producing a good sql query string, but fails because of the missing
accessor:

    SELECT me.artistid, me.name, tracklíst.title
      FROM artist me
      JOIN (
        SELECT track_list_sub_rs.trackid, track_list_sub_rs.cd,
track_list_sub_rs.title, artist.artistid
          FROM track track_list_sub_rs
          JOIN cd cd
            ON cd.cdid = track_list_sub_rs.cd
          JOIN artist artist
            ON artist.artistid = cd.artist
       ) tracklíst
        ON me.artistid = tracklíst.artistid
    WHERE me.name = 'Michael Jackson'
    ORDER BY me.artistid

The error message is "No such relationship 'tracklíst' on Artist at
/usr/local/share/perl/5.18.2/DBIx/Class/Schema.pm line 1081.".

For prefetching the tracklist, I use '+columns' and 'collapse', because I
expected those two not to check for the accessor "tracklist" (but this
seemed to be a wrong assumption).

Unfortunately I was not able to reconstruct the search call that was
providing a result with only a single item out of the sub query results,
since I made some changes in the meantime.

Best regards
Thilo


2015-02-12 16:47 GMT+01:00 Peter Rabbitson <rabbit+dbic at rabbit.us>:

> On 02/11/2015 11:13 AM, thilo.fester at googlemail.com wrote:
>
>>
>> At the beginning working around this problem seemed kind of trivial to
>> me, but now I'm struggling. I thought, I could simply use a sub query in
>> the from-attribute when searching the resultset and adding "+columns"
>> and setting "collapse" to a true value. But this way, I am ending up
>> with only a single property column.
>>
>
> The short answer is - it's likely possible, or it can be made to work with
> relatively few extra changes.
>
> But you need to show the actual search call you constructed, it's not
> clear what you tried from your description alone. Also makes the "this is
> what I want to get" example baseless and hard to interpret.
>
> Cheers
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/
> dbix-class at lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150213/a6acd14a/attachment.htm>