Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 74024
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: vse.paroli [...] mail.ru
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08250



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)' );
Stalling until we solve the underlying problem in the DBIC codebase. Sorry for the delay :(
This is now fixed in the repo master: git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git
Re-stalling until parent RT#74584 is truly resolved
Hopefully this time DBIC 0.08240 nails this issue.
And production version resolving this is now on CPAN.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.