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

[David Ihnen]

Tommy Butler wrote:
> 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'  },
>   
Wait a sec.  Your schema below says for table Users these two 
relationships are defined:

__PACKAGE__->has_many(map_user_role => 'CTIweb::Schema::DB::UserRoles',
'user_id');
__PACKAGE__->many_to_many(roles => 'map_user_role', 'role');


None of which is called 'user_roles'.

You can only join relationships - perhaps you're confused and are 
putting table names in where relationships go?

I'd write it more like

my $rs = $c->model('DB::Users')->search
	( { role => 'friend' }
        , { prefetch => 'roles' }
	);

Then I could access through rel name like this, with no messy +select or 
+as.

foreach  my $o ($rs->all) {
  print "friend role id " . $o->roles->role_id . " is role " . 
$o->roles->role . "\n";
}

David

>                   '+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 :)
>
>