Help constructing SQL select query on massive table and join

Rails
1

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

2

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

3

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

Craig

4

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

5

On 9/24/06, Matthew M. [email protected] 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