Re: Selecting (and accessing) from many tables

[prev] [thread] [next] [Date index for 2004/09/21]

On Tue, Sep 21, 2004 at 08:04:17AM +0100, Tony Bowden wrote:
> On Mon, Sep 20, 2004 at 06:55:16PM -0400, Jesse Sheidlower wrote:
> > I had thought that I was doing something that would _reduce_
> > the overall complexity; if I have to write up new subclasses
> > or a variety of custom accessors, it might not be worth it to
> > use CDBI at all, for this part of the job.
> 
> I'm curious as to why you thought this would reduce complexity.
>
> For a track you wanted to print:
> 
>   Track Track.title is on album CD.title by Artist.name
>   released in CD.year.
> 
> That's just:
> 
> my $track = Track->retrieve($id);
> printf "Track %s is on album %s by %s released in %d.\n", 
>   $track->title, $track->cd->title, 
>   $track->cd->artist->title, $track->cd->year;

Well, if I can do this with the select we discussed yesterday,
the one that started

  Track->set_sql(foo => qq{
    SELECT __ESSENTIAL(t)__, __ESSENTIAL(c)__, __ESSENTIAL(a)__
    FROM __TABLE(=t)__, __TABLE(CD=c)__, __TABLE(Artist=a)__

and get all my CDBI objects at once (or at least without going
back to the database), then it would be fine to do it that
way.

> I'm really not sure what complexity you're trying to reduce here.

I suppose in this _exact_ example, the complexity would be
about the same with CDBI as with a raw SQL search returning a
fetchrow_hashref reference (if I could retrieve as you
describe above and get everything with one trip to the
database). But I'd like not to _add_ complexity by using CDBI.

The benefit I'm looking for is being able to manipulate the
returned objects more effectively. For example, if there are
circumstances in which I would like to get additional data
(say from another, non-ESSENTIAL field, or from another table),
it would be easier to do it with my existing CDBI object than
to handle stuff to do it in SQL.

> I thought you were doing this purely for performance reasons. (Not that
> in general there should be performance problems with this approach if
> you've got the correct indexes anyway).

I don't see why this is the case. Say I'm doing a search that could
return a large result set, and I limit it to 50 records at a time.
If I actually have five tables involved (rather than the three in
this example), that would mean that displaying a table in the form

  "Track %s is on album %s by %s released in %d.\n"

would require 200 trips to the database, and even if everything is
well indexed, this could get bogged down quickly. And there's no
real need for it, if I can get a query that will return everything
I need at one time.

Jesse Sheidlower