Class::DBI and might

debiandude has asked for the wisdom of the Perl Monks concerning the following question:

Hello everyone

I am trying to setup CDBI for my database and either I am not understanding how a might_have works or I am doing something silly. Let me first describe my table structure: (I can't figure out how to get these tables to look normal sorry)

|-------------------|       |-------------------|
|  TBL NAME = Article   |       |  TBL NAME = Author  |     
|-------------------|       |-------------------|
| articleId  INT PrimKey |       | authorId  INT PrimKey|
| authorId  INT              |       |  articleId INT              |
| title varchar(255)        |       | author  varchar(32)    |
|other fields                  |       | other fields                |
---------------------      ---------------------
[download]

So I have a one-to-one relationship between the Article table and the Author table via authorId (and a many-to-one relationship from the Author to the Article table). I wanted to implment a might_have relationship so that I could do something like this:

$article->author
[download]

This is what I wrote (that does not work). Does anybody see my mistake?

package MPDatabase::Article;

use strict;
use base 'MPDatabase::DBI';

__PACKAGE__->table('article');
__PACKAGE__->columns(Primary => qw/articleId/);
__PACKAGE__->columns(All => qw/articleId topicId authorId title intro 
+body postDateTime/);
__PACKAGE__->has_a(topicId  => 'MPDatabase::Topic');
__PACKAGE__->has_a(authorId => 'MPDatabase::Author');

__PACKAGE__->might_have( topic_class  => 'MPDatabase::Topic' => qw/top
+ic relativeImagePath imagePath alt/);
__PACKAGE__->might_have( author_class => 'MPDatabase::Author' => qw/au
+thor email/);

1;
[download]

package MPDatabase::Author;

use strict;
use base 'MPDatabase::DBI';

__PACKAGE__->table('author');
__PACKAGE__->columns(Primary => qw/authorId/);
__PACKAGE__->columns(All => qw/authorId author email/);
__PACKAGE__->has_many(articles => 'MPDatabase::Article');

1;
[download]

I also wrote a test program to see if it works:

!/usr/bin/perl -w
#
use strict;

use MPDatabase::Article;
use MPDatabase::Author;

my $article = MPDatabase::Article->retrieve(articleId => 5);
print $article->author, "\n";

# This program does not produce any output
[download]

I turned on tracing to see the SQL code being generated and saw this:

<--- parse_params
mysql_st_internal_execute
Binding parameters: SELECT authorid
FROM   author
WHERE  authorid = '5'
[download]

Which is not what I expected. I was expecting something like this:

SELECT authorId
FROM author A, article B
WHERE A.authorID = B.authorId and B.articleId = 5
[download]

Thanks in advance.

Comment on Class::DBI and might_have problem Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI and might_have problem by jdtoronto (Prior) on Jun 07, 2006 at 19:10 UTC
The 'has-a' allows you to implement the accessor without having to use the 'might-have'. Specifically the docs say that the 'might-have' is used to establish a relationship where the key to the related table does not appear in the parent. But in your case it already does. I am short on time today so I can't give you a coded example, but I would suggest this - Data::Dumper is your friend. Remove the 'might-have' - leave the 'has-a' - retrieve the data from the parent object and dump it. Now you can see the relationship, in place of the author id field in the parent you will see that the author object is now visible and is directly able to be accessed. Of course $article->author will return a blessed reference, but $author->article->id will give you the author id, and so on. Here is the docs on the subject: might_have Music::CD->might_have(method_name => Class => (@fields_to_import)); Music::CD->might_have(liner_notes => LinerNotes => qw/notes/); my $liner_notes_object = $cd->liner_notes; my $notes = $cd->notes; # equivalent to $cd->liner_notes->notes; might_have() is similar to has_many() for relationships that can have +at most one associated objects. For example, if you have a CD databas +e to which you want to add liner notes information, you might not wan +t to add a 'liner_notes' column to your main CD table even though the +re is no multiplicity of relationship involved (each CD has at most o +ne 'liner notes' field). So, you create another table with the same p +rimary key as this one, with which you can cross-reference. But you don't want to have to keep writing methods to turn the the 'li +st' of liner_notes objects you'd get back from has_many into the sing +le object you'd need. So, might_have() does this work for you. It cre +ates an accessor to fetch the single object back if it exists, and it + also allows you import any of its methods into your namespace. So, i +n the example above, the LinerNotes class can be mostly invisible - y +ou can just call $cd->notes and it will call the notes method on the +correct LinerNotes object transparently for you. [download] I would not be surprised if Class::DBI chokes on having two relationships referring to the related data. jdtoronto	[reply] [d/l]
Re^2: Class::DBI and might_have problem by debiandude (Scribe) on Jun 07, 2006 at 19:36 UTC
The reason I was thinking I wanted to use might_have because I like the idea of beging able to just write: `$article->author` [download] If I instead use the has_a relationship I need to write: `$article->authorId->author` I guess I am still a little muddled on the diff between has_a and might_have. Since it seems that might_have provides a type of access that has_a does not. But they are not replacements for each other?	[reply] [d/l] [select]
Re: Class::DBI and might_have problem by bpphillips (Friar) on Jun 07, 2006 at 22:25 UTC
The Class::DBI docs aren't especially clear on this but `might_have` relationships have to have identical primary keys in both tables. This makes a `has_a` relationship your only option since the `Author` table naturally doesn't have a primary key of `articleId`. If you'd like to have the functionality you're describing, you could do something like the following (quite untested): `package MPDatabase::Article; # ... set up table ... __PACKAGE__->has_a(authorId => 'MPDatabase::Author'); { no strict 'refs'; foreach my $m(MPDatabase::Author->columns()){ # keep from overriding existing methods... next if __PACKAGE__->can($m); # deploy shortcut sub {$m} = sub { my $obj = shift; return $obj->authorId->$m(@_); }; } }` [download] Incidentally, I've found `might_have` relationships to be of rather limited use. -- Brian UPDATE:* tweaked method detection	[reply] [d/l] [select]
Re: Class::DBI and might_have problem by perrin (Chancellor) on Jun 07, 2006 at 19:35 UTC
First, either use might_have or has_a, not both. Second, if multiple authors may have the same value in the articleId field, might_have will not work. You'd need has_many instead. Finally, Class::DBI doesn't do joins so it will never write SQL like the example you wrote. It will do something like this instead: `SELECT authorId FROM author WHERE articleId = 5` [download]	[reply] [d/l]
Re^2: Class::DBI and might_have problem by debiandude (Scribe) on Jun 07, 2006 at 19:40 UTC
Multiple authors cannot have the same value in the articleId field since each article has only one author (its creator). Also I didn't mean to put both has_a and might_have in the code, it was just that in the module I was playing around in I was flipping between them (since has_a was working but might_have was not) to try to figure what was going wrong.	[reply]