[Dbix-class] Need help with DBIC join table queries from Catalyst app

[Jess Robinson]

On Tue, 14 Apr 2009, Dennis Daupert wrote:

> I am trying to work out queries from my Catalyst app that involve join
> tables.
>
> These are no doubt pretty simple once you have worked out the formats.
>
> Yes, I have read thru various docs:
> DBIx::Class::Manual::Intro
> DBIx::Class::ResultSet
> DBIx::Class::Relationship
> DBIx::Class::Relationship::Base
>
> I find that in trying to modify documented examples to my situation,
> something is just not matching up.
>
> I have several sets of parent / child / join tables in which both the
> "parent" (has many and many-to-many) and "child" (belongs to) tables are
> managed separately, and only the join tables need to be updated; e.g. ,
> user_roles; project_members; project_directories; etc.
>
> Let me paste some bits from three tables, snipped for brevity:

You didn't actually say what your errors/problems were.. but here's a 
possible hint:

> ===============================
> package hde::Schema::Result::Projects;
> __PACKAGE__->table("projects");
> __PACKAGE__->add_columns(
>  "id",   { data_type => "INTEGER", is_nullable => 0, size => undef },
>  "rel_tag",   { data_type => "TEXT", is_nullable => 0, size => undef },
>
> __PACKAGE__->has_many(map_project_directory =>
> 'hde::Schema::Result::ProjectDirectories', 'directory_id');

^^^^ "directory_id" probably wants to be "project_id"?

> __PACKAGE__->many_to_many(directories => 'map_project_directory',
> 'directory');
>
> ===============================
> package hde::Schema::Result::Directories;
> __PACKAGE__->table("directories");
> __PACKAGE__->add_columns(
>  "id",   { data_type => "INTEGER", is_nullable => 0, size => undef },
>  "directory",   { data_type => "TEXT", is_nullable => 0, size => undef },
>
> __PACKAGE__->has_many(map_project_directory =>
> 'hde::Schema::Result::ProjectDirectories', 'directory_id');
>
> ===============================
> package hde::Schema::Result::ProjectDirectories;
> __PACKAGE__->table("project_directories");
> __PACKAGE__->add_columns(
>  "project_id",   { data_type => "INTEGER", is_nullable => 0, size => undef
> },
>  "directory_id",   { data_type => "INTEGER", is_nullable => 0, size =>
> undef },
> __PACKAGE__->belongs_to(project => 'hde::Schema::Result::Projects',
> 'project_id');
> __PACKAGE__->belongs_to(directory => 'hde::Schema::Result::Directories',
> 'directory_id');
>
> ===============================
>
> In Catalyst, how can I get a list of directories assigned to projects?
>
> Documentation says: call the project's many_to_many accessor on the project
> object.
> In my case, the many_to_many accessor would be 'directories'
>
> An example I've seen says: $directories_rs = $projects->directories()
>
> Yet, in Catalyst, nothing I've tried along those lines have paid off.
>
>    # One of many failed attempts:
>    my $projects_rs =
> $c->model('HdeDB::Projects')->find($selected_project_id);
>      my $dirs_rs = $projects_rs->directories();
>      while (my $dir_obj = $dirs_rs->next) {
>        my $id = $dir_obj->id;
>        my $dir_name = $dir_obj->directory;
>        $c->log->debug("ID: $id -- Dir Name: $dir_name");
>      }

Code looks lovely, did you try looking at the DBIC_TRACE output? 
($c->model('HdeDB')->schema->storage->debug(1) before the code)
That will output the SQL its producing, and you might then notice your bug 
up there ;)

btw next problem, please also add what the errors were.. perl errors? 
wrong data? cat ate it?

Jess