From: Shu-yu Guo <shu@...>
Date: 2004-04-21T10:14:01+09:00
Subject: Re: The quest for opensource database...


On Apr 20, 2004, at 18:25, Ryan Pavlik wrote:

> There are a couple problems here, and a few solutions to them, as
> well.  I'll shamelessly plug Criteria here.  Make sure you use the SVN
> version:

Well, this is just abstraction against raw SQL, my problem was where to 
*put* the constructed query. Putting it in initialize is slow, consider 
the following scenario:

There is a forum with 30 posts.
Each post has an user associated with it.
Each user has a profile associate with it.

The data is stored in three tables, posts, users, and profiles. If I 
were to put the query in the initialize, that's 3 queries for each 
post, and can quickly spiral out of control (especially since a good 
amount of time might be wasted on pulling the same user again and 
again).

What I do now is to define two class methods 'select_one' and 
'select_all' (to look uniform with DBI, which I use) in the classes in 
question (in this case, Post, User, and User::Profile). These methods 
fetch data (a WHERE clause is passed in as an argument) from the 
database and pass a Hash to initialize. 'select_all' returns an array.

'select_all' is where the optimization happens, the query plan for the 
scenario described might be something like the following:

1. In Post#select_all, select a list of all posts, then call 
User#select_all with the criterion being all uids which appear in the 
list of posts gotten.
2. In User#select_all, select a list of the users given by the 
criterion, then call User::Profile#select_all with the criterion again 
being all the uids which appear.
3. In User::Profile#select_all, fetch all the profiles described by the 
criterion and return them.

This select_all chain improves performance significantly, but I 
question its OO-ness. For 30 posts, for example, this is 3 queries vs 
3*30 queries.

While criteria doesn't seem to solve my problem (well, present a better 
solution), I think I'll adopt it. The more abstraction the merrier. 
Thanks!
-- 
Lo-lee-ta: the tip of the tongue taking a trip of three
   steps down the palate to tap, at three, on the teeth.
Lo. Lee. Ta.               GUO Shu-yu <shu@rufuran.org>