| Subject: | group_by crashes the multi step join query |
Output of uname -a:
Linux 185.72.34.89.in-addr.arpa 2.6.18-194.26.1.el5PAE #1 SMP Tue Nov 9
13:34:42 EST 2010 i686 i686 i386 GNU/Linux
perl -v:
This is perl 5, version 14, subversion 2 (v5.14.2) built for i686-linux-
thread-multi
I have the dbic "query":
my $companies_rs = $self->app->model->resultset('Food::Schema::Result::CompanyType')
->search_rs(
{
'type' => 'orderer',
'orders.status' => { '!=' => 'paid' },
'company_2.id' => $self->app->sessions->{user}->company()->id(),
},
{
join => { 'company' => { 'users' => { 'orders' => 'company' } } },
prefetch => [ qw( company )],
select => [ { sum => 'orders.total', -as => 'company_total' }, ], #SQL As
columns => [ qw( company.name orders.company_id) ],
as => [qw/company_total/], #used for retrieving the above sql as
#group_by => 'orders.company_id' ,
} );
which works ok, it outputs the MySQL query:
SELECT company.name, orders.company_id, SUM( orders.total ) AS company_total, company.id, company.name,
company.website, company.phone, company.company_type_id
FROM company_types me
LEFT JOIN companies company ON company.company_type_id = me.id
LEFT JOIN users users ON users.company_id = company.id
LEFT JOIN orders orders ON orders.user_id = users.id
LEFT JOIN companies company_2 ON company_2.id = orders.company_id
WHERE (
(
company_2.id = ?
AND orders.status != ?
AND TYPE = ?
)
)
ORDER BY company.company_type_id
However, in order to get the sum for each company for each order, I have to add a GROUP BY company.id or GROUP BY
orders.company_id. If I uncomment the group_by clause from the above dbic query, it generates this SQL
monstruosity:
SELECT company.name, orders.company_id, me.company_total, company.id, company.name, company.website,
company.phone, company.company_type_id
FROM (
SELECT company.name, orders.company_id, SUM( orders.total ) AS company_total
FROM company_types me
LEFT JOIN companies company ON company.company_type_id = me.id
LEFT JOIN users users ON users.company_id = company.id
LEFT JOIN orders orders ON orders.user_id = users.id
LEFT JOIN companies company_2 ON company_2.id = orders.company_id
WHERE (
(
company_2.id = ?
AND orders.status != ?
AND TYPE = ?
)
)
GROUP BY orders.company_id
)me
LEFT JOIN companies company ON company.company_type_id = me.id
LEFT JOIN users users ON users.company_id = company.id
LEFT JOIN orders orders ON orders.user_id = users.id
LEFT JOIN companies company_2 ON company_2.id = orders.company_id
WHERE (
(
company_2.id = ?
AND orders.status != ?
AND TYPE = ?
)
)
ORDER BY company.company_type_id
together with this error stacktrace:
DBI Exception: DBD::mysql::st execute failed: Unknown column 'type' in 'where clause' [for Statement "SELECT
company.name, orders.company_id, me.company_total, company.id, company.name, company.website, company.phone,
company.company_type_id FROM (SELECT company.name, orders.company_id, SUM( orders.total ) AS company_total FROM
company_types me LEFT JOIN companies company ON company.company_type_id = me.id LEFT JOIN users users ON
users.company_id = company.id LEFT JOIN orders orders ON orders.user_id = users.id LEFT JOIN companies company_2
ON company_2.id = orders.company_id WHERE ( ( company_2.id = ? AND orders.status != ? AND type = ? ) ) GROUP BY
orders.company_id) me LEFT JOIN companies company ON company.company_type_id = me.id LEFT JOIN users users ON
users.company_id = company.id LEFT JOIN orders orders ON orders.user_id = users.id LEFT JOIN companies company_2
ON company_2.id = orders.company_id WHERE ( ( company_2.id = ? AND orders.status != ? AND type = ? ) ) ORDER BY
company.company_type_id" with ParamValues: 0='2', 1='paid', 2='orderer', 3='2', 4='paid', 5='orderer'] at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Schema.pm line 1078
DBIx::Class::Schema::throw_exception('Food::Schema=HASH(0x9e2e5c0)', 'DBI Exception: DBD::mysql::st
execute failed: Unknown column ...') called at /usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage.pm line
111
DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::mysql=HASH(0x9e33a40)', 'DBI Exception:
DBD::mysql::st execute failed: Unknown column ...') called at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 1288
DBIx::Class::Storage::DBI::__ANON__('DBD::mysql::st execute failed: Unknown column \'type\' in \'w...',
'DBI::st=HASH(0xaed7368)', undef) called at /usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line
1586
DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::mysql=HASH(0x9e33a40)',
'DBI::db=HASH(0x9f6d338)', 'SELECT company.name, orders.company_id, me.company_total, com...',
'ARRAY(0xaede360)', 'ARRAY(0xaf074c8)') called at /usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm
line 779
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::mysql=HASH(0x9e33a40)', '_dbh_execute',
'SELECT company.name, orders.company_id, me.company_total, com...', 'ARRAY(0xaede360)', 'ARRAY(0xaf074c8)')
called at /usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 1551
DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::mysql=HASH(0x9e33a40)', 'select',
'ARRAY(0xaede270)', 'ARRAY(0xaeec028)', 'HASH(0xaf0d628)', 'HASH(0xa03e2c0)') called at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 2077
DBIx::Class::Storage::DBI::_select('DBIx::Class::Storage::DBI::mysql=HASH(0x9e33a40)',
'ARRAY(0xaf0d3f8)', 'ARRAY(0xaf07928)', 'HASH(0xaf0d628)', 'HASH(0xaf07388)') called at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI/Cursor.pm line 89
DBIx::Class::Storage::DBI::Cursor::_dbh_next('DBIx::Class::Storage::DBI::mysql=HASH(0x9e33a40)',
'DBI::db=HASH(0x9f6d338)', 'DBIx::Class::Storage::DBI::Cursor=HASH(0xaef9a48)') called at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 788
DBIx::Class::Storage::DBI::__ANON__() called at /usr/local/lib/perl5/site_perl/5.14.2/Try/Tiny.pm line 71
eval {...} called at /usr/local/lib/perl5/site_perl/5.14.2/Try/Tiny.pm line 67
Try::Tiny::try('CODE(0xaef97d8)', 'Try::Tiny::Catch=REF(0xaf06e28)') called at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI.pm line 799
DBIx::Class::Storage::DBI::dbh_do(undef, undef, 'DBIx::Class::Storage::DBI::Cursor=HASH(0xaef9a48)')
called at /usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/Storage/DBI/Cursor.pm line 108
DBIx::Class::Storage::DBI::Cursor::next('DBIx::Class::Storage::DBI::Cursor=HASH(0xaef9a48)') called at
/usr/local/lib/perl5/site_perl/5.14.2/DBIx/Class/ResultSet.pm line 1198
DBIx::Class::ResultSet::next('DBIx::Class::ResultSet=HASH(0xaf07b18)') called at
script/../lib/Food/Provider.pm line 129
| Subject: | Order.pm |
package Food::Schema::Result::Order;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 NAME
Food::Schema::Result::Order
=cut
__PACKAGE__->table("orders");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
is_auto_increment: 1
is_nullable: 0
=head2 company_id
data_type: 'integer'
is_nullable: 1
=head2 user_id
data_type: 'integer'
is_nullable: 1
=head2 total
data_type: 'float'
is_nullable: 1
=head2 money_id
data_type: 'integer'
is_nullable: 0
=head2 created_at
data_type: 'datetime'
datetime_undef_if_invalid: 1
is_nullable: 1
=head2 updated_at
data_type: 'bigint'
is_nullable: 1
=head2 status
data_type: 'varchar'
is_nullable: 1
size: 10
=cut
__PACKAGE__->add_columns(
"id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"company_id",
{ data_type => "integer", is_nullable => 1 },
"user_id",
{ data_type => "integer", is_nullable => 1 },
"total",
{ data_type => "float", is_nullable => 1 },
"money_id",
{ data_type => "integer", is_nullable => 0 },
"created_at",
{
data_type => "datetime",
datetime_undef_if_invalid => 1,
is_nullable => 1,
},
"updated_at",
{ data_type => "bigint", is_nullable => 1 },
"status",
{ data_type => "varchar", is_nullable => 1, size => 10 },
);
__PACKAGE__->set_primary_key("id");
# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-11-17 16:54:50
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:J295znQJlQmJGF1gSxb2Mg
use DateTime;
__PACKAGE__->remove_column('created_at');
__PACKAGE__->add_columns(
"created_at",
{
data_type => "datetime",
datetime_undef_if_invalid => 1,
is_nullable => 0,
accessor => '_created_at',
},
);
__PACKAGE__->belongs_to(company => 'Food::Schema::Result::Company', 'company_id');
__PACKAGE__->belongs_to(user => 'Food::Schema::Result::User', 'user_id');
__PACKAGE__->has_many(order_lines => 'Food::Schema::Result::OrderLine', 'order_id');
__PACKAGE__->many_to_many(foods => 'order_lines', 'food');
__PACKAGE__->many_to_many(menus => 'order_lines', 'menu');
sub created_at{
my ($self, $value) = @_;
$self->_created_at( $self->_created_at() || DateTime::Format::MySQL->format_datetime( DateTime->now() ) );
return $self->_created_at();
};
sub new{
my ($self, $attrs) = @_;
$self->created_at();
my $new = $self->next::method($attrs);
return $new;
}
1;
| Subject: | Company.pm |
package Food::Schema::Result::Company;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 NAME
Food::Schema::Result::Company
=cut
__PACKAGE__->table("companies");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
is_auto_increment: 1
is_nullable: 0
=head2 name
data_type: 'varchar'
is_nullable: 1
size: 50
=head2 website
data_type: 'varchar'
is_nullable: 1
size: 50
=head2 phone
data_type: 'varchar'
is_nullable: 1
size: 20
=head2 company_type_id
data_type: 'integer'
is_nullable: 0
=cut
__PACKAGE__->add_columns(
"id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"name",
{ data_type => "varchar", is_nullable => 1, size => 50 },
"website",
{ data_type => "varchar", is_nullable => 1, size => 50 },
"phone",
{ data_type => "varchar", is_nullable => 1, size => 20 },
"company_type_id",
{ data_type => "integer", is_nullable => 0 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("website", ["website"]);
__PACKAGE__->add_unique_constraint("name", ["name"]);
# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-11-10 09:13:33
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Mw3wLkcjppjZmK+ZagG6uA
__PACKAGE__->belongs_to(company_type => 'Food::Schema::Result::CompanyType', 'company_type_id');
__PACKAGE__->has_many(users => 'Food::Schema::Result::User', 'company_id');
__PACKAGE__->has_many(orders => 'Food::Schema::Result::Order', 'company_id');
__PACKAGE__->has_many(menus => 'Food::Schema::Result::Menus', 'company_id');
__PACKAGE__->has_many(foods => 'Food::Schema::Result::Food', 'company_id');
# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;
| Subject: | User.pm |
package Food::Schema::Result::User;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
#use Try::Tiny;
use base 'DBIx::Class::Core';
use Food::Util::Exception;
=head1 NAME
Food::Schema::Result::User
=cut
__PACKAGE__->table("users");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
is_auto_increment: 1
is_nullable: 0
=head2 username
data_type: 'varchar'
is_nullable: 0
size: 20
=head2 password
data_type: 'char'
is_nullable: 0
size: 32
=head2 company_id
data_type: 'integer'
is_nullable: 1
=head2 user_type_id
data_type: 'integer'
is_nullable: 1
=head2 first_name
data_type: 'varchar'
is_nullable: 1
size: 20
=head2 last_name
data_type: 'varchar'
is_nullable: 1
size: 20
=head2 email
data_type: 'varchar'
is_nullable: 0
size: 50
=head2 phone_number
data_type: 'varchar'
is_nullable: 1
size: 20
=head2 amount
data_type: 'float'
default_value: 0
is_nullable: 0
=cut
__PACKAGE__->add_columns(
"id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"username",
{ data_type => "varchar", is_nullable => 0, size => 20 },
"password",
{ data_type => "char", is_nullable => 0, size => 32 },
"company_id",
{ data_type => "integer", is_nullable => 1 },
"user_type_id",
{ data_type => "integer", is_nullable => 1 },
"first_name",
{ data_type => "varchar", is_nullable => 1, size => 20 },
"last_name",
{ data_type => "varchar", is_nullable => 1, size => 20 },
"email",
{ data_type => "varchar", is_nullable => 0, size => 50 },
"phone_number",
{ data_type => "varchar", is_nullable => 1, size => 20 },
"amount",
{ data_type => "float", default_value => 0, is_nullable => 0 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("email", ["email"]);
__PACKAGE__->add_unique_constraint("username", ["username"]);
# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-11-17 17:39:47
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:kbT7RsQ7vTus0GN4y95H/A
use DateTime;
use DateTime::Format::MySQL;
__PACKAGE__->belongs_to(user_type => 'Food::Schema::Result::UserType', 'user_type_id');
__PACKAGE__->belongs_to(company => 'Food::Schema::Result::Company', 'company_id');
__PACKAGE__->has_many(my_operations => 'Food::Schema::Result::Money', 'operated_for_id');
__PACKAGE__->has_many(cashier_operations => 'Food::Schema::Result::Money', 'operated_by_id');
__PACKAGE__->many_to_many(cashier_for => 'cashier_operations', 'owner');
__PACKAGE__->has_many(orders => 'Food::Schema::Result::Order', 'user_id');
__PACKAGE__->many_to_many(order_lines => 'orders', 'order_lines');
sub modify_amount{
my $self = shift;
my $cashier = shift;
my $new_amount = shift;
my $options = shift || {};
$self->throw_exception(Food::Util::Exception->new('Must get a number as amount'), undef) if ( $new_amount !~ /^[+-]?\d*\.?\d*$/gmix );
my $money = {
description => 'no description',
amount => $new_amount,
operated_by_id => $cashier->id(),
# created_at => DateTime::Format::MySQL->format_datetime( DateTime->now() ),
# owner => $self,
%{$options},
};
1;
$self->result_source->schema->txn_do(sub {
$self->create_related('my_operations', $money );
$self->amount($self->amount() + $new_amount);
$self->throw_exception(Food::Util::Exception->new( 'Not enough money for operation' )) if ( $self->amount() < 0 );
$self->update();
});
};
1;
| Subject: | CompanyType.pm |
package Food::Schema::Result::CompanyType;
# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE
use strict;
use warnings;
use base 'DBIx::Class::Core';
=head1 NAME
Food::Schema::Result::CompanyType
=cut
__PACKAGE__->table("company_types");
=head1 ACCESSORS
=head2 id
data_type: 'integer'
is_auto_increment: 1
is_nullable: 0
=head2 type
data_type: 'varchar'
is_nullable: 0
size: 10
=cut
__PACKAGE__->add_columns(
"id",
{ data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
"type",
{ data_type => "varchar", is_nullable => 0, size => 10 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("type", ["type"]);
# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-11-10 09:13:33
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:Q9dsWbi5BRfviP6FvWqREQ
__PACKAGE__->has_many(company => 'Food::Schema::Result::Company', 'company_type_id');
# You can replace this text with custom code or comments, and it will be preserved on regeneration
1;