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.

http://brainspl.at/articles/2006/06/30/new-release-of-ez_where-plugin

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