I’m trying to speed up a query that I have running in Rails. It
already uses find_by_sql for performance reasons:
@projects = Project.find_by_sql [“SELECT projects.* FROM projects,
users WHERE projects.id IN (SELECT projects.id FROM projects,
timesheetentries WHERE timesheetentries.startTime>? AND
timesheetentries.startTime<? AND projects.id =
timesheetentries.project_id) AND projects.user_id = users.id”,
@start_date, @end_date]
… and now I’m trying to speed it up even more.
The contents of @projects are printed out in a table, where there is a
lookup to project.client.name. So what I’d ideally like is the client
data to be loaded using an association, but to make that work with my
find_by_sql.
I tried replacing my SQL above with:
SELECT projects., clients. FROM projects, clients WHERE projects.id
IN (SELECT projects.id FROM projects, timesheetentries WHERE
projects.id = timesheetentries.project_id) AND projects.client_id =
client.id
but it went kind of haywire. I think this is because Rails got
confused about projects.id and clients.id and assigned the clients.id
to the id in @projects (I think, maybe… anyway, it didn’t work 
From another part of my app I’ve discovered that when using :include
with find Rails generates SQL like:
SELECT projects.is_chargeable AS t0_r6, clients.id AS t1_r0,
clients.name AS t1_r1, projects.id AS t0_r0, clients.address AS t1_r2,
projects.title AS t0_r1, projects.job_number AS t0_r2, projects.budget
AS t0_r3, projects.client_id AS t0_r4, projects.user_id AS t0_r5 FROM
projects LEFT OUTER JOIN clients ON clients.id = projects.client_id
So I don’t know if I need to emulate that some how or do some other
magic.
Help would be appreciated!
-Rob
–
“The feminist agenda is not about equal rights for women. It is about
a socialist, anti-family political movement that encourages women to
leave their husbands, kill their children, practice witchcraft,
destroy capitalism, and become lesbians.”
– Pat Robertson, fundraising letter, 1992