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

J. Shirley jshirley at gmail.com
Fri Jun 8 17:55:25 GMT 2007


On 6/8/07, Anthony Gladdish <a.j.gladdish at newcastle.ac.uk> wrote:
>
> 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 =3D $schema->resultset('Submission')->search(
>       { 'submissionfields.field' =3D> { 'in', [@field_ids] } },
>       {
>         distinct =3D> 1,
>         prefetch =3D> { submissionfields =3D> [ {
> 'submissions_field_options'=3D>'field_option'}, {
> 'field'=3D>'field_type_input'} ] },
>         join =3D> { submissionfields =3D> [ {
> 'submissions_field_options'=3D>'field_option'}, {
> 'field'=3D>'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=3D>'left' attr.
>
> The sql I've tested that works is:
>
> SELECT * FROM `submission` s join `submission_field` sf on
> s.submission_id =3D sf.submission
> join `field` f on sf.field =3D f.field_id
> join `field_type` ft on ft.field_type_id =3D f.field_type_input
> left join `submission_field_option` sfo on sf.submission_field_id =3D
> sfo.submission_field
> left join `field_option` fo on sfo.field_option =3D 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
>
>
Hey Anthony,

I believe you're running into an issue with 0.07 prefetching that is fixed
for 0.08.  TThere is an RC2 candidate available for download at
http://www.dtmf.com/DBIx-Class-0.07999_05.tar.gz that you can try out.  If
that doesn't work for you, I'll put together a failing test case to get it
fixed.

You shouldn't experience any problems upgrading to the development version.

Thanks,
-Jay

-- =

J. Shirley :: jshirley at gmail.com :: Killing two stones with one bird...
http://www.toeat.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070608/f4c=
c64a5/attachment.htm


More information about the Dbix-class mailing list