[Dbix-class] has_one over joining table

[Stuart Dodds]

Hi,

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.

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

So that I can make calls like this:

$person->home_town;
$town->mayor;

Or prefetch the related data in searches like this:

$resultset('People')->search(
  { 'me.name' => 'Sue' },
  { prefetch => 'home_town' },
);


Many thanks,

Stuart