Rails - Sep 2006 - 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On Sat, 2006-09-23 at 10:55 -0500, Matthew Margolis
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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Craig White wrote:
> On Sat, 2006-09-23 at 10:55 -0500, Matthew Margolis 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Matthew Margolis 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

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On 9/24/06, Matthew Margolis <mrmargolis-63mtpxcE9Cs@public.gmane.org>
wrote:
>
> Craig White wrote:
> > On Sat, 2006-09-23 at 10:55 -0500, Matthew Margolis 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.
> >>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---