Error when using distinct => 1 with select => ...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Error when using distinct => 1 with select => ...

Octavian Rasnita-2
Hello,

I tried to use the following select query, but it gives an error:

use TB::Schema;
$ENV{DBIC_TRACE}++;

my $schema = TB::Schema->connect( "dbi:mysql:intranet", "root" );

my $rs = $schema->resultset('Performance')->search( {}, {
    select => [ { date => 'date_time' } ],
    as => [ 'date' ],
    distinct => 1,
} );

$rs->all;

This gives the following result:

SELECT DATE( date_time ) FROM performance me GROUP BY :
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st
execute failed: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ''
at line 1 [for Statement "SELECT DATE( date_time ) FROM performance me GROUP
BY "] at D:\test_dbic\test.pl line 18

If I replace the line:
    distinct => 1,

with:
    group_by => [ { date => 'date_time' } ],

Then it works fine and it prints the query:
SELECT DATE( date_time ) FROM performance me GROUP BY DATE( date_time ):

Isn't the first way using distinct => 1 recommended, or there is a bug in
DBIC?

The Result class with POD and other non-important columns removed is:

package TB::Schema::Result::Performance;

use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';

__PACKAGE__->load_components("InflateColumn::DateTime");
__PACKAGE__->table("performance");

__PACKAGE__->add_columns(
  "id",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "date_time",
  {
    data_type => "datetime",
    datetime_undef_if_invalid => 1,
    is_nullable => 0,
  },
#... other fields
);

__PACKAGE__->set_primary_key("id");
__PACKAGE__->meta->make_immutable;
1;

Thanks

--Octavian


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