Re: [Catalyst] has_many but no left join?!
Prefetch is really smart, and in the newer versions of DBIx::Class is even capable of taking multiple relations without the risk of duplicating rows. http://search.cpan.org/~frew/DBIx-Class-0.08205/lib/DBIx/Class/ResultSet.pm#prefetch Basically as I understood the problem, you want to fetch a row, and the "possible" relation, as a LEFT JOIN would provide. But you always want the main relation to return a value even if the join is NULL. Prefetch will do that without complicated use cases. Francisco On Jun 18, 2013, at 11:19 AM, Stephen Shorrock wrote: > I think Robert has cracked it, but why prefetch? > > Francisco Obispo Director of Applications and Services - ISC email: fobi...@isc.org Phone: +1 650 423 1374 || INOC-DBA *3557* NOC PGP KeyID = B38DB1BE ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
Again,
I think you want to use 'prefetch'.
On Jun 18, 2013, at 11:09 AM, Robert Wohlfarth wrote:
> Ah, well then I answered the wrong question :)
>
> How about this code?
> $c->model('MyApp::IndexStatus')->search({'old_statuses.id' =>
> undef},{join=>'old_statuses'})
>
>
Francisco Obispo
Director of Applications and Services - ISC
email: fobi...@isc.org
Phone: +1 650 423 1374 || INOC-DBA *3557* NOC
PGP KeyID = B38DB1BE
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
I think Robert has cracked it, but why prefetch?
On Jun 18, 2013 7:14 PM, "Francisco Obispo" wrote:
> Again,
>
> I think you want to use 'prefetch'.
>
>
>
> On Jun 18, 2013, at 11:09 AM, Robert Wohlfarth
> wrote:
>
> > Ah, well then I answered the wrong question :)
> >
> > How about this code?
> > $c->model('MyApp::IndexStatus')->search({'old_statuses.id' =>
> undef},{join=>'old_statuses'})
> >
> >
>
> Francisco Obispo
> Director of Applications and Services - ISC
> email: fobi...@isc.org
> Phone: +1 650 423 1374 || INOC-DBA *3557* NOC
> PGP KeyID = B38DB1BE
>
>
> ___
> List: Catalyst@lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive:
> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
> Dev site: http://dev.catalyst.perl.org/
>
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
That is indeed my plan! I admittedly haven't tried a manual join perhaps as
I was under the impression search related did the same thing. Though
thinking about it now search_related will attempt to return the related
objects thus not left joining and it it quite possibly the source of my
confusion. Many thanks for your support talking though the problem. I'm
quietly confident it will work tomorrow.
Many thanks
Stephen
On Jun 18, 2013 7:10 PM, "Robert Wohlfarth" wrote:
> On Tue, Jun 18, 2013 at 12:58 PM, Stephen Shorrock <
> stephen.shorr...@gmail.com> wrote:
>
>> Thanks Robert.
>>
>> I've attempted to extract the relevant relationship from my code, though
>> when i use it i intend to group by and test for a not exists via matching a
>> null row with the left join. I really need to have a left join in this
>> relationship. It appears to be working for other has_many relationships but
>> not under these circumstances. I wondered whether any knew of any
>> conditions that might prevent it from working.
>>
>> Ah, well then I answered the wrong question :)
>
> How about this code?
> $c->model('MyApp::IndexStatus')->search({'old_statuses.id' =>
> undef},{join=>'old_statuses'})
>
> From my novice understanding of DBIx::Class, I would expect that to find
> all records from MyApp::IndexStatus that do not have a corresponding row in
> old_statuses. The "join" tells the SQL generator to join the tables so that
> you can add conditions about old_statuses. DBIx::Class automatically does a
> LEFT JOIN because the relationship is declared has_many.
>
> --
> Robert Wohlfarth
>
> ___
> List: Catalyst@lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive:
> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
> Dev site: http://dev.catalyst.perl.org/
>
>
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
On Tue, Jun 18, 2013 at 12:58 PM, Stephen Shorrock <
stephen.shorr...@gmail.com> wrote:
> Thanks Robert.
>
> I've attempted to extract the relevant relationship from my code, though
> when i use it i intend to group by and test for a not exists via matching a
> null row with the left join. I really need to have a left join in this
> relationship. It appears to be working for other has_many relationships but
> not under these circumstances. I wondered whether any knew of any
> conditions that might prevent it from working.
>
> Ah, well then I answered the wrong question :)
How about this code?
$c->model('MyApp::IndexStatus')->search({'old_statuses.id' =>
undef},{join=>'old_statuses'})
>From my novice understanding of DBIx::Class, I would expect that to find
all records from MyApp::IndexStatus that do not have a corresponding row in
old_statuses. The "join" tells the SQL generator to join the tables so that
you can add conditions about old_statuses. DBIx::Class automatically does a
LEFT JOIN because the relationship is declared has_many.
--
Robert Wohlfarth
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
Thanks Robert.
I've attempted to extract the relevant relationship from my code, though
when i use it i intend to group by and test for a not exists via matching a
null row with the left join. I really need to have a left join in this
relationship. It appears to be working for other has_many relationships but
not under these circumstances. I wondered whether any knew of any
conditions that might prevent it from working.
Best wishes
Stephen
On Jun 18, 2013 6:45 PM, "Robert Wohlfarth" wrote:
> On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock <
> stephen.shorr...@gmail.com> wrote:
>
>
>> then (in Cat app)
>> $c->model('MyApp::IndexStatus')->search_related('old_statuses');
>>
>> produces SQL something like:
>> SELECT me.myindex, me.myoldindex, me.mystatus FROM index_statuses me
>> JOIN index_statuses old_statuses ON old_statuses.myindex = me.myoldindex:
>>
>> even adding {join_type=>'LEFT'} to the arguments of has_many does not
>> produce a left join.
>>
>
> It looks like the code asks for "all MyApp::IndexStatus with matching
> old_statuses records". That translates into an INNER JOIN.
>
> If you're looking for "all MyApp::IndexStatus records along with any
> related old_statuses records", try something like this:
> foreach my $index_status ($c->model('MyApp::IndexStatus')->all) {
> # Generate the HTML here with columns from $index_status...
> foreach my $old_status ($index_status->old_statuses->all) {
> # Generate the HTML here for any $old_status records...
> }
> }
>
> --
> Robert Wohlfarth
>
> ___
> List: Catalyst@lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive:
> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
> Dev site: http://dev.catalyst.perl.org/
>
>
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
That's going to generate a whole bunch of queries (1 per row), unless you
include it in a prefetch attribute to the first call:
foreach my $index_status
($c->model('MyApp::IndexStatus')->search(undef,{prefetch=>'old_statuses'})) {
# Generate the HTML here with columns from $index_status...
foreach my $old_status ($index_status->old_statuses->all) {
# Generate the HTML here for any $old_status records...
}
}
On Jun 18, 2013, at 10:44 AM, Robert Wohlfarth wrote:
> On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock
> wrote:
>
> then (in Cat app)
> $c->model('MyApp::IndexStatus')->search_related('old_statuses');
>
> produces SQL something like:
> SELECT me.myindex, me.myoldindex, me.mystatus FROM index_statuses me JOIN
> index_statuses old_statuses ON old_statuses.myindex = me.myoldindex:
>
> even adding {join_type=>'LEFT'} to the arguments of has_many does not produce
> a left join.
>
> It looks like the code asks for "all MyApp::IndexStatus with matching
> old_statuses records". That translates into an INNER JOIN.
>
> If you're looking for "all MyApp::IndexStatus records along with any related
> old_statuses records", try something like this:
> foreach my $index_status ($c->model('MyApp::IndexStatus')->all) {
> # Generate the HTML here with columns from $index_status...
> foreach my $old_status ($index_status->old_statuses->all) {
> # Generate the HTML here for any $old_status records...
> }
> }
>
> --
> Robert Wohlfarth
> ___
> List: Catalyst@lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
> Dev site: http://dev.catalyst.perl.org/
Francisco Obispo
Director of Applications and Services - ISC
email: fobi...@isc.org
Phone: +1 650 423 1374 || INOC-DBA *3557* NOC
PGP KeyID = B38DB1BE
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] has_many but no left join?!
On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock <
stephen.shorr...@gmail.com> wrote:
> then (in Cat app)
> $c->model('MyApp::IndexStatus')->search_related('old_statuses');
>
> produces SQL something like:
> SELECT me.myindex, me.myoldindex, me.mystatus FROM index_statuses me JOIN
> index_statuses old_statuses ON old_statuses.myindex = me.myoldindex:
>
> even adding {join_type=>'LEFT'} to the arguments of has_many does not
> produce a left join.
>
It looks like the code asks for "all MyApp::IndexStatus with matching
old_statuses records". That translates into an INNER JOIN.
If you're looking for "all MyApp::IndexStatus records along with any
related old_statuses records", try something like this:
foreach my $index_status ($c->model('MyApp::IndexStatus')->all) {
# Generate the HTML here with columns from $index_status...
foreach my $old_status ($index_status->old_statuses->all) {
# Generate the HTML here for any $old_status records...
}
}
--
Robert Wohlfarth
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
[Catalyst] has_many but no left join?!
Hi,
I was wondering if anyone could offer some insight into a problem I'm
finding. I wish to create a relationship using a left join by this doesn't
appear to be happening when I trace the SQL. My only thought is that it is
something to do with using a 'table' without a primary key or whose
underlying database object is actually not a table but a view?
The example class / relationship below doe snot produce a left join, but
would have thought it aught to:
package MyApp::Schema::Result::IndexStatus;
__PACKAGE__->table("index_statuses");
__PACKAGE__->add_columns(
"myindex",
{ data_type => "bigint", is_nullable => 0 },
"myoldindex",
{ data_type => "bigint", is_nullable => 0 },
"mystatus",
{ data_type => "varchar", default_value => "", is_nullable => 0, size =>
45 },
);
#relationship to find all old index statuses:
__PACKAGE__->has_many(
"old_statuses"=>"MyApp::IndexStatus",
{ "foreign.myindex" => "self.myoldindex"},
);
then (in Cat app)
$c->model('MyApp::IndexStatus')->search_related('old_statuses');
produces SQL something like:
SELECT me.myindex, me.myoldindex, me.mystatus FROM index_statuses me JOIN
index_statuses old_statuses ON old_statuses.myindex = me.myoldindex:
even adding {join_type=>'LEFT'} to the arguments of has_many does not
produce a left join.
DBIx::Class has been pretty robust, I'm assuming that this is an
undocumented feature rather than a bug. However how can I get it to do a
left join?
Thanks in advance
Stephen
___
List: Catalyst@lists.scsys.co.uk
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] Any bug reports or issues with maint release 5.90042?
The unicode warnings have disappeared in v5.90040. Thanks! On Tue, Jun 18, 2013 at 7:06 AM, John Napiorkowski wrote: > Hey All, > > You might have noticed we've had a few bug fix releases of Catalyst to fix > regressions introduced in v5.90040. CPAN testers is reporting more than > 100 test passes with no failures for the latest release. Regressions in > the previous releases were mostly issues with missing dependencies arising > from when we cored the test cases for the unicode plugin. Those should be > fixed, but if anyone that was having trouble and reported it, could you > please check the new release and verify that it is now working, we'd all be > pretty grateful. This way we can finish the retrospective on this release > and start planning the next dev cycle, which I hope to bring more goodies > for asynchronous support and start to better expose Catalyst's native PSI > support, as well as a few other things. > > Thanks! > > John > > ___ > List: Catalyst@lists.scsys.co.uk > Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst > Searchable archive: > http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ > Dev site: http://dev.catalyst.perl.org/ > > ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
[Catalyst] Any bug reports or issues with maint release 5.90042?
Hey All, You might have noticed we've had a few bug fix releases of Catalyst to fix regressions introduced in v5.90040. CPAN testers is reporting more than 100 test passes with no failures for the latest release. Regressions in the previous releases were mostly issues with missing dependencies arising from when we cored the test cases for the unicode plugin. Those should be fixed, but if anyone that was having trouble and reported it, could you please check the new release and verify that it is now working, we'd all be pretty grateful. This way we can finish the retrospective on this release and start planning the next dev cycle, which I hope to bring more goodies for asynchronous support and start to better expose Catalyst's native PSI support, as well as a few other things. Thanks! John___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
