> -----Original Message----- > From: Peter Rabbitson [mailto:[email protected]] > Sent: Saturday, December 12, 2009 3:37 PM > To: DBIx::Class user and developer list > Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column > while limiting rows > > On Fri, Dec 11, 2009 at 02:24:29PM -0800, Alan Humphrey wrote: > > An update on this problem. > > > > Database was converted to MSSQL2008. > > > > The latest code works fine, except if prefetch is used. See below > for code and trace results. > > > > Try [1]. This *should* be the end of this :) > > [1] http://dev.catalyst.perl.org/repos/bast/DBIx- > Class/0.08/branches/mssql_limit_regression/ >
Good news and bad news. Good news - results are coming back. Bad news - the sort is wrong. Example: Generated code and results with no paging: SELECT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined FROM surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id ORDER BY surveyor.name: <snip> Steve Grayson Steve Johnson Steve Johnson Sue Yates Sue Yates Susie Stillman Susie Stillman Terry Mace Terry Mace Terry Mace Terry Mace Terry Mace Tracey Scalici Tristan Nunez Vera Cragin Woody Franzen Woody Franzen Zoe Froyland Now, with paging, trying to get the last page of results. (Other pages are also wrong. This just shows it most dramatically.) SELECT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined FROM ( SELECT * FROM ( SELECT orig_query.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM (SELECT me.id, me.surveyor_id, me.survey_site_id, me.year FROM (SELECT TOP 100 PERCENT me.id, me.surveyor_id, me.survey_site_id, me.year FROM surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id ORDER BY surveyor.name) me) orig_query ) rno_subq WHERE rno__row__index BETWEEN 136 AND 150 ) me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id ORDER BY surveyor.name: Alex Greene Alex Greene David Gluckman Ian Maunsell Ian Maunsell Jim Oakland John Cragin Ken Wilson Port Townsend Marine Science Center Ron Martin Ron Martin Ron Sikes Rosemary Sikes - Alan _______________________________________________ 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/[email protected]
