Skip Menu |
 

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

Report information
The Basics
Id: 72556
Status: rejected
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: TCONST [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 0.08195
Fixed in: (no value)



Subject: group_by crashes the multi step join query
Download (untitled) / with headers
text/plain 6.6k
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
Download Order.pm
text/x-perl 2.4k
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
Download Company.pm
text/x-perl 1.7k
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
Download User.pm
text/x-perl 3.4k
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
Download CompanyType.pm
text/x-perl 1k
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;
Download (untitled) / with headers
text/plain 5.1k
On Sat Nov 19 15:37:04 2011, TCONST wrote: Show quoted text
> 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 = ? > )se > ) > ORDER BY company.company_type_id > > together with this error stacktrace: > > DBI Exception: DBD::mysql::st execute failed: Unknown column 'type' in > 'where clause'...
You did not supply the most important part - your DBIC version. In any case I think I see the problem (I see *a* problem, whether this is the only problem you have or not is a matter of testing). In fact DBIC is behaving entirely correctly here. Several points: Your CompanyType has_many 'company'. This makes no semantical sense - you has_many 'companies' (note the S for *many*). I shall refer to this relationship as companieS from now on, as it will be confusing otherwise. You asked DBIC to bring you back comapnies, but at the same time you asked it to aggregate the main resultset so that your SUM will work properly. Since the aggregate will completely collapse/flatten the prefetch you requested (* see next paragraph), DBIC has to find a different way. It constructs a subquery representing just your main resultset, constricted/aggregated the way you asked. Then it takes this pre-canned resultset, and executes a prefetch on it - tadaaa you get exactly what you asked for. Note that what you are asking for by the way is generally illegal - a selection needs to match the group clause. Mysql tries hard to guess what you meant, which makes it appear like things are working. If however you think a bit about what does it mean to group by the belongs_to of a has_many, and what happens to the intermediate multi-results in the middle - you will start getting the picture that mysql is being cock by even letting you do this. To get out of such trouble in the future (i.e. to get better error messaging) it is strongly recommended to use mysql strict mode as described here: http://search.cpan.org/~abraxxa/DBIx-Class-0.08195/lib/DBIx/Class/Storage/DBI/mysql.pm#set_strict_mode So with this out of the way - why didn't this work? The problem is that you did not qualify your 'type' column in the condition. DBIC tried to guess where did it come from - but it failed. So it did the only thing it could - pushed it through unqualified, and without its native source joined in either (since it could not reliably determine which source that was). Qualify the TYPE in your condition, and everything shoul start working quite magically. Let us know how did this work for you. Cheers
Download (untitled) / with headers
text/plain 9.6k
Hi again, First of all let me express a wow for your quick and detailed response. You are right with the semantic nonsense of the relation from CompanyType to Company - thx for pointing that out. I modified that in the CompanyType schema file, and I got an error saying that "No such relationship company on CompanyType" when accessing $companies_rs->next(). I checked the code, relationships with/from other schemas and I coouldn't find any references to CompanyType->company. However, if I understood correctly from your reply, that was just a semantic issue, so I let both relationships in the Schema: __PACKAGE__->has_many(companies => 'Food::Schema::Result::Company', 'company_type_id'); __PACKAGE__->has_many(company => 'Food::Schema::Result::Company', 'company_type_id'); And modified the dbic query to: my $companies_rs = $self->app->model->resultset('Food::Schema::Result::CompanyType') ->search_rs( { 'type' => 'orderer', 'orders.status' => { '!=' => 'paid' }, 'company.id' => $self->app->sessions->{user}->company()->id(), }, { join => { 'companies' => { 'users' => { 'orders' => 'company' } } }, prefetch => [ qw( company )], select => [ { sum => 'orders.total', -as => 'company_total' }, ], #SQL As columns => [ qw( companies.name orders.company_id) ], as => [qw/company_total/], #used for retrieving the above sql as #group_by => 'orders.company_id' , } ); This worked as yesterday. Again, when I uncommented the group_by clause, yesterday's behavior reproduced. Unfortunately, the same errors appeared when I qualified the type column: "Unknown column 'me.type' in 'where clause'" and "Unknown column 'companies.type' in 'where clause'". I remember that yesterday I also tried to qualify the type column, getting the same errors (sorry for not mentioning that then). The version of the dbic query that I sent you yesterday (with the unqualified type column) was so because I expected that an unqualified column to be default qualified to the main ResultSet table alias ('Food::Schema::Result::CompanyType' in my case) - this would be a nice to have feature. Regarding the expected (by me at least) behavior for the group_by attribute in the dbic query - I expected that clause to simply be transformed in an "GROUP BY orders.company_id" string appended to the previously generated SQL query. The version of DBIC I use is the latest one (I installed DBIC a couple of weeks before from cpan). Regarding the logic of the MySQL query, I want to get the SUM of the AMOUNT of each ORDER for each COMPANY that has the TYPE 'orderer'. So, I took each company of type orderer, each order of that, group by company and computed the sum of the amount column. However, yesterday I managed to find a workaround for achieving what I needed by working with 2 resultsets: - one for each company with type 'orderer': my $companies_rs = $self->app->model->resultset('Food::Schema::Result::Company') ->search_rs( { 'company_type.type' => 'orderer', }, { join => ['company_type'], } ); - and for each of the users of the above companies, get the SUM of the amount from orders: my $comp_orders = $self->app->model->resultset('Food::Schema::Result::User') ->search_rs( { 'me.company_id' => $company->{id}, 'orders.status'=> { '!=' => 'paid' }, 'company.id' => $self->app->sessions->{user}->company()->id(), }, { join => { 'orders' => 'company' }, prefetch => [ qw( orders company ) ], select => [ { sum => 'orders.total', -as => 'company_total' }, ], #SQL As columns => [ qw( company.name orders.company_id) ], as => [qw/company_total/], } ); The reason of wanting to solve that in a single query is because of the wonderful feature that DBIC has by working with result sets instead of only individual rows - and I still think it is (or should be) possible to do that. Regards, Tudor On Sat Nov 19 18:38:34 2011, RIBASUSHI wrote: Show quoted text
> On Sat Nov 19 15:37:04 2011, TCONST wrote:
> > 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 = ? > > )se > > ) > > ORDER BY company.company_type_id > > > > together with this error stacktrace: > > > > DBI Exception: DBD::mysql::st execute failed: Unknown column 'type'
> in
> > 'where clause'...
> > You did not supply the most important part - your DBIC version. In any > case I think I see the problem (I see *a* problem, whether this is the > only problem you have or not is a matter of testing). In fact DBIC is > behaving entirely correctly here. Several points: > > Your CompanyType has_many 'company'. This makes no semantical sense - > you has_many 'companies' (note the S for *many*). I shall refer to > this > relationship as companieS from now on, as it will be confusing > otherwise. > > You asked DBIC to bring you back comapnies, but at the same time you > asked it to aggregate the main resultset so that your SUM will work > properly. Since the aggregate will completely collapse/flatten the > prefetch you requested (* see next paragraph), DBIC has to find a > different way. It constructs a subquery representing just your main > resultset, constricted/aggregated the way you asked. Then it takes > this > pre-canned resultset, and executes a prefetch on it - tadaaa you get > exactly what you asked for. > > Note that what you are asking for by the way is generally illegal - a > selection needs to match the group clause. Mysql tries hard to guess > what you meant, which makes it appear like things are working. If > however you think a bit about what does it mean to group by the > belongs_to of a has_many, and what happens to the intermediate > multi-results in the middle - you will start getting the picture that > mysql is being cock by even letting you do this. To get out of such > trouble in the future (i.e. to get better error messaging) it is > strongly recommended to use mysql strict mode as described here: > http://search.cpan.org/~abraxxa/DBIx-Class- > 0.08195/lib/DBIx/Class/Storage/DBI/mysql.pm#set_strict_mode > > So with this out of the way - why didn't this work? The problem is > that > you did not qualify your 'type' column in the condition. DBIC tried to > guess where did it come from - but it failed. So it did the only thing > it could - pushed it through unqualified, and without its native > source > joined in either (since it could not reliably determine which source > that was). Qualify the TYPE in your condition, and everything shoul > start working quite magically. > > Let us know how did this work for you. > > Cheers
Download (untitled) / with headers
text/plain 5.5k
On Sun Nov 20 14:07:05 2011, TCONST wrote: Show quoted text
> Hi again, > > First of all let me express a wow for your quick and detailed > response. > > You are right with the semantic nonsense of the relation from > CompanyType to Company - thx for pointing > that out. > I modified that in the CompanyType schema file, and I got an error > saying that "No such relationship > company on CompanyType" when accessing $companies_rs->next(). I > checked the code, relationships with/from > other schemas and I coouldn't find any references to CompanyType-
> >company.
> > However, if I understood correctly from your reply, that was just a > semantic issue, so I let both > relationships in the Schema: > > __PACKAGE__->has_many(companies => 'Food::Schema::Result::Company', > 'company_type_id'); > __PACKAGE__->has_many(company => 'Food::Schema::Result::Company', > 'company_type_id'); >
When you say columns => [ qw( companies.name... You are also referring to a relationship. When grepping for the relationship name - search for all occurances. Show quoted text
> And modified the dbic query to: > > my $companies_rs = $self->app->model-
> >resultset('Food::Schema::Result::CompanyType')
> ->search_rs( > { > > 'type' => 'orderer', > 'orders.status' => { '!=' => 'paid' }, > 'company.id' => $self->app->sessions->{user}->company()-
> >id(),
> }, > { > join => { 'companies' => { 'users' => { 'orders' => > 'company' } } }, > prefetch => [ qw( company )], > select => [ { sum => 'orders.total', -as => > 'company_total' }, ], #SQL As > columns => [ qw( companies.name orders.company_id) ], > as => [qw/company_total/], #used for retrieving the > above sql as > #group_by => 'orders.company_id' , > } ); > > This worked as yesterday. > Again, when I uncommented the group_by clause, yesterday's behavior > reproduced. > Unfortunately, the same errors appeared when I qualified the type > column: > "Unknown column 'me.type' in 'where clause'" and > "Unknown column 'companies.type' in 'where clause'".
I will need to look into this. But please keep reading: Show quoted text
> I remember that yesterday I also tried to qualify the type column, > getting the same errors (sorry for not > mentioning that then). > > The version of the dbic query that I sent you yesterday (with the > unqualified type column) was so because > I expected that an unqualified column to be default qualified to the > main ResultSet table alias > ('Food::Schema::Result::CompanyType' in my case) - this would be a > nice to have feature.
Unfortunately this will never happen ue to backcompat issues. Show quoted text
> Regarding the expected (by me at least) behavior for the group_by > attribute in the dbic query - I > expected that clause to simply be transformed in an "GROUP BY > orders.company_id" string appended to the > previously generated SQL query.
Show quoted text
> The version of DBIC I use is the latest one (I installed DBIC a couple > of weeks before from cpan).
In the future please give the actual version number: you have no idea how often people end up using a stale cpan mirror. I will assume you are using 0.08195 Show quoted text
> Regarding the logic of the MySQL query, I want to get the SUM of the > AMOUNT of each ORDER for each > COMPANY that has the TYPE 'orderer'. So, I took each company of type > orderer, each order of that, group > by company and computed the sum of the amount column. > > However, yesterday I managed to find a workaround for achieving what I > needed by working with 2 > resultsets: > - one for each company with type 'orderer': > > my $companies_rs = $self->app->model-
> >resultset('Food::Schema::Result::Company')
> ->search_rs( > { > 'company_type.type' => 'orderer', > }, > { > join => ['company_type'], > } > ); > - and for each of the users of the above companies, get the SUM of the > amount from orders: > my $comp_orders = $self->app->model-
> >resultset('Food::Schema::Result::User')
> ->search_rs( > { > 'me.company_id' => $company->{id}, > 'orders.status'=> { '!=' => 'paid' }, > 'company.id' => $self->app->sessions->{user}->company()-
> >id(),
> > }, > { > join => { 'orders' => 'company' }, > prefetch => [ qw( orders company ) ], > select => [ { sum => 'orders.total', -as => > 'company_total' }, ], #SQL As > columns => [ qw( company.name orders.company_id) ], > as => [qw/company_total/], > } > ); > > The reason of wanting to solve that in a single query is because of > the wonderful feature that DBIC has > by working with result sets instead of only individual rows - and I > still think it is (or should be) > possible to do that.
Sigh. No it shouldn't - you are still not understanding what you are asking of DBIC. Since on top of everything else you are also asking for a *PREFETCH OF A HAS_MANY*, you in essence have: 'artist' has_many 'cds' belongs_to 'genre', and you are trying to prefetch cds. So you get something like: SELECT *.artist, *.cds FROM artist LEFT JOIN cds ... LEFT JOIN genre Once you GROUP BY something from genre. - what do you think happens to the entire result, especially what happens to the individual cds with identical genres? *everything* is collapsed down to some random undefined state. No more prefetch. If you take the prefetch out - everything will work "as you expect". But what you ask of DBIC currently does not match what you think you are.
Download (untitled) / with headers
text/plain 6.4k
just read what prefetch is actually doing - and is not the same thing as to what I was expecting - I put it there because i thought it only puts all the columns in the SELECT clause - removing it, indeed does what I want. The transformation of the query from some simple joins to the complex select from subselect by simply adding a group by clause made me think that it's a bug. Thank you very much for helping me clarifying this out and sorry for my hurry in reporting this as a bug. Cheers, Tudor On Sun Nov 20 15:21:35 2011, RIBASUSHI wrote: Show quoted text
> On Sun Nov 20 14:07:05 2011, TCONST wrote:
> > Hi again, > > > > First of all let me express a wow for your quick and detailed > > response. > > > > You are right with the semantic nonsense of the relation from > > CompanyType to Company - thx for pointing > > that out. > > I modified that in the CompanyType schema file, and I got an error > > saying that "No such relationship > > company on CompanyType" when accessing $companies_rs->next(). I > > checked the code, relationships with/from > > other schemas and I coouldn't find any references to CompanyType-
> > >company.
> > > > However, if I understood correctly from your reply, that was just a > > semantic issue, so I let both > > relationships in the Schema: > > > > __PACKAGE__->has_many(companies => 'Food::Schema::Result::Company', > > 'company_type_id'); > > __PACKAGE__->has_many(company => 'Food::Schema::Result::Company', > > 'company_type_id'); > >
> > When you say > > columns => [ qw( companies.name... > > You are also referring to a relationship. When grepping for the > relationship name - search for all occurances. >
> > And modified the dbic query to: > > > > my $companies_rs = $self->app->model-
> > >resultset('Food::Schema::Result::CompanyType')
> > ->search_rs( > > { > > > > 'type' => 'orderer', > > 'orders.status' => { '!=' => 'paid' }, > > 'company.id' => $self->app->sessions->{user}->company()-
> > >id(),
> > }, > > { > > join => { 'companies' => { 'users' => { 'orders' => > > 'company' } } }, > > prefetch => [ qw( company )], > > select => [ { sum => 'orders.total', -as => > > 'company_total' }, ], #SQL As > > columns => [ qw( companies.name orders.company_id) ], > > as => [qw/company_total/], #used for retrieving the > > above sql as > > #group_by => 'orders.company_id' , > > } ); > > > > This worked as yesterday. > > Again, when I uncommented the group_by clause, yesterday's behavior > > reproduced. > > Unfortunately, the same errors appeared when I qualified the type > > column: > > "Unknown column 'me.type' in 'where clause'" and > > "Unknown column 'companies.type' in 'where clause'".
> > I will need to look into this. But please keep reading: >
> > I remember that yesterday I also tried to qualify the type column, > > getting the same errors (sorry for not > > mentioning that then). > > > > The version of the dbic query that I sent you yesterday (with the > > unqualified type column) was so because > > I expected that an unqualified column to be default qualified to the > > main ResultSet table alias > > ('Food::Schema::Result::CompanyType' in my case) - this would be a > > nice to have feature.
> > Unfortunately this will never happen ue to backcompat issues. >
> > Regarding the expected (by me at least) behavior for the group_by > > attribute in the dbic query - I > > expected that clause to simply be transformed in an "GROUP BY > > orders.company_id" string appended to the > > previously generated SQL query.
> >
> > The version of DBIC I use is the latest one (I installed DBIC a couple > > of weeks before from cpan).
> > In the future please give the actual version number: you have no idea > how often people end up using a stale cpan mirror. I will assume you are > using 0.08195 >
> > Regarding the logic of the MySQL query, I want to get the SUM of the > > AMOUNT of each ORDER for each > > COMPANY that has the TYPE 'orderer'. So, I took each company of type > > orderer, each order of that, group > > by company and computed the sum of the amount column. > > > > However, yesterday I managed to find a workaround for achieving what I > > needed by working with 2 > > resultsets: > > - one for each company with type 'orderer': > > > > my $companies_rs = $self->app->model-
> > >resultset('Food::Schema::Result::Company')
> > ->search_rs( > > { > > 'company_type.type' => 'orderer', > > }, > > { > > join => ['company_type'], > > } > > ); > > - and for each of the users of the above companies, get the SUM of the > > amount from orders: > > my $comp_orders = $self->app->model-
> > >resultset('Food::Schema::Result::User')
> > ->search_rs( > > { > > 'me.company_id' => $company->{id}, > > 'orders.status'=> { '!=' => 'paid' }, > > 'company.id' => $self->app->sessions->{user}->company()-
> > >id(),
> > > > }, > > { > > join => { 'orders' => 'company' }, > > prefetch => [ qw( orders company ) ], > > select => [ { sum => 'orders.total', -as => > > 'company_total' }, ], #SQL As > > columns => [ qw( company.name orders.company_id) ], > > as => [qw/company_total/], > > } > > ); > > > > The reason of wanting to solve that in a single query is because of > > the wonderful feature that DBIC has > > by working with result sets instead of only individual rows - and I > > still think it is (or should be) > > possible to do that.
> > Sigh. No it shouldn't - you are still not understanding what you are > asking of DBIC. Since on top of everything else you are also asking for > a *PREFETCH OF A HAS_MANY*, you in essence have: > > 'artist' has_many 'cds' belongs_to 'genre', and you are trying to > prefetch cds. So you get something like: > > SELECT *.artist, *.cds FROM artist LEFT JOIN cds ... LEFT JOIN genre > > Once you GROUP BY something from genre. - what do you think happens to > the entire result, especially what happens to the individual cds with > identical genres? *everything* is collapsed down to some random > undefined state. No more prefetch. > > If you take the prefetch out - everything will work "as you expect". But > what you ask of DBIC currently does not match what you think you are. >
What is the procedure for closing this ticket? I think you can reject this, as it is not a bug On Sun Nov 20 15:21:35 2011, RIBASUSHI wrote: Show quoted text
> On Sun Nov 20 14:07:05 2011, TCONST wrote:
> > Hi again, > > > > First of all let me express a wow for your quick and detailed > > response. > > > > You are right with the semantic nonsense of the relation from > > CompanyType to Company - thx for pointing > > that out. > > I modified that in the CompanyType schema file, and I got an error > > saying that "No such relationship > > company on CompanyType" when accessing $companies_rs->next(). I > > checked the code, relationships with/from > > other schemas and I coouldn't find any references to CompanyType-
> > >company.
> > > > However, if I understood correctly from your reply, that was just a > > semantic issue, so I let both > > relationships in the Schema: > > > > __PACKAGE__->has_many(companies => 'Food::Schema::Result::Company', > > 'company_type_id'); > > __PACKAGE__->has_many(company => 'Food::Schema::Result::Company', > > 'company_type_id'); > >
> > When you say > > columns => [ qw( companies.name... > > You are also referring to a relationship. When grepping for the > relationship name - search for all occurances. >
> > And modified the dbic query to: > > > > my $companies_rs = $self->app->model-
> > >resultset('Food::Schema::Result::CompanyType')
> > ->search_rs( > > { > > > > 'type' => 'orderer', > > 'orders.status' => { '!=' => 'paid' }, > > 'company.id' => $self->app->sessions->{user}->company()-
> > >id(),
> > }, > > { > > join => { 'companies' => { 'users' => { 'orders' => > > 'company' } } }, > > prefetch => [ qw( company )], > > select => [ { sum => 'orders.total', -as => > > 'company_total' }, ], #SQL As > > columns => [ qw( companies.name orders.company_id) ], > > as => [qw/company_total/], #used for retrieving
the Show quoted text
> > above sql as > > #group_by => 'orders.company_id' , > > } ); > > > > This worked as yesterday. > > Again, when I uncommented the group_by clause, yesterday's behavior > > reproduced. > > Unfortunately, the same errors appeared when I qualified the type > > column: > > "Unknown column 'me.type' in 'where clause'" and > > "Unknown column 'companies.type' in 'where clause'".
> > I will need to look into this. But please keep reading: >
> > I remember that yesterday I also tried to qualify the type column, > > getting the same errors (sorry for not > > mentioning that then). > > > > The version of the dbic query that I sent you yesterday (with the > > unqualified type column) was so because > > I expected that an unqualified column to be default qualified to the > > main ResultSet table alias > > ('Food::Schema::Result::CompanyType' in my case) - this would be a > > nice to have feature.
> > Unfortunately this will never happen ue to backcompat issues. >
> > Regarding the expected (by me at least) behavior for the group_by > > attribute in the dbic query - I > > expected that clause to simply be transformed in an "GROUP BY > > orders.company_id" string appended to the > > previously generated SQL query.
> >
> > The version of DBIC I use is the latest one (I installed DBIC a
couple Show quoted text
> > of weeks before from cpan).
> > In the future please give the actual version number: you have no idea > how often people end up using a stale cpan mirror. I will assume you
are Show quoted text
> using 0.08195 >
> > Regarding the logic of the MySQL query, I want to get the SUM of the > > AMOUNT of each ORDER for each > > COMPANY that has the TYPE 'orderer'. So, I took each company of type > > orderer, each order of that, group > > by company and computed the sum of the amount column. > > > > However, yesterday I managed to find a workaround for achieving what
I Show quoted text
> > needed by working with 2 > > resultsets: > > - one for each company with type 'orderer': > > > > my $companies_rs = $self->app->model-
> > >resultset('Food::Schema::Result::Company')
> > ->search_rs( > > { > > 'company_type.type' => 'orderer', > > }, > > { > > join => ['company_type'], > > } > > ); > > - and for each of the users of the above companies, get the SUM of
the Show quoted text
> > amount from orders: > > my $comp_orders = $self->app->model-
> > >resultset('Food::Schema::Result::User')
> > ->search_rs( > > { > > 'me.company_id' => $company->{id}, > > 'orders.status'=> { '!=' => 'paid' }, > > 'company.id' => $self->app->sessions->{user}->company()-
> > >id(),
> > > > }, > > { > > join => { 'orders' => 'company' }, > > prefetch => [ qw( orders company ) ], > > select => [ { sum => 'orders.total', -as => > > 'company_total' }, ], #SQL As > > columns => [ qw( company.name orders.company_id) ], > > as => [qw/company_total/], > > } > > ); > > > > The reason of wanting to solve that in a single query is because of > > the wonderful feature that DBIC has > > by working with result sets instead of only individual rows - and I > > still think it is (or should be) > > possible to do that.
> > Sigh. No it shouldn't - you are still not understanding what you are > asking of DBIC. Since on top of everything else you are also asking
for Show quoted text
> a *PREFETCH OF A HAS_MANY*, you in essence have: > > 'artist' has_many 'cds' belongs_to 'genre', and you are trying to > prefetch cds. So you get something like: > > SELECT *.artist, *.cds FROM artist LEFT JOIN cds ... LEFT JOIN genre > > Once you GROUP BY something from genre. - what do you think happens to > the entire result, especially what happens to the individual cds with > identical genres? *everything* is collapsed down to some random > undefined state. No more prefetch. > > If you take the prefetch out - everything will work "as you expect".
But Show quoted text
> what you ask of DBIC currently does not match what you think you are. >
Subject: Re: [rt.cpan.org #72556] group_by crashes the multi step join query
Date: Mon, 21 Nov 2011 08:49:39 +0100
To: bug-DBIx-Class [...] rt.cpan.org
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 354b
Tudor Constantin via RT wrote: Show quoted text
> Queue: DBIx-Class > Ticket <URL: https://rt.cpan.org/Ticket/Display.html?id=72556 > > > What is the procedure for closing this ticket? I think you can reject > this, as it is not a bug >
It is a bug, given that a fully qualified far-side group_by did not work. I just didn't get a chance to look into it yet.
Download (untitled) / with headers
text/plain 168b
Looking more into this bug it is clear that the second search() invocantion was laso incorrect - there is still an unqualified type column in there. Closing as ENOTABUG


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.