MSSQL - problem sorting by joined column while limiting rows

[Alan Humphrey]

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




_______________________________________________
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@...

[Eden Cardim]

>>>>> "Alan" == Alan Humphrey <[hidden email]> 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

_______________________________________________
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@...

[Alan Humphrey]

> -----Original Message-----
> From: Eden Cardim [mailto:[hidden email]]
> 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 <[hidden email]> 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



_______________________________________________
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@...