# Help constructing SQL select query on massive table and join **URL:** \ **Category:** Rails\ **Created:** [September 23, 2006, 9:37pm UTC](https://www.ruby-forum.com/t/help-constructing-sql-select-query-on-massive-table-and-join/69755 "2006-09-23T21:37:49Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\ **Post date:** [September 23, 2006, 9:37pm UTC](https://www.ruby-forum.com/t/help-constructing-sql-select-query-on-massive-table-and-join/69755/1 "2006-09-23T21:37:49Z")
Hey everyone, I currently have an app that has been deployed for two years now that has a massive number of objects in it. When a user searches I do a find on all these objects and then go through the objects and look for the search string in a number of fields in the object and related objects. This uses up too much memory and my server is throwing 500s. I was hoping to get some help writing an SQL query that will do the search and only instantiate objects that meet the criteria instead of all objects. I need something that will do a like on several fields, some in other tables involving joins. So something that does a like for a keyword on the following fields problem, site.user.firstname, worker.company where problem is in the current model and site and worker are related models using has\_one Any advice is appreciated. Thanks, Matthew M. [blog.mattmargolis.net](http://blog.mattmargolis.net) ---
**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\ **Post date:** [September 23, 2006, 10:02pm UTC](https://www.ruby-forum.com/t/help-constructing-sql-select-query-on-massive-table-and-join/69755/2 "2006-09-23T22:02:44Z")
Matthew M. wrote: > Hey everyone, > > I currently have an app that has been deployed for two years now that > has a massive number of objects in it. When a user searches I do a find > on all these objects and then go through the objects and look for the > search string in a number of fields in the object and related objects. > This uses up too much memory and my server is throwing 500s. I was > hoping to get some help writing an SQL query that will do the search and > only instantiate objects that meet the criteria instead of all objects. > > I need something that will do a like on several fields, some in other > tables involving joins. > > So something that does a like for a keyword on the following fields > problem, site.user.firstname, worker.company > where problem is in the current model and site and worker are related > models using has\_one This is really to vague for anyone to give specific directions on. Can you do something like this? Almost certainly this, it will almost certainly be something along the lines of find :all, :joins =\> ‘INNER JOIN foos on foos.id = foo\_id, INNER JOIN bars on bars.id = bar\_id’, :conditions =\> [“bars.thing like ? AND foos.property like?”, query\_1, query\_2] Fred ---
**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\ **Post date:** [September 24, 2006, 1:12am UTC](https://www.ruby-forum.com/t/help-constructing-sql-select-query-on-massive-table-and-join/69755/3 "2006-09-24T01:12:22Z")
On Sat, 2006-09-23 at 10:55 -0500, Matthew M. wrote: > I need something that will do a like on several fields, some in other > tables involving joins. > > So something that does a like for a keyword on the following fields > problem, site.user.firstname, worker.company > where problem is in the current model and site and worker are related > models using has\_one > > Any advice is appreciated. * * * Ezra’s ez-where plug-in saves the day. [http://brainspl.at/articles/2006/06/30/new-release-of-ez\_where-plugin](http://brainspl.at/articles/2006/06/30/new-release-of-ez_where-plugin) Craig ---
**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\ **Post date:** [September 24, 2006, 1:46am UTC](https://www.ruby-forum.com/t/help-constructing-sql-select-query-on-massive-table-and-join/69755/4 "2006-09-24T01:46:26Z")
Craig W. wrote: > > only instantiate objects that meet the criteria instead of all objects. > > Any advice is appreciated. I am running this app on a really old version of rails(client refused to pay for upgrades) and since I only need this functionality in one place in the app I was hoping to just use pure find\_by\_sql. I just need an example of how to write sql for finding records where “problem like keyword, site.user.firstname like keyword, and worker.company like keyword” Thank you, Matthew M. [blog.mattmargolis.net](http://blog.mattmargolis.net) ---
**Author:** ![mansfiem](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@mansfiem](https://www.ruby-forum.com/u/mansfiem)\ **Post date:** [September 25, 2006, 10:29am UTC](https://www.ruby-forum.com/t/help-constructing-sql-select-query-on-massive-table-and-join/69755/5 "2006-09-25T10:29:40Z")
On 9/24/06, Matthew M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > This uses up too much memory and my server is throwing 500s. I was > > > > > > Any advice is appreciated. Let’s call the top-level model class “Problem” - you haven’t given us the name, so YMMV: SELECT problems.\* from problems join sites on sites.id=problems.sites\_id join users on users.id=sites.id join workers on workers.id=problems.worker\_id WHERE problems.problem LIKE ‘%keyword%’ OR users.firstname LIKE ‘%keyword%’ OR workers.company LIKE ‘%keyword%’ I also think you should spend some time reading up on and learning more about SQL. This is pretty basic stuff as far as SQL goes, and you’ll find that it will get a lot easier with practice. Cheers, Max