All the Perl that's Practical to Extract and Report

Class::DBI "self-referencial" join

LTjake on 2005-05-25T13:28:23

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?

What about table_alias?

jk2addict on 2005-05-25T14:27:40

Had you tried using the table_alias() (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.

Re:What about table_alias?

LTjake on 2005-05-26T11:33:58

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!

Re:What about table_alias?

jk2addict on 2005-05-26T12:37:47

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.

Cross-memories

cog on 2005-05-25T16:15:21

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

n1vux on 2005-05-26T14:44:58

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.