Merging ResultSets

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Merging ResultSets

Vladimir Melnik
Hello,

Is that possible to merge multiple ResultSets into one? For example, I’ve got 3 different ResultSets (as the yields of 3 different SQL-queries), but I’m pretty sure that they are based on the same class. Yes, I can work with their Results as with elements of a list, but sometimes it would be much more complicated to work with them as with a single ResultSet. Is that possible?

In other words, here's what I have to do now:
        my $rs1 = $schema->resultset('Foo')->search({ bar => 1 });
        my $rs2 = $schema->resultset('Foo')->search({ bar => 2 });
        my $rs3 = $schema->resultset('Foo')->search({ bar => 3 });
        foreach my $rs ($rs1, $rs2, $rs3) {
            foreach my $r ($rs->all) {
                $r->do_something;
            }
        }

...and here’s what I’d like to do:
        my $rs1 = $schema->resultset('Foo')->search({ bar => 1 });
        my $rs2 = $schema->resultset('Foo')->search({ bar => 2 });
        my $rs3 = $schema->resultset('Foo')->search({ bar => 3 });
        my $rs_merged = ...; # <-- Something cool happens here
        foreach ($rs_merged->all) {
            $r->do_something;
        }

...maybe something like that?
        my $rs1 = $schema->resultset('Foo')->search({ bar => 1 });
        my $rs2 = $schema->resultset('Foo')->search({ bar => 2 });
        my $rs3 = $schema->resultset('Foo')->search({ bar => 3 });
        my $rs_merged = $schema->resultset('Foo');
        foreach my $rs ($rs1, $rs2, $rs3) {
            foreach my $r ($rs->all) {
                $rs_merged->new_result($r->get_columns)
            }
        }
        foreach ($rs_merged->all) {
            $r->do_something;
        }

Thanks in advance for any hints and clues!

--
V.Melnik

_______________________________________________
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
|  
Report Content as Inappropriate

Re: Merging ResultSets

Dagfinn Ilmari Mannsåker
Vladimir Melnik <[hidden email]> writes:

> Hello,
>
> Is that possible to merge multiple ResultSets into one? For example,
> I’ve got 3 different ResultSets (as the yields of 3 different
> SQL-queries), but I’m pretty sure that they are based on the same
> class. Yes, I can work with their Results as with elements of a list,
> but sometimes it would be much more complicated to work with them as
> with a single ResultSet. Is that possible?

What you are looking for is the 'union_all' method from
https://metacpan.org/pod/DBIx::Class::Helper::ResultSet::SetOperations

> ...and here’s what I’d like to do:
> my $rs1 = $schema->resultset('Foo')->search({ bar => 1 });
> my $rs2 = $schema->resultset('Foo')->search({ bar => 2 });
> my $rs3 = $schema->resultset('Foo')->search({ bar => 3 });
> my $rs_merged = ...; # <-- Something cool happens here

        my $rs_merged = $rs1->union_all([$rs2, $rs3]);

> foreach ($rs_merged->all) {
>    $r->do_something;
> }
[…]
> Thanks in advance for any hints and clues!

Hope this helps!

- ilmari
--
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law

_______________________________________________
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
|  
Report Content as Inappropriate

Re: Merging ResultSets

Vladimir Melnik
On Tue, Apr 18, 2017 at 02:43:55PM +0100, Dagfinn Ilmari Mannsåker wrote:
> What you are looking for is the 'union_all' method from
> https://metacpan.org/pod/DBIx::Class::Helper::ResultSet::SetOperations

Thank you very much, it's exactly what I've been looking for!

--
V.Melnik

_______________________________________________
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
|  
Report Content as Inappropriate

Re: Merging ResultSets

