Friends,
I'm having serious pains getting DBIx::Class to JOIN on anything. I'm dealing with one situation in particular, but my problems are not limited to this. I'd like to learn how to fix this one problem so I can go on to fix others. In the description of my problem I will provide the SQL query (which works), the DBIx::Class code call (which doesn't work), and the schemata that correctly correspond to my database tables.
Could someone provide some insight or a few lines of working code for the query I'm trying to make? Your help is so much appreciated. I am very confident that my attempts (including the example I provide here and the many others I do not) are very far from correct, considering the great amount of time I've invested in failed attempts at getting this right. It might be worth noting that I have also tried various methods that refer directly to the "map_user_role" relation that is defined in the schemata.
The query below seems very straightforward no? And yet in DBIx::Class it has become an insurmountable chore for me. I've spent more time fiddling around with this specific issue than I'd ever admit. Following the official documentation has only led to more and more confusion. =>( http://search.cpan.org/~ribasushi/DBIx-Class-0.08103/lib/DBIx/Class/Manual/Joining.pod#USING_JOINS ). I'm now asking for help at PerlMonks.org and on the DBIx::Class mailing list.
select * from users join user_roles on (user_roles. user_id = id) join + roles on (roles.id = role_id) where username like '%foo%';
#You may well note from my code below that my use of DBIx::Class is th +rough the Catalyst framework. # The code below fails with a very long stack trace which basically sa +ys that my SQL syntax is wrong. $c->model('DB::Users')->search( { role => 'friend' }, { join => { 'user_roles' => 'roles' }, '+select' => [ 'user_roles.role_id', 'roles.role' ], '+as' => [ 'roleid', 'role' ], }, { rows => 10 } );
A very-abridged example of one kind of error that my code produces is as follows: DBI Exception: DBD::mysql::st execute failed: Unknown column 'roles.role' in 'where clause' (((for Statement "SELECT COUNT( * ) FROM users me WHERE ( roles.role = ? )" with ParamValues: 0='Guard'))) at /usr/share/perl5/DBIx/Class/Schema.pm line 945
...And My Schema for the three tables used in the above code are thus:
package CTIweb::Schema::DB::Users; use strict; use warnings; use base 'DBIx::Class'; __PACKAGE__->load_components("Core"); __PACKAGE__->table("users"); __PACKAGE__->add_columns( "id", { data_type => "INT", default_value => undef, is_nullable => 0, size + => 10 }, "username", { data_type => "VARCHAR", default_value => undef, is_nullable => 1, size => 255, }, "password", { data_type => "VARCHAR", default_value => undef, is_nullable => 1, size => 255, }, "active", { data_type => "TINYINT", default_value => undef, is_nullable => 1, +size => 1 }, ); __PACKAGE__->set_primary_key("id"); # Created by DBIx::Class::Schema::Loader v0.04006 @ 2009-06-05 07:45:4 +9 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7y/vHA7Praq6Pfdk3BsP9g __PACKAGE__->has_many(map_user_role => 'CTIweb::Schema::DB::UserRoles' +, 'user_id'); __PACKAGE__->many_to_many(roles => 'map_user_role', 'role'); 1;
package CTIweb::Schema::DB::Roles; use strict; use warnings; use base 'DBIx::Class'; __PACKAGE__->load_components("Core"); __PACKAGE__->table("roles"); __PACKAGE__->add_columns( "id", { data_type => "INT", default_value => undef, is_nullable => 0, size + => 10 }, "role", { data_type => "VARCHAR", default_value => undef, is_nullable => 1, size => 255, }, ); __PACKAGE__->set_primary_key("id"); # Created by DBIx::Class::Schema::Loader v0.04006 @ 2009-06-05 07:45:4 +9 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:4YCojBBfGJ3Jbr5JOKB6/w __PACKAGE__->has_many(map_user_role => 'CTIweb::Schema::DB::UserRoles' +, 'role_id'); __PACKAGE__->has_many(map_acl_role => 'CTIweb::Schema::DB::AclRoles', +'role_id'); 1;
package CTIweb::Schema::DB::UserRoles; use strict; use warnings; use base 'DBIx::Class'; __PACKAGE__->load_components("Core"); __PACKAGE__->table("user_roles"); __PACKAGE__->add_columns( "user_id", { data_type => "INT", default_value => undef, is_nullable => 0, size + => 10 }, "role_id", { data_type => "INT", default_value => undef, is_nullable => 0, size + => 10 }, ); __PACKAGE__->set_primary_key("user_id", "role_id"); # Created by DBIx::Class::Schema::Loader v0.04006 @ 2009-06-05 07:45:4 +9 # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:oNLOSz4mdPtk5TSEtfwG1w __PACKAGE__->belongs_to(user => 'CTIweb::Schema::DB::Users', 'user_id' +); __PACKAGE__->belongs_to(role => 'CTIweb::Schema::DB::Roles', 'role_id' +); 1;
So how about it? Any takers? What should I be doing in order to get a resultset, and even better, a correct one :)
In reply to Problems with DBIx::Class and SQL JOIN's by Tommy
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |