[CDBI] Using Left/Right joins

05:14 on 06 Feb 2006

--===============1978057216==
Content-Type: multipart/alternative; 
	boundary="----=_Part_25568_18143242.1139202862485"

------=_Part_25568_18143242.1139202862485
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 t=
o
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=3Dclients.agent
WHERE company=3D?;

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 par=
t
of the FROM clause.

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

Thanks!
Charles Gordon

------=_Part_25568_18143242.1139202862485
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I've been working on a very simple Mason-based web front-end to a set of da=
tabase 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 o=
f objects from the database took N+1 queries to fetch (one query to get the=
 list, and then N queries to &quot;flesh&quot; the foreign keys). I switche=
d to using the &quot;prefetch&quot; attribute available via Sweet, and now =
its down to one query for the list, which is great.
 However, the &quot;prefetch&quot; attribute only handles &quot;inne=
r&quot; joins, and there are a number of cases in which I need a &quot;left=
&quot; join. These cases generally involve optional relationships. Here is =
a boiled down example:
 CREATE TABLE companies ( &nbsp;&nbsp;&nbsp; id int unsigned not =
null primary key, &nbsp;&nbsp;&nbsp; ... ); CREATE TABLE peopl=
e ( &nbsp;&nbsp;&nbsp; id int unsigned not null primary key, &nbsp;&n=
bsp;&nbsp; ... ); CREATE TABLE clients ( &nbsp;&nbsp; id int u=
nsigned not null primary key,
 &nbsp;&nbsp; company int unsigned not null, &nbsp;&nbsp; client =
int unsigned not null, &nbsp;&nbsp; agent int unsigned default null, =
 &nbsp;&nbsp; ... ); The &quot;clients&quot; table establishes=
 a relationship between a &quot;company&quot; and a &quot;person&quot;. Som=
etimes the &quot;client&quot; is represented by a specific &quot;agent&quot=
;, sometimes not. I realize this could be better represented in a full 3NF =
version that included a &quot;client_agent&quot; table, but denormalizing t=
his table buys me a lot of query speed, and doesn't complicate the model mu=
ch (there are a couple consistency checks I have to do manually, but CDBI m=
akes that easy).
 To get a list view of all clients for a given company, along with t=
he 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=3Dc=
lients.agent
 WHERE company=3D?; This is the query that CDBI::Sweet doesn't cu=
rrently provide any method to produce. Does anyone know a good way to gener=
ate such a query, and have the joined table fields automatically converted =
to the appropriate objects? I could probably write this all myself with a s=
et_sql method, but it would be complicated, and not very general. Looking a=
t the Sweet code, it looks like it should be easy to add another relationsh=
ip type, and then add some code to _resolve_join to have it generate the ap=
propriate &quot;LEFT JOIN ...&quot; as part of the FROM clause.
 Has anyone found a better way to introduce LEFT JOIN's into prefetc=
hed-style queries with CDBI? Thanks! Charles Gordon =20

------=_Part_25568_18143242.1139202862485--

--===============1978057216==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi

--===============1978057216==--