in reply to One to many, many to one relationships

What you want is either DBI::Class or Class::DBIx. I would personally recommend using Class::DBIx.
  • Comment on Re: One to many, many to one relationships

Replies are listed 'Best First'.
Re^2: One to many, many to one relationships
by rvosa (Curate) on Mar 12, 2007 at 17:45 UTC
    No, really, this is not a database design issue. It's about how to design my objects to model the relationships: 
    One-to-many:
taxon ---> node
      ---> node
      ---> sequence
      ---> sequence

One-to-one:
node ---> taxon

One-to-one:
sequence ---> taxon

    [download]
    Such that I can do: 
    my @nodes = $taxon->get_nodes;
my @sequences = $taxon->get_sequences;
my $taxon = $node->get_taxon;
$taxon = $sequence->get_taxon;

    [download]
    ...with an underlying architecture where the relationships automatically are bi-directional in an elegant way. For example: 
    $node->set_taxon( $taxon );

    [download]
    ...might mean that the $node now holds a reference to $taxon, but the $taxon now also has to hold a reference to $node. So should I then do: 
    sub set_taxon {
    my ( $node, $taxon ) = @_;
    $node->{'_taxon'} = $taxon;
    # check if taxon already knows about me
    for my $known ( $taxon->get_nodes ) {
        return $node if $known == $node;
    }
    $taxon->add_node( $node );
    return $node;
}

    [download]
    ...but then the $taxon->add_node( $node ) sub would likewise need to check if $node already knows about $taxon, and if not make the link in the other direction - without recursing add infinitum. All in all, that's not elegant.

    So my question was: should the link making be managed by a third object (some sort of manager), to which the set_whatever, add_whatever, get_whatever method calls are re-routed. Or should I do something else entirely?

    My question is not addressed by database designs, raw sql code, or object-relational mappers. Thank you for your input, though. I'm looking for suggestions for design patterns to deal with bi-directional relationships between objects. I understand that the suggestion for MVC has something to do with the issue (but the term MVC is so polluted at this point it's not unambiguously obvious how to apply it - perhaps the 'C' is the link manager?), so greatshots I think got what I was talking about, and so did graff.

    I apologize to all others if I've phrased things so poorly that I made you write about databases.
[reply]
[d/l]
[select]
      I think the closest formal (GoF) pattern for what you are describing is the Mediator pattern, which acts as the third-party object you are describing. The main challenge is that this pattern is open to abuse as described by the God anti-pattern
[reply]
        Thanks, yes - I'm using the mediator pattern, it's pretty much what I thought I should do so it's nice to see it validated by the Gang of Four :)
[reply]
      You can do this with Class::DBIx and Rose::DB::Object. For example in RDBO: Assume this simple schema: 
      DROP TABLE public.nodes;
DROP TABLE public.sequences;
DROP TABLE public.taxa;

CREATE TABLE public.taxa (
    id SERIAL NOT NULL PRIMARY KEY
    , common_name CHARACTER(60)
    , UNIQUE (common_name)
);

CREATE TABLE public.nodes (
    id SERIAL NOT NULL PRIMARY KEY
    , description CHARACTER(60)
    , taxon_id INT REFERENCES taxa (id)
);

CREATE TABLE public.sequences (
    id SERIAL NOT NULL PRIMARY KEY
    , description CHARACTER(60)
    , seq TEXT NOT NULL
    , taxon_id INT REFERENCES taxa (id)
);

      [download]
      Then this perl code: 
      

package MyDB;

use base qw(Rose::DB);

__PACKAGE__->use_private_registry;

__PACKAGE__->register_db(
    driver   => 'pg',
    database => 'mydb',
    host     => 'localhost',
    username => $ENV{USER},
    password => '',
);
1; 

package MyDB::Object;

use MyDB;

use base qw(Rose::DB::Object);

sub init_db { MyDB->new };

1;

package MyDB::Node;

use base qw(MyDB::Object);

__PACKAGE__->meta->table('nodes');
__PACKAGE__->meta->auto_initialize;
__PACKAGE__->meta->make_manager_class('nodes');
1;


package MyDB::Sequence;

use base qw(MyDB::Object);

__PACKAGE__->meta->table('sequences');
__PACKAGE__->meta->auto_initialize;
__PACKAGE__->meta->make_manager_class('sequences');

1;

package MyDB::Taxon;

use base qw(MyDB::Object);

__PACKAGE__->meta->table('taxa');
__PACKAGE__->meta->auto_initialize;

1;



my $t = MyDB::Taxon->new(common_name => 'Homo Sapiens');
$t->add_sequences({ description => '1', seq => 'AA'},
                  { description => '2', seq => 'GG'},);
$t->add_nodes({ description => '1', }, );
$t->save;

# a little bit more complicated than necessary just
# to demonstrate queries
my $nodes = MyDB::Node::Manager->get_nodes(
        query => [ 'taxon.id' => $t->id],
        require_objects => [ 'taxon'],
    );

foreach my $node (@$nodes) {
    print "NODE: ". $node->description . ' '
          . $node->taxon->common_name . "\n";
};

my $seqs = MyDB::Sequence::Manager->get_sequences(
        query => [ 'taxon_id' => $t->id],
    );

foreach my $seq (@$seqs) {
    print "SEQ: ". $seq->description . ' '
          . $seq->taxon->common_name . "\n";
};

# change taxon
$nodes->[0]->taxon(MyDB::Taxon->new(common_name=>'Guppy'));
$nodes->[0]->save;

# old taxon
$nodes->[0]->taxon($t);
#$nodes->[0]->save;

      [download]
      ...does want you want I think. Or not? Is there a reason why you want to write your own OO mapper (you said you will have an underlying database)? Update: Fixed Idention
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom