I just love Class::DBI. But I found something on the manpage that doesn't look right.

The manpage shows a cd table, linking to an artist table. For this, the cd table's artist field contains a numerical id which references the artist table's artistid field.

Now, Class::DBI hides this pretty well, once the has_a() relationship gets defined. If you get a CD object $cd as a result of a query, you can simply use $cd->artist()->name() to get to the artist table and to the name field from there.

However, further down, there's a code snippet that I don't think will work:

my $cd = Music::CD->find_or_create(
   { artist => "U2", title => "Boy" });
[download]

This seems to imply that you could use the artist field somewhat transparently and Class::DBI would figure out magically that you meant the artist table's name field.

Further below, there's another example:

    my @music = Music::CD->search_where(
        artist => [ ’Ozzy’, ’Kelly’ ],
        status => { ’!=’, ’outdated’ },
    );
[download]

This seems to imply that you could use Class::DBI::AbstractSearch to search Music::CD (mind you, not Music::Artist) for the artist field, which Class::DBI would then somehow magically map to the name field in the artist table. How would it know that? The mapping is between the cd table's artist field and the artist table's artistid field. Noone mentioned the name field.

Further down there's a search_like:

@cds = Music::CD->search_like(
    title  => ’Hits%’, 
    artist => ’Various%’);
[download]

Same thing. How would Music::CD know to match the content of the artist parameter against the name field of the artist table?

Finally, here's a code snippet to reproduce the behaviour. It doesn't find the artist because Class::DBI is looking for the artist ID, not, as advertised, for the artist name. SQL setup included. Any insight appreciated ...

package Music::DBI;

use base qw(Class::DBI);
Music::DBI->set_db("Main", "dbi:mysql:testdb", "root", "");

package Music::Artist;
use base qw(Music::DBI);
Music::Artist->table("artist");
Music::Artist->columns(All => qw/artistid name/);
Music::Artist->has_many(cds => "Music::CD");

package Music::CD;
use base qw(Music::DBI);
use Class::DBI::AbstractSearch;
Music::CD->table("cd");
Music::CD->columns(All => qw/cdid artist title year/);
Music::CD->has_a(artist => "Music::Artist");

my $artist = Music::Artist->create({artistid => 1, name => "U2"});

my $cd = $artist->add_to_cds({
    cdid   => 1,
    title  => "October",
    year   => 1980,
});

my @cds = Music::CD->search_like(artist => "%U%");

print $_->title(), "\n" for @cds;

__END__
CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
CREATE TABLE IF NOT EXISTS cd (
    cdid   INTEGER   PRIMARY KEY,
    artist INTEGER, # references ’artist’
    title  VARCHAR(255),
    year   CHAR(4)
);

CREATE TABLE IF NOT EXISTS artist (
    artistid  INTEGER   PRIMARY KEY,
    name      VARCHAR(255)
);
[download]