Matt S Trout-2
On Tue, Apr 18, 2017 at 05:20:55PM +0300, Vladimir Melnik wrote:
> On Tue, Apr 18, 2017 at 02:43:55PM +0100, Dagfinn Ilmari Mannsåker wrote:
> > What you are looking for is the 'union_all' method from
> > https://metacpan.org/pod/DBIx::Class::Helper::ResultSet::SetOperations
>
> Thank you very much, it's exactly what I've been looking for!

Note that if you don't need an actual UNION query you can also do -

  my $merged = $schema->resultset('Foo');
  $merged->set_cache([ map $_->all, $rs1, $rs2, $rs3 ]);

and then iterating $merged will just use the cache.

This may or may not be better, depending on your goal.

--
Matt S Trout - Shadowcat Systems - Perl consulting with a commit bit and a clue

http://shadowcat.co.uk/blog/matt-s-trout/   http://twitter.com/shadowcat_mst/

Email me now on mst (at) shadowcat.co.uk and let's chat about how our CPAN
commercial support, training and consultancy packages could help your team.

_______________________________________________
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
|  
Report Content as Inappropriate

Re: Merging ResultSets

Mitchell Elutovich
Matt will your way though not have multiple trips to the db?

On Wed, Apr 19, 2017 at 3:17 AM, Matt S Trout <[hidden email]> wrote:
On Tue, Apr 18, 2017 at 05:20:55PM +0300, Vladimir Melnik wrote:
> On Tue, Apr 18, 2017 at 02:43:55PM +0100, Dagfinn Ilmari Mannsåker wrote:
> > What you are looking for is the 'union_all' method from
> > https://metacpan.org/pod/DBIx::Class::Helper::ResultSet::SetOperations
>
> Thank you very much, it's exactly what I've been looking for!

Note that if you don't need an actual UNION query you can also do -

  my $merged = $schema->resultset('Foo');
  $merged->set_cache([ map $_->all, $rs1, $rs2, $rs3 ]);

and then iterating $merged will just use the cache.

This may or may not be better, depending on your goal.

--
Matt S Trout - Shadowcat Systems - Perl consulting with a commit bit and a clue

http://shadowcat.co.uk/blog/matt-s-trout/   http://twitter.com/shadowcat_mst/

Email me now on mst (at) shadowcat.co.uk and let's chat about how our CPAN
commercial support, training and consultancy packages could help your team.


_______________________________________________
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
|  
Report Content as Inappropriate

Re: Merging ResultSets

Matt S Trout-2
On Wed, Apr 19, 2017 at 12:52:25PM +0300, Mitchell Elutovich wrote:
> Matt will your way though not have multiple trips to the db?

Yes. But if you're already using the resultsets for something else, that's
probably already the case.

If you're not already using the resultsets for something else, then I'd
construct a single resultset using '-or' to generate OR clauses in the SQL
and avoid needing the UNIONs in the first place.

If neither of the above applies, then I'm not really sure what your use
case is and a bit more detail would probably help elucidate.

--
Matt S Trout - Shadowcat Systems - Perl consulting with a commit bit and a clue

http://shadowcat.co.uk/blog/matt-s-trout/   http://twitter.com/shadowcat_mst/

Email me now on mst (at) shadowcat.co.uk and let's chat about how our CPAN
commercial support, training and consultancy packages could help your team.

_______________________________________________
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
|  
Report Content as Inappropriate

Re: Merging ResultSets

fREW Schmidt
In reply to this post by Vladimir Melnik
Another option, which might be more efficient, is the SearchOr Helper. 

--
Sent from a telephone. Pardon my brevity.

On Apr 18, 2017 7:22 AM, "Vladimir Melnik" <[hidden email]> wrote:
On Tue, Apr 18, 2017 at 02:43:55PM +0100, Dagfinn Ilmari Mannsåker wrote:
> What you are looking for is the 'union_all' method from
> https://metacpan.org/pod/DBIx::Class::Helper::ResultSet::SetOperations

Thank you very much, it's exactly what I've been looking for!

--
V.Melnik

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