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, are very far from correct. (The Problem) ================================================= 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. (The Query) ================================================== select * from users join user_roles on (user_roles. user_id = id) join roles on (roles.id = role_id) where username like '%foo%'; (The Perl Code) ================================================= #You may well note from my code below that my use of DBIx::Class is through the Catalyst framework. # The code below fails with a very long stack trace which basically says 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 } ); ...And My Schema for the three tables used in the above code are thus: (Users.pm) ==================================================== 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:49 # 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; (Roles.pm) ==================================================== 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:49 # 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; (UserRoles.pm) ==================================================== 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:49 # 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 :) -- Tommy _______________________________________________ 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/[email protected]
