DBIx::ClassとSQLiteで複数のカラムをDISTINCTするとエラーする
DISTINCTで複数のカラムを指定するとエラーした。
以下のようにして、複数のカラムをDISTINCTするコード
$c->stash->{employees} = [$schema->resultset('Employee')->search( {'name' => {'LIKE' => '%hoge%'} }, { select => {distinct => [qw(me.name, me.pr, me.background, me.licence)] } } )];
実行すると以下のようなSQLが発行された
SELECT DISTINCT( me.name, me.pr, me.background, me.licence ) FROM employee me WHERE ( name LIKE ? ): '%hoge%'
で吐かれた、エラーがコレ
[error] rollbacked: DBI Exception: DBD::SQLite::db prepare_cached failed: near ",": syntax error(1) at dbdimp.c line 271 [for Statement "SELECT DISTINCT( me.name, me.pr, me.background, me.licence ) FROM employee me WHERE ( name LIKE ? )"] at /Library/Perl/5.8.8/DBIx/Class/Schema.pm line 954
DBIx::Class::Schema::throw_exception('ResoManage::Model::Schema=HASH(0xd26918)', 'DBI Exception: DBD::SQLite::db prepare_cached failed: near ",...') called at /Library/Perl/5.8.8/DBIx/Class/Storage.pm line 122
DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'DBI Exception: DBD::SQLite::db prepare_cached failed: near ",...') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 846
DBIx::Class::Storage::DBI::__ANON__('DBD::SQLite::db prepare_cached failed: near ",": syntax error...', 'DBI::db=HASH(0xe08044)', 'undef') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1188
DBIx::Class::Storage::DBI::_dbh_sth('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'DBI::db=HASH(0xe08044)', 'SELECT DISTINCT( me.name, me.pr, me.background, me.licence ) ...') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 582
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'CODE(0xdb50e4)', 'SELECT DISTINCT( me.name, me.pr, me.background, me.licence ) ...') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1201
DBIx::Class::Storage::DBI::sth('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'SELECT DISTINCT( me.name, me.pr, me.background, me.licence ) ...', 'select') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 981
DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'DBI::db=HASH(0xe08044)', 'select', 'undef', 'ARRAY(0xe32df0)', 'HASH(0xe33540)', 'ARRAY(0xe32ce8)', 'HASH(0xe32bb0)', 'ARRAY(0xe32cf4)', ...) called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 582
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'CODE(0xdb2d5c)', 'select', 'undef', 'ARRAY(0xe32df0)', 'HASH(0xe33540)', 'ARRAY(0xe32ce8)', 'HASH(0xe32bb0)', 'ARRAY(0xe32cf4)', ...) called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1013
DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'select', 'undef', 'ARRAY(0xe32df0)', 'HASH(0xe33540)', 'ARRAY(0xe32ce8)', 'HASH(0xe32bb0)', 'ARRAY(0xe32cf4)', 'undef', ...) called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1128
DBIx::Class::Storage::DBI::_select('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'ARRAY(0xe32df0)', 'ARRAY(0xe32ce8)', 'HASH(0xe32bb0)', 'HASH(0xe32e98)') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI/MultiDistinctEmulation.pm line 18
DBIx::Class::Storage::DBI::MultiDistinctEmulation::_select('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'ARRAY(0xe32df0)', 'ARRAY(0xe32ce8)', 'HASH(0xe32bb0)', 'HASH(0xe32e98)') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI/Cursor.pm line 121
DBIx::Class::Storage::DBI::Cursor::_dbh_all('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'DBI::db=HASH(0xe08044)', 'DBIx::Class::Storage::DBI::Cursor=HASH(0xe33258)') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 582
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'CODE(0xd90d0c)', 'DBIx::Class::Storage::DBI::Cursor=HASH(0xe33258)') called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI/Cursor.pm line 131
DBIx::Class::Storage::DBI::Cursor::all('DBIx::Class::Storage::DBI::Cursor=HASH(0xe33258)') called at /Library/Perl/5.8.8/DBIx/Class/ResultSet.pm line 1052
DBIx::Class::ResultSet::all('DBIx::Class::ResultSet=HASH(0xe32d9c)') called at /Library/Perl/5.8.8/DBIx/Class/ResultSet.pm line 151
DBIx::Class::ResultSet::search('DBIx::Class::ResultSet=HASH(0xe32b2c)', 'HASH(0xe32bb0)', 'HASH(0xe32c40)') called at /Users/wyrd/Development/repos/ResoManage/trunk/script/../lib/ResoManage/Controller/Employee.pm line 48
ResoManage::Controller::Employee::__ANON__() called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 646
eval {...} called at /Library/Perl/5.8.8/DBIx/Class/Storage/DBI.pm line 634
DBIx::Class::Storage::DBI::txn_do('DBIx::Class::Storage::DBI::SQLite=HASH(0xd4df14)', 'CODE(0xdfe0fc)') called at /Library/Perl/5.8.8/DBIx/Class/Schema.pm line 734
DBIx::Class::Schema::txn_do('ResoManage::Model::Schema=HASH(0xd26918)', 'CODE(0xdfe0fc)') called at /Users/wyrd/Development/repos/ResoManage/trunk/script/../lib/ResoManage/Controller/TextController.pm line 36
eval {...} called at /Users/wyrd/Development/repos/ResoManage/trunk/script/../lib/ResoManage/Controller/TextController.pm line 36
ResoManage::Controller::TextController::db_transact('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)', 'CODE(0xdfe0fc)') called at /Users/wyrd/Development/repos/ResoManage/trunk/script/../lib/ResoManage/Controller/Employee.pm line 100
ResoManage::Controller::Employee::search('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 47
Catalyst::Action::execute('Catalyst::Action=HASH(0xde8c24)', 'ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 32
Catalyst::Action::__ANON__('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst.pm line 1219
eval {...} called at /Library/Perl/5.8.8/Catalyst.pm line 1219
Catalyst::execute('ResoManage=HASH(0xdf7040)', 'ResoManage::Controller::Employee', 'Catalyst::Action=HASH(0xde8c24)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 42
Catalyst::Action::dispatch('Catalyst::Action=HASH(0xde8c24)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Controller.pm line 74
Catalyst::Controller::_ACTION('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 47
Catalyst::Action::execute('Catalyst::Action=HASH(0xde8ab0)', 'ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 32
Catalyst::Action::__ANON__('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst.pm line 1219
eval {...} called at /Library/Perl/5.8.8/Catalyst.pm line 1219
Catalyst::execute('ResoManage=HASH(0xdf7040)', 'ResoManage::Controller::Employee', 'Catalyst::Action=HASH(0xde8ab0)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 42
Catalyst::Action::dispatch('Catalyst::Action=HASH(0xde8ab0)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Dispatcher.pm line 177
Catalyst::Dispatcher::forward('Catalyst::Dispatcher=HASH(0xb700f4)', 'ResoManage=HASH(0xdf7040)', '_ACTION') called at /Library/Perl/5.8.8/Catalyst.pm line 315
Catalyst::forward('ResoManage=HASH(0xdf7040)', '_ACTION') called at /Library/Perl/5.8.8/Catalyst/Controller.pm line 48
Catalyst::Controller::_DISPATCH('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 47
Catalyst::Action::execute('Catalyst::Action=HASH(0xde88f4)', 'ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 32
Catalyst::Action::__ANON__('ResoManage::Controller::Employee=HASH(0xdc3380)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst.pm line 1219
eval {...} called at /Library/Perl/5.8.8/Catalyst.pm line 1219
Catalyst::execute('ResoManage=HASH(0xdf7040)', 'ResoManage::Controller::Employee', 'Catalyst::Action=HASH(0xde88f4)') called at /Library/Perl/5.8.8/Catalyst/Action.pm line 42
Catalyst::Action::dispatch('Catalyst::Action=HASH(0xde88f4)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Dispatcher.pm line 177
Catalyst::Dispatcher::forward('Catalyst::Dispatcher=HASH(0xb700f4)', 'ResoManage=HASH(0xdf7040)', '/employee/_DISPATCH') called at /Library/Perl/5.8.8/Catalyst.pm line 315
Catalyst::forward('ResoManage=HASH(0xdf7040)', '/employee/_DISPATCH') called at /Library/Perl/5.8.8/Catalyst/Dispatcher.pm line 116
Catalyst::Dispatcher::dispatch('Catalyst::Dispatcher=HASH(0xb700f4)', 'ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst.pm line 1168
Catalyst::dispatch('ResoManage=HASH(0xdf7040)') called at /System/Library/Perl/5.8.8/NEXT.pm line 75
NEXT::AUTOLOAD('ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst/Plugin/Static/Simple.pm line 67
Catalyst::Plugin::Static::Simple::dispatch('ResoManage=HASH(0xdf7040)') called at /Library/Perl/5.8.8/Catalyst.pm line 1510
eval {...} called at /Library/Perl/5.8.8/Catalyst.pm line 1501
Catalyst::handle_request('ResoManage') called at /Library/Perl/5.8.8/Catalyst/Engine/HTTP.pm line 380
Catalyst::Engine::HTTP::_handler('Catalyst::Engine::HTTP::Restarter=HASH(0xb50e38)', 'ResoManage', 3000, 'GET', '/employee/search?k=%E6%AD%8C%E3%80%80%E3%83%89%E3%83%A9%E3%81...', 'HTTP/1.1') called at /Library/Perl/5.8.8/Catalyst/Engine/HTTP.pm line 285
Catalyst::Engine::HTTP::run('Catalyst::Engine::HTTP::Restarter=HASH(0xb50e38)', 'ResoManage', 3000, 'undef', 'HASH(0x800c9c)') called at /System/Library/Perl/5.8.8/NEXT.pm line 75
NEXT::AUTOLOAD('Catalyst::Engine::HTTP::Restarter=HASH(0xb50e38)', 'ResoManage', 3000, 'undef', 'HASH(0x800c9c)') called at /Library/Perl/5.8.8/Catalyst/Engine/HTTP/Restarter.pm line 70
Catalyst::Engine::HTTP::Restarter::run('Catalyst::Engine::HTTP::Restarter=HASH(0xb50e38)', 'ResoManage', 3000, 'undef', 'HASH(0x800c9c)') called at /Library/Perl/5.8.8/Catalyst.pm line 1824
Catalyst::run('ResoManage', 3000, 'undef', 'HASH(0x800c9c)') called at script/resomanage_server.pl line 57
[info] Request took 0.150941s (6.625/s)
sqlite> SELECT DISTINCT( me.name, me.pr, me.background, me.licence ) FROM employee me WHERE ( name LIKE '%hoge%'); SQL error: near ",": syntax error
DISTINCTの括弧をとって実行すると、すんなり通った。ってことで、ソースを追っていったり調べたりしたところ。
DBIx::Class::Storage::DBIクラスが書かれてるファイルの_recurse_fieldsを書き換えたら動いた。
sub _recurse_fields {
my ($self, $fields, $params) = @_;
my $ref = ref $fields;
return $self->_quote($fields) unless $ref;
return $$fields if $ref eq 'SCALAR';
if ($ref eq 'ARRAY') {
return join(', ', map {
$self->_recurse_fields($_)
.(exists $self->{rownum_hack_count} && !($params && $params->{no_rownum_hack})
? ' AS col'.$self->{rownum_hack_count}++
: '')
} @$fields);
} elsif ($ref eq 'HASH') {
foreach my $func (keys %$fields) {
- return $self->_sqlcase($func)
- .'( '.$self->_recurse_fields($fields->{$func}).' )';
+ if($func eq 'distinct') {
+ return $self->_sqlcase($func)
+ .' '.$self->_recurse_fields($fields->{$func});
+ } else {
+ return $self->_sqlcase($func)
+ .'( '.$self->_recurse_fields($fields->{$func}).' )';
+ }
}
}
}
distinctの時だけ括弧を取っ払った。明らかに力技。この方法は良くないと分かっているので正しい解決方法あったら教えてください。
追記
前に、こんな事をして対策してましたが今はgroup_byを使ってます。
