[Dbix-class] Problems with DBIx:: and SQL JOIN's

[Tommy Butler]

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