[Dbix-class] Joining the same table several times with different conditions each join

[Andrew Beverley]

On Sat, 2014-05-17 at 10:00 -0700, Karen Etheridge wrote:
> On Sat, May 17, 2014 at 11:01:59AM +0100, Andrew Beverley wrote:
> > I'd like to join the same table multiple times, with different
> > conditions each join (and a variable number of joins).
> 
> If you define each of your join permutations as a relationship, you can
> bring in the joins using that/those relationship name(s):
> 
> $rs->search({ ... }, { join => [ 'rel1', 'rel2', ... ] })

The problem is the second join condition, which requires more than a
simple join. However...

> > Of course, the table could be designed with multiple columns for each of
> > the values (surname and firstname being just 2 examples), but these are
> > not known at the time of coding and are configurable by the application.
> 
> You can dynamically add relationship definitions at runtime.  Just keep the
> rel names around in a local variable or attribute for later use.

...this could be the answer.

I could do with a bit of help with dynamically adding a relationship at
runtime though.

The code below is what I've written in my main application (not in a
schema class). I'm assuming it's not correct and that I don't need to
add both relationship statements; however, I couldn't get it to work
correctly with only one or the other.

What's the correct way of dynamically adding a relationship?

$sch->source('Record')->add_relationship(
    surname => 'String',
    sub {
        my $args = shift;

        return {
            "$args->{foreign_alias}.record_id" =>
                { -ident => "$args->{self_alias}.id" },
            "$args->{foreign_alias}.layout_id" => 1,
        };
    }
);
SDS::Schema::Result::Record->has_one(
    surname => 'String',
    sub {
        my $args = shift;

        return {
            "$args->{foreign_alias}.record_id" =>
                { -ident => "$args->{self_alias}.id" },
            "$args->{foreign_alias}.layout_id" => 1,
        };
    }
);

Thanks,

Andy