# Find\_by\_sql with associations?

**URL:** <https://www.ruby-forum.com/t/find-by-sql-with-associations/47841>\
**Category:** Rails\
**Created:** [November 14, 2005, 12:55pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-associations/47841 "2005-11-14T12:55:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [November 14, 2005, 12:55pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-associations/47841/1 "2005-11-14T12:55:45Z")

</div>

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

[http://www.robhulme.com/](http://www.robhulme.com/)

> **[robhu](https://robhu.livejournal.com/)**
>
> robhu - the new blog in LiveJournal. There should be new interesting records soon.

---

<div class="post-metadata">

**Author:** ![rob](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/838e76/32.png) [@rob](https://www.ruby-forum.com/u/rob)\
**Post date:** [November 14, 2005, 11:38pm UTC](https://www.ruby-forum.com/t/find-by-sql-with-associations/47841/2 "2005-11-14T23:38:36Z")

</div>

Why not use  
SELECT projects.\*, clients.name…  
instead?  
This will stop the id:s from mixing.

2005/11/14, Robert H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):