Re: [Dbix-class] relationship with additional constraints?
On Fri, May 18, 2018 at 09:52:09AM +0200, Stuart Dodds wrote: > On 17/05/18 22:25, Matt S Trout wrote: > > Surely http://p3rl.org/DBIx::Class::ParameterizedJoinHack is exactly > > what's needed here - just use it on a belongs_to rel with a join_type of > > left so non-matching entries are still returned, then prefetch that rel? > > > > (ironic since I'm replying to the man who was kind enough to sponsor > > Shadowcat > > to develop that module in the first place :) > > > > Yes, I tried it yesterday and it is (almost) exactly what I need, thank you! > > I say "almost" because my use case actually calls for a might_have > relationship (rather than has_many) but it is easy enough to ->first off the > entry in the rs. Hmm. So, the only reason there's only a has_many there is because I didn't really have any "good" use cases for belongs_to at the time. I wouldn't be averse to adding the feature if you can write up a test for it. -- 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@lists.scsys.co.uk
Re: [Dbix-class] relationship with additional constraints?
On Thu, May 17, 2018 at 10:37:57PM +0100, Andrew Beverley wrote: > On Thu, 17 May 2018 20:25:07 + m...@shadowcat.co.uk wrote: > > Surely http://p3rl.org/DBIx::Class::ParameterizedJoinHack is exactly > > what's needed here - just use it on a belongs_to rel with a join_type of > > left so non-matching entries are still returned, then prefetch that rel? > > > > (ironic since I'm replying to the man who was kind enough to sponsor > > Shadowcat > > to develop that module in the first place :) > > Indeed - I didn't mention it as it's in the opening paragraph of Frew's > blog that I linked to ;-) Ah, the "that approach no longer works" threw me off. -- 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@lists.scsys.co.uk
Re: [Dbix-class] relationship with additional constraints?
On 17/05/18 22:25, Matt S Trout wrote: > Surely http://p3rl.org/DBIx::Class::ParameterizedJoinHack is exactly > what's needed here - just use it on a belongs_to rel with a join_type of > left so non-matching entries are still returned, then prefetch that rel? > > (ironic since I'm replying to the man who was kind enough to sponsor Shadowcat > to develop that module in the first place :) > Yes, I tried it yesterday and it is (almost) exactly what I need, thank you! I say "almost" because my use case actually calls for a might_have relationship (rather than has_many) but it is easy enough to ->first off the entry in the rs. Stuart ___ 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@lists.scsys.co.uk
Re: [Dbix-class] relationship with additional constraints?
On Thu, 17 May 2018 20:25:07 + m...@shadowcat.co.uk wrote: > Surely http://p3rl.org/DBIx::Class::ParameterizedJoinHack is exactly > what's needed here - just use it on a belongs_to rel with a join_type of > left so non-matching entries are still returned, then prefetch that rel? > > (ironic since I'm replying to the man who was kind enough to sponsor Shadowcat > to develop that module in the first place :) Indeed - I didn't mention it as it's in the opening paragraph of Frew's blog that I linked to ;-) Andy ___ 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@lists.scsys.co.uk
Re: [Dbix-class] relationship with additional constraints?
Surely http://p3rl.org/DBIx::Class::ParameterizedJoinHack is exactly what's needed here - just use it on a belongs_to rel with a join_type of left so non-matching entries are still returned, then prefetch that rel? (ironic since I'm replying to the man who was kind enough to sponsor Shadowcat to develop that module in the first place :) -- 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@lists.scsys.co.uk
Re: [Dbix-class] relationship with additional constraints?
The following (below) I sent to "Frew" upon which he updated the post to
indicate that the hack does not work. (Some of his hack does work and some
not.)
In his example is:
my @rows = do {
local $My::Schema::Result::Foo::SHARE_TYPE = [1, 2];
$rs->search(undef, { join => 'output_devices' })->all
};
This usage still works having the local and "all" in the same scope.
someone shared with me your
https://blog.afoolishmanifesto.com/posts/dbix-class-parameterized-
relationships/#extended-relationship-refresher
I ran into a problem with the wrapping into a resultset method
At the time the "all" is called the "local" has gone out of scope so
the die gets triggered
die "no share_type specified!" unless $SHARE_TYPE;
For myself I had to have this as the last in the chain and in the
resultset method call the all itself.
On Thu, May 17, 2018 at 12:35 PM, Andrew Beverley wrote:
> On Thu, 17 May 2018 11:12:45 +0200 do...@united-domains.de wrote:
> > Is it possible to create a relationship which has, along with the
> > join condition, an additional constraint where the value could
> > somehow be passed when the search() method is called?
>
> This should answer your question:
>
> https://blog.afoolishmanifesto.com/posts/dbix-class-parameterized-
> relationships/
>
> Interestingly the post has been updated since I last looked at it
> saying that approach no longer works. I'm not sure why, as it still
> seems to work for me. Frew - can you elaborate?
>
> > - Get a list of all movies, as well as a review by Sue if it exists
> > - If a movie does not have a review by Sue then it should still be
> > returned with reviews.text = NULL.
> > - If a movie does not have a review by Sue but it has a review by
> > someone else then it should also be returned with reviews.text = NULL
>
> The other method you might want to consider is using a correlated
> sub-query. It's a little cleaner from a DBIx::Class viewpoint, but I
> think you'd need to pull out each review column separately, so would
> depend what you want to retrieve. Another excellent blog from Frew:
>
> https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-
> resultset-correlaterelationship/
>
> It allows you to do something like this (untested):
>
> '+columns' => {
> sue_review => $self->schema->resultset('Review')
>->correlate('movies')
>->search({ person => 'sue' })
>->get_column('review_text')
>->as_query,
> },
>
> Andy
>
>
> ___
> 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@lists.scsys.co.uk
>
___
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@lists.scsys.co.uk
Re: [Dbix-class] relationship with additional constraints?
On Thu, 17 May 2018 11:12:45 +0200 do...@united-domains.de wrote:
> Is it possible to create a relationship which has, along with the
> join condition, an additional constraint where the value could
> somehow be passed when the search() method is called?
This should answer your question:
https://blog.afoolishmanifesto.com/posts/dbix-class-parameterized-relationships/
Interestingly the post has been updated since I last looked at it
saying that approach no longer works. I'm not sure why, as it still
seems to work for me. Frew - can you elaborate?
> - Get a list of all movies, as well as a review by Sue if it exists
> - If a movie does not have a review by Sue then it should still be
> returned with reviews.text = NULL.
> - If a movie does not have a review by Sue but it has a review by
> someone else then it should also be returned with reviews.text = NULL
The other method you might want to consider is using a correlated
sub-query. It's a little cleaner from a DBIx::Class viewpoint, but I
think you'd need to pull out each review column separately, so would
depend what you want to retrieve. Another excellent blog from Frew:
https://blog.afoolishmanifesto.com/posts/introducing-dbix-class-helper-resultset-correlaterelationship/
It allows you to do something like this (untested):
'+columns' => {
sue_review => $self->schema->resultset('Review')
->correlate('movies')
->search({ person => 'sue' })
->get_column('review_text')
->as_query,
},
Andy
___
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@lists.scsys.co.uk
