help constructing SQL select query on massive table and joins

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 Margolis blog.mattmargolis.net

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.

Craig White 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

Any advice is appreciated.     

---- Ezra's ez-where plug-in saves the day.

Ruby on Rails Blog / What is Ruby on Rails for?

Craig

>   

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 Margolis blog.mattmargolis.net

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