Re: Problems with DBIx::Class and SQL JOIN's

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.

Comment on Re: Problems with DBIx::Class and SQL JOIN's Select or Download Code

Replies are listed 'Best First'.
Re^2: Problems with DBIx::Class and SQL JOIN's by Tommy (Chaplain) on Jun 10, 2009 at 12:44 UTC
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]
Re^2: Problems with DBIx::Class and SQL JOIN's by Tommy (Chaplain) on Jun 10, 2009 at 15:43 UTC
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]
Re^3: Problems with DBIx::Class and SQL JOIN's by Tommy (Chaplain) on Jun 10, 2009 at 19:18 UTC
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]