Prefetching resulset subqueries

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Prefetching resulset subqueries

thilo.fester@googlemail.com
Hello everbody,

is it possible to use a resultset as subquery in the FROM clause and prefetch its results in a way, that it is available as an accessor in the final resultset?

In detail I would like to do a query like that:

select me.*, properties.me_id, properties.value
from table_me me join ( select id, property from complex_subselect where condition ) properties
on me.id = properties.id

... and the "properties" should be collapsed to the me-Table.

I would like to use DBIx::Class::ResultClass::HashRefInflator, but I think this is based on defining a "prefetch" and again this needs to find accessors in the underlying table_me resultset definition.

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.

What I want my final result hash to look like is...

[
    {
        me.id => 1,
        me.hello => "world",
        me.some => "thing",
        # ... more fields

        properties => [
            { me_id => 1, value => "something, that I don't get by simply joining tables" },
            { me_id => 1, value => "the next item in a really long lis" },
            # ... more properties
        ]
    },
    {
        # .... next result object
    }
]

Is it possible to do that in a single database request (with DBIx::Class::ResultClass::HashRefInflator)?

Thanks in advance : )
Thilo

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>:
On 02/11/2015 11:13 AM, [hidden email] 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@...


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Prefetching resulset subqueries

thilo.fester@googlemail.com


2015-02-14 12:57 GMT+01:00 Peter Rabbitson <[hidden email]>:

Note that the above usage is unsupported (specifying a from structure by hand may or may not work this way). [...] this *will* stop working in the distant future.

Normally I don't use it. I just did to find a solution for this special case and because ... ^_^ ... the documentation (still) says sub queries are supported with the from keyword -> https://metacpan.org/pod/DBIx::Class::Manual::Cookbook#Support

But thanks for that information, I will avoid it. Is it appropriate to contribute a fix for the Cookbook's POD or should I consult somebody before?
 


In essence your best bet is to define a real relationship to "tracklist" with the correct subquery and everything, like in this example: https://github.com/dbsrgits/dbix-class/blob/current/for_cpan_index/t/lib/DBICTest/Schema/CD.pm#L120-L154

If you are interested in the inner logic that collapse => 1 triggers, it can be found in this "double-recursion" function: https://github.com/dbsrgits/dbix-class/blob/current/for_cpan_index/lib/DBIx/Class/ResultSource/RowParser.pm#L149-L398

Thanks for pointing out those lines. I wrote a relationship very similar to "last_track" in the CD test case and it does the job:

    __PACKAGE__->has_many(
        'tracklist',
        'MyApp::Schema::Result::Track',
        sub {
            my $args = shift;

            return (
                {
                    "$args->{foreign_alias}.trackid" => { '-in' =>
                        $args->{self_resultsource}->schema->resultset('Track')->search(
                            { 'cd.artist' => { -ident => "$args->{self_alias}.artistid" } },
                            {
                                join => 'cd',
                                alias => 'correlated_tracks',
                                columns => ['trackid']
                            },
                        )->as_query
                    }
                }
            );
        },
    );

Now I can use the "prefetch" keyword in the search call.

 
If this reply isn't sufficient to answer your question - please ask further.
Your answer is very helpful and constructive! Thank you very much : )



_______________________________________________
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@...