On Mon, Oct 04, 2004 at 10:08:24AM -0400, Sean Davis wrote:
> I have looked into CDBI a bit more (it was I that started all of this
> several days ago). I am still concerned about handling joins. I agree that
> it (CDBI) looks fantastic for getting information from a single table at a
> time, but I often need to pull data from different columns in several tables
> at a time or perform a join (or multiple) to get the information that I
> want. In my particular case, I have some 50 tables, many with only 2-3
> columns because of the relationality of the data, so performing multiple
> selects to "drill down" to data from a fourth or fifth join using the CDBI
> framework seems quite tedious...
With CDBI, you don't have to think in terms of performing multiple
selects; the module handles this for you. Here's a simple example using
cds and artists which is based on the CDBI docs. I've started with the
code you'd use in your C::A module then included the table defs and some
of the CDBI defs.
# Retrieve the cd with id=1
my $cd = Music::CD->retrieve(1);
# All columns are available via accessors of the same name
my $year = $cd->year;
# It's possible to retrieve columns from related tables
# Retrieve the artist name of that cd
my $artist = $cd->artist->name;
# Retrieve cds with 'October' in the name
my @cds = Music::CD->search_like(title => 'October%');
------ SQL defs --------------------
CREATE TABLE cd (
cdid INTEGER PRIMARY KEY,
artist INTEGER,
title VARCHAR(255),
year CHAR(4)
);
CREATE TABLE artist (
artistid INTEGER,
name VARCHAR(255)
);
------ CDBI defs --------------------
package Music::Artist;
use base '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 'Music::DBI';
Music::CD->table('cd');
Music::CD->columns(All => qw/cdid artist title year/);
Music::CD->has_many(tracks => 'Music::Track');
Music::CD->has_a(artist => 'Music::Artist');
Music::CD->has_a(reldate => 'Time::Piece',
inflate => sub { Time::Piece->strptime(shift, "%Y-%m-%d") },
deflate => 'ymd',
);
> ...and successfully defeats the power of modern SQL engines for
> performing these joins in a very systematic fashion, using more than
> just a primary key if possible.
As Rhesa suggested, you may want to take a look at the CDBI wiki for
more info about doing joins. This is an area that is getting more
attention (at least on the mailing list) so hopefully better support
will be forthcoming in future releases. It's a complex area that other
modules such as SQL::Abstract have also not provided complete solutions
to yet.
HTH,
William
--
Knowmad Services Inc.
http://www.knowmad.com
---------------------------------------------------------------------
Web Archive: http://www.mail-archive.com/[EMAIL PROTECTED]/
http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
