[Dbix-class] Search - prefetch and left joins problem

[Anthony Gladdish]

Hi there,

1. My tables and relationship aliases:

submission *has_many* submissionfields

submissionfields *has_many* submissions_field_options
submissionfields *many_to_many* fieldoptions
So, submissions_field_options is the many to many join table between
submissionfields and fieldoptions

submissionfields.field *belongs_to* field
field.field_type_input *belongs_to* field_type_input

2. My search: 

Want to prefetch all tables as objects, including relevant
submissionfields that have/don't have field_options:

    my $rs = $schema->resultset('Submission')->search(
      { 'submissionfields.field' => { 'in', [@field_ids] } },
      {
        distinct => 1,
        prefetch => { submissionfields => [ {
'submissions_field_options'=>'field_option'}, {
'field'=>'field_type_input'} ] },
        join => { submissionfields => [ {
'submissions_field_options'=>'field_option'}, {
'field'=>'field_type_input'} ] },
      },
    );

... this only returns submissionfields that HAVE
submissions_field_options, AND also isn't prefetching field_option
objects as I would expect.

Left joins are required between submission_field and
submission_field_option, and between submission_field_option and
field_option, but are not being picked up in my relationships even when
specifying the join_type=>'left' attr.

The sql I've tested that works is:

SELECT * FROM `submission` s join `submission_field` sf on
s.submission_id = sf.submission
join `field` f on sf.field = f.field_id
join `field_type` ft on ft.field_type_id = f.field_type_input
left join `submission_field_option` sfo on sf.submission_field_id =
sfo.submission_field
left join `field_option` fo on sfo.field_option = fo.field_option_id

Can anyone shed light as to how to achieve this sql using
prefetching/left joins?

I'm using DBIx::Class version '0.07006'.

Thanks!
Anthony