[Catalyst] has_many but no left join?!

[Francisco Obispo]

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 <rbwohlfarth at gmail.com> wrote:

> On Tue, Jun 18, 2013 at 11:54 AM, Stephen Shorrock <stephen.shorrock at 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 at 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: fobispo at isc.org
Phone: +1 650 423 1374 || INOC-DBA *3557* NOC
PGP KeyID = B38DB1BE