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', [EMAIL PROTECTED] } },
{
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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
