[CDBI] Using Left/Right joins

Charles Gordon charles.gordon at gmail.com
Mon Feb 6 05:14:22 GMT 2006


I've been working on a very simple Mason-based web front-end to a set of
database tables. I've been using Class::DBI::Sweet to handle all the
database interaction, and so far it has been great. I noticed early on that
lists of objects from the database took N+1 queries to fetch (one query to
get the list, and then N queries to "flesh" the foreign keys). I switched to
using the "prefetch" attribute available via Sweet, and now its down to one
query for the list, which is great.

However, the "prefetch" attribute only handles "inner" joins, and there are
a number of cases in which I need a "left" join. These cases generally
involve optional relationships. Here is a boiled down example:

CREATE TABLE companies (
    id int unsigned not null primary key,
    ...
);

CREATE TABLE people (
    id int unsigned not null primary key,
    ...
);

CREATE TABLE clients (
   id int unsigned not null primary key,

   company int unsigned not null,
   client int unsigned not null,
   agent int unsigned default null,

   ...
);

The "clients" table establishes a relationship between a "company" and a
"person". Sometimes the "client" is represented by a specific "agent",
sometimes not. I realize this could be better represented in a full 3NF
version that included a "client_agent" table, but denormalizing this table
buys me a lot of query speed, and doesn't complicate the model much (there
are a couple consistency checks I have to do manually, but CDBI makes that
easy).

To get a list view of all clients for a given company, along with the agent
information (if there is any, or null if there isn't), I would run this:

SELECT *
FROM clients
LEFT JOIN people ON people.id=clients.agent
WHERE company=?;

This is the query that CDBI::Sweet doesn't currently provide any method to
produce. Does anyone know a good way to generate such a query, and have the
joined table fields automatically converted to the appropriate objects? I
could probably write this all myself with a set_sql method, but it would be
complicated, and not very general. Looking at the Sweet code, it looks like
it should be easy to add another relationship type, and then add some code
to _resolve_join to have it generate the appropriate "LEFT JOIN ..." as part
of the FROM clause.

Has anyone found a better way to introduce LEFT JOIN's into prefetched-style
queries with CDBI?

Thanks!
Charles Gordon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digitalcraftsmen.net/mailman/private/classdbi/attachments/20060205/8ddc72f8/attachment.htm


More information about the ClassDBI mailing list