Can I have a "Parent has_many GrandChildren through Children" relationship in DBIx::Class?

Question

I'd like to have functionality like this:

$parent->get_grandchildren_by_category({category => 'foo'});

I can do it easily outside of the parent class with a simple chained join:

$schema->resultset('Parent')->search(
    {
        'me.id' => 62,
        'grandchildren.category' => 'foo'
    },
    {
        join => {'children' => 'grandchildren'}
    }
);

But inside the parent class I don't have access (and shouldn't) to the schema object. Inside the parent class I can access $self->children but that returns a resultset of children, and I'd have to iterate over them to get each one's grandchildren.

Is there any way I can define this ActiveRecord style?

class Parent < ActiveRecord::Base
   has_many :children
  has_many :grandchildren, :through => :children
end

Answer 1

I got a lot of help from #dbix-class. There may not be a way to explicitly define a :through relationship ActiveRecord-style, but there is a way to grab children further down the result chain.

Using the ResultSet's search_related method, you can reach through a relationship and use the child's accessors. For example, in my Parent class:

sub get_grandchildren_by_category{
    my ($self, $category) = @_;
    my @gchildren = $self->children->search_related('grandchildren'
        {
             'grandchildren.category' => $category
        }
    );

    return \@gchildren;
}

Since search_related returns a resultset, I guess you could chain those search_related calls as far as you want.