Selecting (and accessing) from many tables

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

I'm interested in using CDBI for a multi-table database where
most queries involve numerous tables. For the moment I'm
putting aside the issue of inserts or updates where multiple
tables are affected; I'm interested in how best to execute and
utilize selects. The core structure is similar enough to the
Music stuff in the docs that I can use that for the example--I
have an Artist, which has_many CD, which has_many Track. In my
case the Track is my fundamental way of looking at my data, so
I'd be looping over Track results and getting information 
higher (and lower) in the chain.

My selects usually involve left joins and things, and I don't
want to generate the query using CDBI shortcuts; I already
have a method that does this, so I want to use this using
something like set_sql.

The catch is, I'm almost always needing data that comes from
several of the tables involved. So if I'm searching for a
Track, my desired output will look something like

  Track Track.title is on album CD.title by Artist.name
  released in CD.year.

To get to what I think is the core of my question (though I
seem to have been doing this poorly lately): If I do a set_sql
like "SELECT Artist.*, CD.*, Track.* FROM...  WHERE... ORDER
BY... LIMIT...", so that I will get back all the columns from
those three tables matching my WHERE (there are other tables
involved in the joins, but I don't need any data from them),
what would be the syntax for printing out my above desired
output, when I'm looping over the results?

Given that I want to get my results in a single query,
without having to go back to the database to retrieve 
related values, is there some better way I should be doing
this in CDBI? 

(I'd prefer to use __ESSENTIAL__ instead of table.*, which
gets everything, except that as mentioned I have more tables
involved in the query than I want results from, so it would
have to be something having the quality of "SELECT
Artist.__ESSENTIAL__, CD.__ESSENTIAL__, Track.__ESSENTIAL__
FROM Artist, CD, Track, Foo, Bar, Baz...", which is probably
not possible.)

Part of my motivation for wanting to move to CDBI for this,
even though everything works fine and fairly quickly using
raw SQL, is that it's getting increasingly more cumbersome
to manipulate the results etc. right now, so if I can keep
the raw SQL part where it really matters--getting the results
out quickly--I can then push the rest of it onto CDBI.

Thanks.

Jesse Sheidlower