[Dbix-class] MSSQL - problem sorting by joined column while limiting rows

[Peter Rabbitson]

Alan Humphrey wrote:
> Hi -
> 
> If I try to sort by a column in a joined table in a MSSQL database AND limit
> the number of rows, the generated SQL is bad.  Here's the code:
> 
> my $result = $schema->resultset('SurveyorsSurveySites')->search({},
>     {join => 'surveyor',
>      order_by => ['surveyor.name'],
>      rows => 5
>      });
> 
> And here's the generated SQL.
> 
> SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year FROM ( SELECT
> TOP 5 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 ASC ) me ORDER BY surveyor.name

It looks like right-side joins finally got nailed. Please test this
branch[1] and report how things are. As a bonus you now get proper
offset-past-end-of-rs limits (i.e. you have 5 rows, you request
rows => 3, offset => 3 - you get 2 rows, not 3 as before).

Cheers

[1] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/mssql_rno_pagination/