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

[Alan Humphrey]

> -----Original Message-----
> From: Eden Cardim [mailto:edencardim at gmail.com]
> Sent: Monday, November 09, 2009 8:23 PM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
> while limiting rows
> 
> >>>>> "Alan" == Alan Humphrey <alan.humphrey at comcast.net> writes:
> 
>     Alan> Hi - If I try to sort by a column in a joined table in a
> MSSQL
>     Alan> database AND limit the number of rows, the generated SQL is
>     Alan> bad.  Here's the code:
> 
>     Alan> my $result =
>     Alan> $schema->resultset('SurveyorsSurveySites')->search({}, {join
>     Alan> => 'surveyor', order_by => ['surveyor.name'], rows => 5 });
> 
>     Alan> And here's the generated SQL.
> 
>     Alan> SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year
>     Alan> FROM ( SELECT TOP 5 me.id, me.surveyor_id, me.survey_site_id,
>     Alan> me.year FROM surveyors_survey_sites me JOIN surveyors
> surveyor
>     Alan> ON surveyor.id = me.surveyor_id ORDER BY surveyor.name ASC )
>     Alan> me ORDER BY surveyor.name
> 
>     Alan> The same code run against a MySQL database works fine.
> 
> I'm working on a patch for this, can you supply the precise error
> emmitted by MSSQL? thanks in advance
> 

DBIx::Class::ResultSet::next(): DBI Exception: DBD::Sybase::st execute
failed: Server message number=107 severity=16 state=2 line=1 server=SQLA4
text=The column prefix 'surveyor' does not match with a table name or alias
name used in the query. Server message number=107 severity=16 state=2 line=1
server=SQLA4 text=The column prefix 'surveyor' does not match with a table
name or alias name used in the query. [for Statement "SELECT TOP 100 PERCENT
id, surveyor_id, survey_site_id, year FROM ( SELECT TOP 10 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 DESC ) me ORDER BY surveyor.name "] at test.pl line
26

Many thanks!

- Alan