select * from users join user_roles on (user_roles. user_id = id) join roles on (roles.id = role_id) where username like '%foo%';

##</code><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 }
            );

##</code><code>##

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;

##</code><code>##

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;

##</code><code>##

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;