Re: [Catalyst] has_many but no left join?!
On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock
stephen.shorr...@gmail.com wrote:
snip
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/
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 rbwohlfa...@gmail.com wrote:
On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock
stephen.shorr...@gmail.com wrote:
snip
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?!
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 rbwohlfa...@gmail.com wrote:
On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock
stephen.shorr...@gmail.com wrote:
snip
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?!
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?!
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 rbwohlfa...@gmail.com 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?!
I think Robert has cracked it, but why prefetch?
On Jun 18, 2013 7:14 PM, Francisco Obispo fobi...@isc.org wrote:
Again,
I think you want to use 'prefetch'.
On Jun 18, 2013, at 11:09 AM, Robert Wohlfarth rbwohlfa...@gmail.com
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?!
Again,
I think you want to use 'prefetch'.
On Jun 18, 2013, at 11:09 AM, Robert Wohlfarth rbwohlfa...@gmail.com 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?!
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 stephen.shorr...@gmail.com 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/
