I''ve got a simple app where I search for some records in a database doing the following in the controller: @filings = Filing.find_all(["company_name = ?", @companyToSearchFor]) Which works nice except for the fact that I need to use the exact name to find what I''m looking for. How would I find the records that just match some partial version of the name. For example, right now, I''ve got a record "DANA CORP". I''d like to just search for "DANA". John
On 20/04/05, John Marsan <jmarsan-JxUet7QE5mQXhy9q4Lf3Ug@public.gmane.org> wrote:> I''ve got a simple app where I search for some records in a database doing > the following in the controller: > > @filings = Filing.find_all(["company_name = ?", @companyToSearchFor]) > > Which works nice except for the fact that I need to use the exact name to > find what I''m looking for. How would I find the records that just match > some partial version of the name. For example, right now, I''ve got a record > "DANA CORP". I''d like to just search for "DANA".@filings = Filing.find_all(["company_name LIKE ?", "%"+@companyToSearchFor+"%"]) -- Phillip Hutchings http://www.sitharus.com/ sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org
On 4/20/05, Phillip Hutchings <sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 20/04/05, John Marsan <jmarsan-JxUet7QE5mQXhy9q4Lf3Ug@public.gmane.org> wrote: > > I''ve got a simple app where I search for some records in a database doing > > the following in the controller: > > > > @filings = Filing.find_all(["company_name = ?", @companyToSearchFor]) > > > > Which works nice except for the fact that I need to use the exact name to > > find what I''m looking for. How would I find the records that just match > > some partial version of the name. For example, right now, I''ve got a record > > "DANA CORP". I''d like to just search for "DANA". > > @filings = Filing.find_all(["company_name LIKE ?", "%"+@companyToSearchFor+"%"])This will also match, "BANDANA CORP" i.e. matches in the middle of the string. If you only want to match from the beginning of the record use: @filings = Filing.find_all(["company_name LIKE ?", "#{@companyToSearchFor}%"]) There''s a performance increase from doing it this way as well, but I wouldn''t worry about that. If you''re using mysql, you should look into FULLTEXT indexes as they''re much better suited to searching than LIKE comparisons> -- > Phillip Hutchings > http://www.sitharus.com/ > sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
Thanks for the help with this. The first solution (from Phillip Hutchings) didn''t work but the second one (from Michael Koziarski) does. The first results in an error of some sort. I ran out of time today to troubleshoot so no further info right now. John ----- Original Message ----- From: "Michael Koziarski" <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> To: <sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org>; <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> Sent: Tuesday, April 19, 2005 10:49 PM Subject: Re: [Rails] Searching for Record in Database On 4/20/05, Phillip Hutchings <sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 20/04/05, John Marsan <jmarsan-JxUet7QE5mQXhy9q4Lf3Ug@public.gmane.org> wrote: > > I''ve got a simple app where I search for some records in a database > > doing > > the following in the controller: > > > > @filings = Filing.find_all(["company_name = ?", @companyToSearchFor]) > > > > Which works nice except for the fact that I need to use the exact name > > to > > find what I''m looking for. How would I find the records that just match > > some partial version of the name. For example, right now, I''ve got a > > record > > "DANA CORP". I''d like to just search for "DANA". > > @filings = Filing.find_all(["company_name LIKE ?", > "%"+@companyToSearchFor+"%"])This will also match, "BANDANA CORP" i.e. matches in the middle of the string. If you only want to match from the beginning of the record use: @filings = Filing.find_all(["company_name LIKE ?", "#{@companyToSearchFor}%"]) There''s a performance increase from doing it this way as well, but I wouldn''t worry about that. If you''re using mysql, you should look into FULLTEXT indexes as they''re much better suited to searching than LIKE comparisons> -- > Phillip Hutchings > http://www.sitharus.com/ > sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.1 - Release Date: 4/20/2005
On 21/04/05, John Marsan <jmarsan-JxUet7QE5mQXhy9q4Lf3Ug@public.gmane.org> wrote:> Thanks for the help with this. The first solution (from Phillip Hutchings) > didn''t work but the second one (from Michael Koziarski) does. The first > results in an error of some sort. I ran out of time today to troubleshoot > so no further info right now.That''s what happens when I type things off the top of my head, it was probably the string concatination - I was working on C# and my brain didn''t completly flip back in to Ruby mode. This works in PostgreSQL: find_all(["lower(name) LIKE ?", "%#{@params[''searchname''].downcase}%"]) -- Phillip Hutchings http://www.sitharus.com/ sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org