How do I create a nested has_many or belongs_to relationship with DBIx::Class?

Question

In my code I have three classes as follows: Forum, Forum::Thread and Forum::Post

What I want to do is create a belongs_to-relationship from the Forum::Post class to the Forum class and vice versa with a has_many, preferably without creating a custom function for it. (This is admittedly more of an intellectual exercise than a technical limitation or actual problem, but if it is possible, I would much like to know.)

The commented out lines contain my intention with the relationships, but in their current form, they fail to work. I've poked around in the documentation, but cannot find anything relevant to this specific case.

Any pointers?

The forum class:

package Schema::Result::Forum;

use Moose;
extends qw/DBIx::Class/;

__PACKAGE__->load_components (qw/Core/);
__PACKAGE__->table ('forum');

__PACKAGE__->add_columns (
    id => {
    is_auto_increment => 1,
    data_type         => 'integer',
  },
);

__PACKAGE__->set_primary_key ('id');

__PACKAGE__->has_many (threads => 'Schema::Result::Forum::Thread');
#This is the interesting line
#__PACKAGE__->has_many (posts => 'threads' => 'forums' );

1;

The thread class:

package Schema::Result::Forum::Thread;

use Moose;
extends qw/DBIx::Class/;

__PACKAGE__->load_components (qw/Core/);
__PACKAGE__->table ('forum_thread');
__PACKAGE__->add_columns (
  id => {
    is_auto_increment => 1,
    data_type         => 'integer',
  },
  forum => {
    data_type         => 'integer',
  },
);

__PACKAGE__->set_primary_key ('id');

__PACKAGE__->belongs_to (forum => 'Schema::Result::Forum');
__PACKAGE__->has_many (posts => 'Schema::Result::Forum::Post');

1;

The post class:

package Schema::Result::Forum::Post;

use Moose;

extends qw/DBIx::Class/;

__PACKAGE__->load_components (qw/Core/);

__PACKAGE__->table ('forum_post');

__PACKAGE__->add_columns (
  id => {
    is_auto_increment => 1,
    data_type         => 'integer',
  },
  thread => {
    data_type         => 'integer',
  },
);

__PACKAGE__->set_primary_key ('id');

__PACKAGE__->belongs_to (thread => 'Schema::Result::Forum::Thread');
#This is the other interesting line
#__PACKAGE__->belongs_to (forum => 'thread' => 'forum');

1;

PS: Additional columns to hold actual content were omitted for brevity.

Answer 1

What database and engine-type are you using? If you don't have foreign keys (like, for instance, with myisam tables in MySQL) then you'll have to provide the column names in your relationship statements.

Answer 2

It looks like nested relationships aren't possible. has_many takes a single foreign class, which has a foreign key to the calling class.

The good news is $forum->threads->posts returns a single DBIx::Class::Resultset. It is not translated into SQL until needed, so when you call $forum->threads->posts->all() or even something like $forum->search_related('threads',{},{rows=>25})->posts->all(), it only runs a single query.

If your goal is to have a $post->forum, it can always be a method: sub forum{$_[0]->thread->forum}