Why not use
SELECT projects.*, clients.name...
instead?
This will stop the id:s from mixing.
2005/11/14, Robert Hulme
<rob-7tdvfriZBvFWk0Htik3J/w@public.gmane.org>:>
> 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 <http://projects.id> IN (SELECT
projects.id<http://projects.id>FROM projects,
> timesheetentries WHERE timesheetentries.startTime>? AND
> timesheetentries.startTime<? AND projects.id <http://projects.id>
> timesheetentries.project_id) AND projects.user_id =
users.id<http://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 <http://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<http://projects.id>
> IN (SELECT projects.id <http://projects.id> FROM projects,
> timesheetentries WHERE
> projects.id <http://projects.id> = timesheetentries.project_id) AND
> projects.client_id > client.id <http://client.id>
>
> but it went kind of haywire. I think this is because Rails got
> confused about projects.id <http://projects.id> and
clients.id<http://clients.id>and assigned the
> clients.id <http://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
<http://clients.id> AS
> t1_r0,
> clients.name <http://clients.name> AS t1_r1,
projects.id<http://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 <http://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
>
> http://www.robhulme.com/
> http://robhu.livejournal.com/
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails