[Dbix-class] has_one over joining table

[Andrew Beverley]

On Mon, 29 Jan 2018 12:34:14 +0100 Stuart Dodds wrote:
> I have a many to many relationship over a joining table, eg.
> 
> towns -> town_people -> people
> 
> In some cases it is possible for there to be has_one relationships
> between the two outer tables. It's a bit of a contrived example but lets
> say a person can live in multiple towns but only one is her home town.
> Another (probably better) example would be that every town has one mayor.
> 
> I currently have a home_town_id field in the people table, which makes
> things difficult with the foreign key constraint when inserting new
> people with new home towns. And if there is a mayor_id in the towns
> table which links to people, it is impossible to insert any new data
> without disabling the foreign key constraints first.
> 
> So, my idea is (please say if this is also not such a good solution) to
> remove the home_town_id field from people and add an is_home_town
> NULLable flag (with unique index) to town_people. And similarly an
> is_mayor flag to the same table...this way all town/people related data
> is in one table and I no longer need to worry about foreign key
> constraints when inserting the outer tables.

Yes - that sounds like a good idea to me, and is probably the way that I
would do it.

> And finally, the DBIC question....
> 
> Is there a way to set up a has_one (home_town/mayor) relationship
> between the people and towns tables with the following constraint on the
> joining table: is_home_id = 1

I'm not sure you can do it directly from one table to the other, but
you could certainly create a might_have relationship (instead of
has_one as it might not exist) to town_people with the extra condition.

Something like (in Result::Town):

__PACKAGE__->might_have(
    "town_people_mayor",
    "MyApp::Schema::Result::TownPeople",
    sub {
        my $args = shift;
        return {
            "$args->{foreign_alias}.town_id"  => { -ident => "$args->{self_alias}.id" },
            "$args->{foreign_alias}.is_mayor" => 1,
        };
    }
);

> So that I can make calls like this:
> 
> $person->home_town;
> $town->mayor;

You would then do something like:

$town->town_people_mayor->mayor

Andy