different query after upgrade

[Steve Rippl]

Hi,

I'm building an app that was on one machine with Debian's Catalyst
version 5.7.../DBIx::Class 0.08010 and I've had to move it to another
machine, so I bit the bullet and upgraded to latest versions of things
including Catalyst 5.8.../DBIx-Class 0.08109.

Now the following in my app

$c->stash->{sections} = [$c->model('DB::Section')->search(
                                {},
                                {
                                  join => [{'course_sections' =>
'courses'} , {'staff_sections' => 'staffs'}],
                                  order_by => ['courses.grade',
'staffs.name_last'],
                                  prefetch => [{'course_sections' =>
'courses'} , {'staff_sections' => 'staffs'}],
                                 })];


used to generate this query

SELECT
me.id, me.name, course_sections.section_id, course_sections.course_id,
courses.id, courses.name, courses.grade, courses.duration,
courses.reportcard, staff_sections.section_id, staff_sections.staff_id,
staffs.id, staffs.name_first, staffs.name_last, staffs.name_middle,
staffs.username, staffs.password, staffs.room, staffs.grade
FROM section me
LEFT JOIN course_section course_sections ON ( course_sections.section_id
= me.id )
JOIN course courses ON ( courses.id = course_sections.course_id )
LEFT JOIN section_staff staff_sections ON ( staff_sections.section_id =
me.id )
JOIN staff staffs ON ( staffs.id = staff_sections.staff_id )
ORDER BY courses.grade, staffs.name_last, course_sections.section_id,
staff_sections.section_id

and now it generates this

SELECT
me.id, me.name, course_sections.section_id, course_sections.course_id,
courses.id, courses.name, courses.grade, courses.duration,
courses.reportcard, staff_sections.section_id, staff_sections.staff_id,
staffs.id, staffs.name_first, staffs.name_last, staffs.name_middle,
staffs.username, staffs.password, staffs.room, staffs.grade
FROM section me
LEFT JOIN course_section course_sections ON course_sections.section_id =
me.id
LEFT JOIN course courses ON courses.id = course_sections.course_id
LEFT JOIN section_staff staff_sections ON staff_sections.section_id = me.id
LEFT JOIN staff staffs ON staffs.id = staff_sections.staff_id
ORDER BY courses.grade, staffs.name_last, course_sections.section_id,
staff_sections.section_id

Everything is a LEFT JOIN now.  How do I get back to the first one?  
What's changed?

Any pointers would be much appreciated!

Many thanks,
Steve

--
Steve Rippl
Technology Director
Woodland School District
360 225 9451 x326


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Wallace Reis]

On Fri, Aug 21, 2009 at 4:22 PM, Steve Rippl<[hidden email]> wrote:

> Now the following in my app
>
> $c->stash->{sections} = [$c->model('DB::Section')->search(
>                               {},
>                               {
>                                 join => [{'course_sections' => 'courses'} ,
> {'staff_sections' => 'staffs'}],
>                                 order_by => ['courses.grade',
> 'staffs.name_last'],
>                                 prefetch => [{'course_sections' =>
> 'courses'} , {'staff_sections' => 'staffs'}],
>                                })];

Are you sure that the rel between 'course_sections' class and
'courses' class is a belongs_to (or a proper add_relationship setup),
as well between 'staff_sections' class and 'staffs' class? If so,
report with a failing test case.

--
     wallace reis/wreis         Catalyst and DBIx::Class consultancy with a clue
     Software Developer and a commit bit: http://shadowcat.co.uk/catalyst/
 Shadowcat Systems Limited
 http://www.shadowcat.co.uk     http://www.linkedin.com/in/wallacereis

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...

[Wallace Reis]

Actually, never mind what i just said. DBIC is right, it's a proper bugfix.
*Generally*, it is not correct to LEFT JOIN one thing then JOIN
onwards from it, which clearly is not your case here.

Sorry for the wrong spot.

--
     wallace reis/wreis         Catalyst and DBIx::Class consultancy with a clue
     Software Developer and a commit bit: http://shadowcat.co.uk/catalyst/
 Shadowcat Systems Limited
 http://www.shadowcat.co.uk     http://www.linkedin.com/in/wallacereis

_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...