| Subject: | resolve dups in the order_by clause |
| Date: | Mon, 16 Jan 2012 08:29:53 +0400 |
| To: | bug-DBIx-Class [...] rt.cpan.org |
| From: | vse paroli <vse.paroli [...] mail.ru> |
== How to change sorting order for MSSQL? ==
================================================================================
order_by => { -desc => [ 'cdrs.calling_num', 'cdrs.date_time' ] }
If I comment out 'order_by => ...' part it still automatically adds 'ORDER BY [cdrs].[calling_num]' or 'ORDER BY `cdrs`.`calling_num`' in the end.
==== MSSQL gives:
SELECT [me].[nomer_ta], [cdrs].[calling_num], [cdrs].[date_time], [cdrs].[dialed_num], [cdrs].[sec_dur] FROM [sprav] [me] LEFT JOIN [cdr] [cdrs] ON [cdrs].[calling_num] = [me].[nomer_ta] WHERE ( ( [me].[otdel] = ? AND [cdrs].[date_time] >= ? AND [cdrs].[date_time] <= ? AND [cdrs].[dialed_num] LIKE ? ) ) ORDER BY [cdrs].[calling_num] DESC, [cdrs].[date_time] DESC, [cdrs].[calling_num]: '34 - УЧАСТОК СВЯЗИ', '2009-01-01 00:00:00', '2012-01-15 23:59:59'
DBIx::Class::ResultSet::next(): DBI Exception: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique. (SQL-42000)
==== MySQL gives:
SELECT `me`.`nomer_ta`, `cdrs`.`calling_num`, `cdrs`.`date_time`, `cdrs`.`dialed_num`, `cdrs`.`sec_dur` FROM `sprav` `me` LEFT JOIN `cdr` `cdrs` ON `cdrs`.`calling_num` = `me`.`nomer_ta` WHERE ( ( `me`.`otdel` = ? AND `cdrs`.`date_time` >= ? AND `cdrs`.`date_time` <= ? ) ) ORDER BY `cdrs`.`calling_num` DESC, `cdrs`.`date_time` DESC, `cdrs`.`calling_num`: '34 - УЧАСТОК СВЯЗИ', '2009-01-01 00:00:00', '2012-01-15 23:59:59'
and produces required results without errors (don't understand why because the query looks identical).
================================================================================
Following is all the code.
================================================================================
package Local::Tables::Cdr;
use base 'DBIx::Class';
use Local::DateTime ();
my @columns = qw(
calling_num
date_time
dialed_num
sec_dur
);
__PACKAGE__->load_components( qw( PK::Auto Core ) );
__PACKAGE__->table( lc +( __PACKAGE__ =~ /(?>.*::)(.*)/g )[0] );
__PACKAGE__->source_name( lc +( __PACKAGE__ =~ /(?>.*::)(.*)/g )[0] );
__PACKAGE__->add_columns(@columns);
__PACKAGE__->set_primary_key(@columns);
__PACKAGE__->inflate_column(
'date_time',
{
inflate => sub {
$_[0] =~ /(.{4})-(.{2})-(.{2}) (.{2}):(.{2})/;
return Local::DateTime->new(
year => $1,
month => int $2,
day => int $3,
hour => int $4,
minute => int $5,
);
},
}
);
1;
================================================================================
package Local::Tables::Sprav;
use base 'DBIx::Class';
my @columns = qw(
nomer_ta
otdel
);
__PACKAGE__->load_components( qw( PK::Auto Core ) );
__PACKAGE__->table( lc +( __PACKAGE__ =~ /(?>.*::)(.*)/g )[0] );
__PACKAGE__->source_name( lc +( __PACKAGE__ =~ /(?>.*::)(.*)/g )[0] );
__PACKAGE__->add_columns( qw( nomer_ta ) );
__PACKAGE__->set_primary_key('nomer_ta');
__PACKAGE__->has_many(
cdrs => 'Local::Tables::Cdr',
{ 'foreign.calling_num' => 'self.nomer_ta' },
);
1;
================================================================================
package Local_Handler::Otdel;
use strict;
use Local::Calc ();
use Local::FormValidator ();
use Local::Template ();
use Local::Util ();
use Local qw( $authen $sprav );
my $TT = new Local::Template title => 'В�‹борка по отделу';
sub process { process $TT @_ }
sub handler { my $r; eval {
$r = shift;
$r->content_type('text/html');
my %args; eval { %args = Local::Util::get_args $r };
$@ and process('read_POST_error'), return 0;
process( 'index', { otdel => $authen->find( $r->user )->otdel } ), return 0
unless $args{section};
my $check = Local::FormValidator::check \%args;
if ( $check->success ) {
if (
$args{section} eq 'index' &&
!grep !defined, @args{ qw( otdel ) }
) {
process 'result/header', \%args;
eval { $r->rflush };
my $sum_minute_mezhgorod = 0;
my $sum_minute_mezhgorod_group = 0;
my $sum_stoimost = 0;
my $sum_stoimost_group = 0;
my $vsyo_uchteno = 1;
my $previous_calling_num;
my $otdel = $authen->find( $r->user )->otdel;
my $cond = {
-and => [
'me.otdel' => $otdel eq '(ВСЕ)' ? $args{otdel} : $otdel,
'cdrs.date_time' => {
'>=',
join( '-', @args{ qw( by bm bd ) } ) . ' 00:00:00',
},
'cdrs.date_time' => {
'<=',
join( '-', @args{ qw( ey em ed ) } ) . ' 23:59:59',
},
],
};
push @{ $$cond{-and} }, 'cdrs.dialed_num' => { 'like', '8%' }
if $args{tolko_mezhgorod};
my $rs = $sprav->search(
$cond,
{
prefetch => 'cdrs',
order_by => { -desc => [ 'cdrs.calling_num', 'cdrs.date_time' ] },
},
);
while ( my $cdrs = $rs->next ) {
my $cdr = $cdrs->cdrs;
while ( my $rec = $cdr->next ) {
Local::Calc::otobrazhaem $rec
and do {
$previous_calling_num ||= $rec->calling_num;
if ( $rec->calling_num ne $previous_calling_num ) {
process 'result/group',
{
previous_calling_num => $previous_calling_num,
sum_minute_mezhgorod_group => $sum_minute_mezhgorod_group,
sum_stoimost_group => $sum_stoimost_group,
};
$sum_stoimost_group = 0;
$sum_minute_mezhgorod_group = 0;
}
my $stoimost = Local::Calc::stoimost $rec;
my $round_minute = Local::Calc::round_minute $rec;
# Суммируем только если определена стоимость:
if ( $stoimost =~ /^\d+(?:\.\d+)?$/ ) {
# Суммируем только минут�‹ за котор�‹е м�‹ платим:
if ($stoimost) {
$sum_minute_mezhgorod += $round_minute;
$sum_stoimost += $stoimost;
$sum_minute_mezhgorod_group += $round_minute;
$sum_stoimost_group += $stoimost;
}
}
else { $vsyo_uchteno = 0 }
eval { print '<tr>' };
eval { print '<td align="center">', $_, '</td>' }
for (
$rec->date_time->dmy,
$rec->date_time->hm,
$rec->calling_num,
$rec->dialed_num,
Local::Calc::dir($rec),
$rec->sec_dur,
$round_minute,
$stoimost,
);
eval { print '</tr>' };
$previous_calling_num = $rec->calling_num;
};
eval { print "\0" };
eval { $r->rflush };
return 0 if $r->connection->aborted; # нажата кнопка Стоп
}
}
process 'result/group',
{
previous_calling_num => $previous_calling_num,
sum_minute_mezhgorod_group => $sum_minute_mezhgorod_group,
sum_stoimost_group => $sum_stoimost_group,
};
process 'result/footer',
{
sum_minute_mezhgorod => $sum_minute_mezhgorod,
sum_stoimost => $sum_stoimost,
vsyo_uchteno => $vsyo_uchteno,
};
}
}
else { process $args{section}, $check->msgs, \%args }
return 0;
}; $@ and print( STDERR '[', scalar localtime, '] [user: ', $r->user, '] ', $@ ), process 'server_error'; return 0 }
1;
================================================================================
package Local;
use strict;
use Exporter 'import';
our @EXPORT_OK = qw( $authen $authz $cdr $sprav );
use Local::Config ();
use Local::Tables ();
my $db =
Local::Tables->connect(
Local::Config::DBI_DSN,
Local::Config::DBI_USER,
Local::Config::DBI_PASS,
{
AutoCommit => 1, # рекомендуется в доках DBIx::Class
RaiseError => 1,
PrintError => 0,
},
{
# quote_char => '`', # DBD::mysql
quote_char => [ qw( [ ] ) ], # DBD::ODBC
name_sep => '.',
},
);
$db->storage->debug(1);
our $authen = $db->resultset('authen');
our $authz = $db->resultset('authz');
our $cdr = $db->resultset('cdr');
our $sprav = $db->resultset('sprav');
1;
================================================================================
package Local::Config;
use constant {
BASE => $INC[0],
# DBI_DSN => 'dbi:mysql:database=test;host=localhost',
DBI_DSN => 'dbi:ODBC:Driver={SQL Server};Server=asu01;Database=usv',
DBI_USER => '...',
DBI_PASS => '........',
};
1;
================================================================================
package Local::Tables;
use base 'DBIx::Class::Schema';
__PACKAGE__->load_classes;
1;
================================================================================
$dbh->do( 'CREATE TABLE sprav (otdel varchar(255),nomer_ta varchar(255))' );
$dbh->do( 'create table cdr (date_time datetime not null,' .
'calling_num varchar(15) not null,' .
'dialed_num varchar(23) not null,sec_dur int not null)' );