Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

LTjake (4001)

LTjake
  bricas@cpan.org
http://www.alternation.net/

Journal of LTjake (4001)

Wednesday May 25, 2005
08:28 AM

Class::DBI "self-referencial" join

[ #24876 ]

I've come across an interesting scenario with Class::DBI. Let's say you have a list of authors, but those authors can also have any number of aliases.

You might think to set things up like so: (Note: if I want author 2 to be an alias for author 1 and vice versa i need to have two rows in the alias table)

package My::Author;

use base qw( My::DBI );

__PACKAGE__->table( 'my_authors' );

__PACKAGE__->columns( Primary   => qw( author_id ) );
__PACKAGE__->columns( Essential => qw( first_name last_name ) );

__PACKAGE__->has_many( aliases => [ 'My::AuthorAlias' => 'alias' ] );

package My::AuthorAlias;

use base qw( My::DBI );

__PACKAGE__->table( 'my_author_alias' );

__PACKAGE__->columns( Primary   => qw( alias_id ) );
__PACKAGE__->columns( Essential => qw( author alias ) );

__PACKAGE__->has_a( author => 'My::Author' );
__PACKAGE__->has_a( alias  => 'My::Author' );

1;

But, it seems that Class::DBI gets confused with two has_a relationships pointing to the same table. So, the aliases relationship doesn't work.

My current work-around is to use might_have on the non-primary column:

package My::AuthorAlias;

use base qw( My::DBI );

__PACKAGE__->table( 'my_author_alias' );

__PACKAGE__->columns( Primary   => qw( alias_id ) );
__PACKAGE__->columns( Essential => qw( author ) );

__PACKAGE__->has_a( author => 'My::Author' );

__PACKAGE__->might_have( alias => 'My::Author' );

1;

From a simple test, it seems okay. Am I missing an easier alternative?


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Class::DBI "self-referencial" join 5 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • Had you tried using the table_alias() [cpan.org] (or also the second param to table()) on the AuthorAlias class?

    I would summize that using table_alis on the second package would un-confuse CDBI.

    • I'm failing to see how table_alias() will help in this situation, but, perhaps I'm being dense. Could you explain it further?

      Thanks!

      • It doesn't. :-) I misread the post the first time. I thought that it was an alias field in the authors table pointing right back to the authors table, instead of using the 2nd pivot table.

        Could you post your schema for those two tables as well as a couple rows of sample data? I'd like to tinker.
  • Funny... read your journal entry and thought "hey, I think somebody on IRC was looking for something like this, this morning..."

    So I went to see the logs...

    It was you :-)

  • Thanks for posting this -- I haven't had this problem yet, but the schema I'm expecting to put into Class::DBI / Maypole / Catalyst later this year has exactly this structure. Forewarned is forarmed.
    --
    Bill
    # I had a sig when sigs were cool
    use Sig;
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.