Tommy has asked for the wisdom of the Perl Monks concerning the following question:

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 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%';

[download]

The Perl Code

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

[download]

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

The Schemata

...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: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;

[download]

(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: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;

[download]

(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: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;

[download]

So how about it? Any takers? What should I be doing in order to get a resultset, and even better, a correct one :)

--
Tommy

Replies are listed 'Best First'.
Re: Problems with DBIx::Class and SQL JOIN's
by Your Mother (Archbishop) on Jun 09, 2009 at 06:34 UTC

    I think the trouble you're having is your relationships are a bit squirrelly. The docs on many_to_many have in the past contained either not enough info or some misleading info. I had trouble with this spot too. Hence-

    I have also tried various methods that refer directly to the "map_user_role" relation that is defined in the schemata.

    -isn't working the way you'd like and deserve to see. So, this one-

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

    [download]

    -should be (I think, I'm not testing it, just eyeballing it and I changed your "map_user_role" to a straight "user_roles" which is just a personal preference.)- 

    # User
__PACKAGE__->many_to_many( roles => "user_roles", "role_id" );
__PACKAGE__->has_many(
  "user_roles",
  "CTIweb::Schema::DB::UserRoles",
  { "foreign.user_id" => "self.id" },
)

    [download]

    And then-

    # Role
__PACKAGE__->has_many(map_user_role => 'CTIweb::Schema::DB::UserRoles'
+, 'role_id');
__PACKAGE__->has_many(map_acl_role => 'CTIweb::Schema::DB::AclRoles', 
+'role_id'); # I'm not dealing with this one below

# ------------ becomes -----------

__PACKAGE__->has_many(
  "user_roles",
  "CTIweb::Schema::DB::UserRoles",
  { "foreign.role_id" => "self.id" },
);
__PACKAGE__->many_to_many(users => 'user_roles', 'user_id');

    [download]

    Your UserRoles belongs_to stuff is right.

    Give that a whirl and the "it just works" stuff should just start working. If it doesn't, please post an SQL schema dump (on your scratchpad) if you can so I can autogenerate stuff and test it for real instead of eyeballing it. :)

    I know learning DBIC can be a demoralizing drag but it can seriously pay off later after you get through the "why is this so difficult?" part.

[reply]
[d/l]
[select]

      This is a great answer, Your Mother! I am so thankful for your help, as well as your empathy. It goes a long way to know that there are others who have struggled and eventually had success with this tool, especially in light of my current circumstance and limited options. DBIx::Class is the tool my company wants to use for the project on which we're working; there's no way around it-- I have to learn and use this tool correctly and efficiently.

      The help you monks have provided has proved invaluable to me on my road to success. Again I thank you

      --
      Tommy
[reply]

      I find myself back at a very similar problem, equally frustrated (personal issue, yes I know). What would help greatly is if I could see what is actually in the resultset object after I fail to get what data I want from it. It's a blessed hashref that isn't inspect-able with Data dumper, because it uses methods that call for data rather than variables that store data. As such, I can't find out what the result of the query would produce...

      How is this done? I've printed off all the docs and have been reading them for 4 days. I have yet to come across anything that lets me see exactly what a query returned. I only get a resultset object (as promised by the docs) but have no way to search it. What columns are there? What rows are there? What are the names and values of such? I have no known way to tell.

      Does anyone else know?

      --
      Tommy
[reply]

        I was able to squeak one out and fix the immediate problem, and I admit it was a bit of a learning experience *grits teeth and bites tongue*. I'm earnestly hoping I can get through all the documentation for this monster before I lose patience for it altogether.

        The fix? Just had to go over the relationships in my growing number of table schema definition classes and tweak-tweak-tweak (rockin' robin)

        --
        Tommy
[reply]
Re: Problems with DBIx::Class and SQL JOIN's
by Arunbear (Prior) on Jun 08, 2009 at 21:30 UTC
    I think your search should look something like: 
    $c->model('DB::Users')->search(
    { 'role.role' => 'friend' },
    {
        join => { 'map_user_role' =>  'role'  },
        '+select' => [ 'role.id', 'role.role' ],
        '+as'     => [ 'roleid', 'role' ],
        rows => 10, 
    },
);

    [download]
[reply]
[d/l]
      Two observations:

      It works... as long as you only search by one role at a time. I don't know why, but I'll take what I can get! You have my gratitude.

      It does not work if you remove the plus ("+") sign from the "+select", and "+as" hash keys. Why is this so?

      Could anyone shed some light on why this works? If I know why/how it works, then I'll never have to ask related questions again --I'll already know the underlying principle and be able to apply it.

      Again, my thanks to you, Arunbear!

      --
      Tommy
[reply]
        You only get one role at a time because the search was limited to the 'friend' role. To change that you need to change the 'where' criteria in the 1st hashref: 
        $c->model('DB::Users')->search_like(
    { 'username' => '%foo%' },
    {
        join => { 'map_user_role' =>  'role'  },
        '+select' => [ 'role.id', 'role.role' ],
        '+as'     => [ 'roleid', 'role' ],
        rows => 10, 
    },
);

        [download]
        The plus ("+") sign is needed because 'role.id' and 'role.role' do not originate in the Users schema/table
[reply]
[d/l]
Sample schema and object introspection with DBIx::Class and Catalyst
by Your Mother (Archbishop) on Jun 11, 2009 at 04:43 UTC

    When you get this running you will have two URIs to play with.

    http://localhost:3000/walk_object/schema to see a table of your schema. There is also a nice module one of the awesome DBIC kids put together to do this stuff and more -- DBICx::AutoDoc.

    And you have http://localhost:3000/walk_object/ResultClassName/_id_. So you should be able to use it like-

    http://localhost:3000/walk_object/Role/1 and http://localhost:3000/walk_object/User/3 etc. I did not check the layout on anything but Firefox so the CSS off the top of my head might be wonky.

    A Catalyst controller stub to play with

    The schema thing isn't the best style... You get the idea. It requires no template. walk_object does require a template. (I updated the model namespace to match what I think yours is.) 

    package MyApp::Controller::Play;
use strict;
use warnings;
use parent 'Catalyst::Controller';
use YAML ();

use CGI ":standard";

my $th_style = {
    -style => "font-family:sans-serif; font-size:13px; text-align:righ
+t; border:1px solid black; padding: 1px 3px; margin:1px;background-co
+lor:#fef;"
    };

my $td_style = {
    -style => "font-family:sans-serif; font-size:12px;border:1px solid
+ black; padding: 1px 3px; margin:1px; background-color:#efe"
    };

my $td_style2 = {
    -style => "font-family:sans-serif; font-size:12px;border:1px solid
+ black; padding: 1px 3px; margin:1px; background-color:#eff"
    };

sub schema : Local {
    my ( $self, $c ) = @_;
    my $schema = $c->model("DB")->schema;
    my @sources = start_html("DB schema");

    for my $src ( sort { "$a" cmp "$b" } $schema->sources )
    {
        push @sources, h2($src);
        push @sources, '<table style="width:90%; border:2px solid gray
+; background-color:#ddd">';
        for my $col ( $schema->source($src)->columns )
        {
            push @sources, "<tr>";
            push @sources, th($th_style, $col);
            my $col_info = $schema->source($src)->column_info($col);
            for my $key ( reverse sort keys %{ $col_info } )
            {
                push @sources, $key ? td($td_style, $key) : td(" ");
                my $info = ref $col_info->{$key} ?
                    YAML::Dump($col_info->{$key}) : $col_info->{$key};
                push @sources, $info ?
                    td($td_style2, $info) : td(" ");
            }
            push @sources, "</tr>";
        }
        push @sources, "</table>";
    }
    $c->response->body( join("\n",@sources) );
}

sub walk_object : Local Args(2) {
    my ( $self, $c, $type, $id ) = @_;
    my $object = $c->model("DB::$type")->find($id)
        or die "No can do, no $type with id '$id'";
    $c->stash( object => $object );
}

1;

    [download]

    The view

    I have moved from Template to Template::Alloy for the last project or two. You might have to install it to play with this. I think you could do the blessed jazz in TT2 but I don't know the syntax off the top of my head. 

    package MyApp::View::Alloy;
use strict;
no warnings "uninitialized";
use parent "Catalyst::View::TT::Alloy";
use Scalar::Util "blessed";

__PACKAGE__->config( RECURSION => 1 );

Template::Alloy->define_vmethod
    (
     "SCALAR",
     blessed => sub {
         blessed(+shift);
     }
     );

    [download]

    The template: walk_object.tt

    This is Alloy but I think it's all valid for TT2 as well. 

    [% INCLUDE show_object
   title = object.blessed()
   obj = object %]

[% BLOCK show_object %]
<div style="clear: both; float:left; padding: 1ex; width: 100%">
  <h3 style="margin:0; padding:0;">[% title %]</h3>
    [% FOR col IN obj.columns %]
    <b style="width: 25%; text-align: right; display:block; float:left
+; margin-right: 1ex">
    [% col %]
    </b>
  <div style="width: 70%; float: left;">
      [% IF obj.$col.blessed %]
        [% INCLUDE show_object
           title = obj.$col.blessed()
           obj = obj.$col %]
      [% ELSE %]
    [% obj.$col || "<i>na</i>" %]
      [% END %]
  </div>
    [% END %]
</div>
[% END %]

    [download]

    The template might be a little tricky to read, but give it try. You can see how it's possible to iterate on columns and descend into relationships (not many_to_many yet, sad to say but maybe in the next major release).

    Good luck and let me know if that won't fly for you. I tested it but in a different namespace so I hope I didn't introduce any bugs setting it up for posting here.

[reply]
[d/l]
[select]

      No, the template's not tricky to read. I want to thank you for the time you spent putting that example together. That will become very useful as I use DBIx::Class and Catalyst more and more.

      Thanks again!

      --
      Tommy
[reply]

Back to Seekers of Perl Wisdom