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:
rofile). 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:
rofile#select_all with the criterion again
being all the uids which appear.
3. In User:
rofile#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!
