[Dbix-class] has_one joining through another table

[Matt S Trout]

On Tue, Jun 26, 2007 at 06:45:11PM -0400, Steve Francia wrote:
> Matt S Trout wrote:
> >On Tue, Jun 26, 2007 at 03:05:06PM -0700, Marc Mims wrote:
> >  
> >>* Steve Francia <steve at takkle.com> [070626 14:40]:
> >>    
> >>>in User Schema:
> >>>__PACKAGE__->has_many(user_pictures =>'Schema::UserPicture', 
> >>>'fk_user_id');
> >>>__PACKAGE__->many_to_many(pictures => 'user_pictures', 'picture');
> >>>
> >>>in UserPicture Schema:
> >>>__PACKAGE__->belongs_to(user => 'Schema::User', 'fk_user_id');
> >>>__PACKAGE__->belongs_to(picture => 'Schema::Picture', 'fk_picture_id');
> >>>
> >>>in Picture Schema:
> >>>__PACKAGE__->has_many(user_pictures =>'Schema::UserPicture',
> >>>'fk_picture_id');
> >>>__PACKAGE__->many_to_many(users => 'user_pictures', 'user');
> >>>      
> >>I would add a user foreign key to picture and drop the owner flag from
> >>user_picture.  The the relationship is straight forward:
> >>
> >>In Picture Schema:
> >>__PACKAGE__->belongs_to(owner => 'Schema::User', 'owner_id');
> >>    
> >
> >Agreed. The schema as-is isn't sane since there's no way to enforce
> >that only one user is the owner of a given picture.
> >
> >Unless you're planning to have multiple owners, which I didn't infer from
> >what you said before.
> >
> >  
> Sadly, that is the way we had it, we decided to switch to using the 
> owner flag to try to eliminate duplication of data in the database.

False economy - that isn't duplication, as the first reply to the this
post points out. Won't work the way you've got it, we don't tend to add
code to make broken database layouts easier to core.
 
> This does bring up an interesting question though. If there were 
> multiple owners, but then that would just be a fairly typical 
> many_to_many relationship, right?

Yep.

> Is there a way to do it given the current database layout? It would be 
> good to know from a design standpoint, if such a thing were possible. I 
> have a more complicated example that requires an answer to this simpler 
> one. It is more or less the same exact scenario, except instead of 
> owner, it is type. There are 3 types, which translate to three different 
> relationships, two are a many_to_many, one is a has_one. I know the 
> database wouldn't enforce that, but our application would.

Again, you probably want to rethink that and model the has_one as a separate
table.

Your database is there to enforce the integrity of your data, that's what
the whole constraint subsystem is designed to do. It might take a bit
more thinking to get it to work right, but it's always better to let the
database do what it's good at.

If you have problems with query speed then a denormalised lookup table
populated from triggers or leaning on memcached will work, but you should
always avoid sacrificing relational integrity for performance until you're
-absolutely- sure you need ti, and the whole point of an ORM layer is
that you -don't- have to sacrifice convenience for relational integrity :)

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/             http://www.shadowcatsystems.co.uk/