[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
> 
> The same code run against a MySQL database works fine.
> 
> - Alan
> 

I *think* if you +select/+as the column you order by, things should just work.
It's been a while since I rewrote the abomination that is _Top :) If this
indeed solves the problem - we can dig into making it smarter.