My site lets people search for other users by part of their username. The SQL snippet looks like this: WHERE username LIKE "%bob%" This will return the users: jobob, bobby, bobdobbs, bob I want the exact matches (i.e. "bob") to appear at the top of the list. How do I do this? Thanks, Jeremy --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
While playing around with SQL queries I came up with what seems to me to be a hacky way to solve this: SELECT u1.name, u2.name FROM users AS u1 LEFT OUTER JOIN users AS u2 ON u1.id = u2.id AND u2.name LIKE ''bob'' WHERE u1.name LIKE ''%bob%'' ORDER BY u2.name DESC; I''m hoping there''s a cleaner way, because this is ugly. --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
> jobob, bobby, bobdobbs, bob > > I want the exact matches (i.e. "bob") to appear at the top of the > list. How do I do this?select name from users where name like ''%bob%'' order by name = ''bob'' desc ///ark --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
That''s a great solution. Now how do I ensure the ORDER BY statement is sanitized from the user input? On Dec 24, 5:19 pm, Mark Wilden <m...-OCn100epQuBBDgjK7y7TUQ@public.gmane.org> wrote:> > jobob, bobby, bobdobbs, bob > > > I want the exact matches (i.e. "bob") to appear at the top of the > > list. How do I do this? > > select name > from users > where name like ''%bob%'' > order by name = ''bob'' desc > > ///ark--~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 26, 9:27 am, Mozmonkey <JeremyMail...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> That''s a great solution. Now how do I ensure the ORDER BY statement > is sanitized from the user input?Thanks! I think you would use :order => [''name = ? desc'', user_input] in your query. You''d do the same thing with :condition => [''name like ?'', ''%'' + user_input + ''%''] . ///ark --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> select name > from users > where name like ''%bob%'' > order by name = ''bob'' descYou might even want to try order by name like ''bob%'' desc which adds a little order to the matches following the exact match. ///ark --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 12/24/07, Mozmonkey <JeremyMailing-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > My site lets people search for other users by part of their username. > The SQL snippet looks like this: > > WHERE username LIKE "%bob%" > > This will return the users: > > jobob, bobby, bobdobbs, bob > > I want the exact matches (i.e. "bob") to appear at the top of the > list. How do I do this?Use a database that supports fulltext searching. Your query results will return a score or provide a means to rank the results. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html http://www.postgresql.org/docs/8.3/static/textsearch-controls.html#TEXTSEARCH-RANKING -- Greg Donald http://destiney.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?hl=en -~----------~----~----~----~------~----~------~--~---
You could also use soundex to return similar results. On Dec 27, 2007 9:28 AM, Greg Donald <gdonald-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > On 12/24/07, Mozmonkey <JeremyMailing-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > > My site lets people search for other users by part of their username. > > The SQL snippet looks like this: > > > > WHERE username LIKE "%bob%" > > > > This will return the users: > > > > jobob, bobby, bobdobbs, bob > > > > I want the exact matches (i.e. "bob") to appear at the top of the > > list. How do I do this? > > Use a database that supports fulltext searching. Your query results > will return a score or provide a means to rank the results. > > http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html > > > http://www.postgresql.org/docs/8.3/static/textsearch-controls.html#TEXTSEARCH-RANKING > > > -- > Greg Donald > http://destiney.com/ > > > >-- Ryan Bigg http://www.frozenplague.net Feel free to add me to MSN and/or GTalk as this email. --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
> > Use a database that supports fulltext searching. Your query results > will return a score or provide a means to rank the results. >I would, but I don''t believe Rails natively supports fulltext searching, and it seems like more than I need for this situation (correct me if I''m wrong -- I''ve never used fulltext search before and might not properly understand it). My solution was to use the sanitize_sql function, which is part of the ActiveRecord::Base object, so I had to put the find code within the Model. Here''s the snippet: def search(params) if params.has_key?("username") && !params[:username].nil? conditions = "name LIKE :name" values[:name] = "%#{params[:username]}%" options[:order] = sanitize_sql ["name LIKE ? DESC", params[:username]] end # ...Other stuff... # options[:conditions] = [conditions, values] find(:all, options) end --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 26, 10:07 pm, Mozmonkey <JeremyMail...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I would, but I don''t believe Rails natively supports fulltext > searching, and it seems like more than I need for this situation > (correct me if I''m wrong -- I''ve never used fulltext search before and > might not properly understand it).Just be aware that any time you use a condition like LIKE ''%foo%'' the entire table (or index) will be scanned. If the table is large (thousands of names), it might make better sense to use fulltext searching. A hybrid way that I''ve used is to generate my own indexes.> My solution was to use the > sanitize_sql function, which is part of the ActiveRecord::Base object,sanitize_sql is not necessary when you use parameter substitution, as you''re doing.> so I had to put the find code within the Model.The code definitely belongs in the model, so that''s OK.> def search(params) > > if params.has_key?("username") && !params[:username].nil? > conditions = "name LIKE :name"Does that work? I would''ve thought you needed %''s in there. And I don''t understand :name.> values[:name] = "%#{params[:username]}%" > options[:order] = sanitize_sql ["name LIKE ? DESC", > params[:username]]No, the :order part has to be an exact or prefix match to put the exact match at the top. Plus I think there''s some %''s needed there, too. You might want to have another look at the code I posted, and let me/ us know if you have any questions about it. ///ark --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> > Just be aware that any time you use a condition like LIKE ''%foo%'' the > entire table (or index) will be scanned. If the table is large > (thousands of names), it might make better sense to use fulltext > searching. A hybrid way that I''ve used is to generate my own indexes. >Great point. I''ll consider converting it over. Does Rails have support for fulltext queries?> > sanitize_sql is not necessary when you use parameter substitution, as > you''re doing. >From my tests the following causes a SQL error: options[:order] = ["name = ? DESC", params[:username]] Because the resulting SQL is: ORDER BY name = ? DESCbob And entering the value into the :order string doesn''t sanitize it: options[:order] = "name = ''#{params[:username]}'' DESC" So the only solution I could see is using the sanitize_sql function. If there''s a simpler way, I''d love to know.> > if params.has_key?("username") && !params[:username].nil? > > conditions = "name LIKE :name" > > Does that work? I would''ve thought you needed %''s in there. And I > don''t understand :name. >It seems to add the ''%'' characters for me. The resulting SQL is: name LIKE ''%bob%'' The :name is for sanitized replacement, similar to the ''?''. Here''s how it goes together in a find statement: User.find(:all, :conditions => ["name LIKE :name AND foo = :bar", {:name => "bob", :foo => "baz"}]> > No, the :order part has to be an exact or prefix match to put the > exact match at the top. Plus I think there''s some %''s needed there, > too. >Good catch, thanks! Thanks for all the help! --~--~---------~--~----~------------~-------~--~----~ 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?hl=en -~----------~----~----~----~------~----~------~--~---
On Dec 27, 7:23 pm, Mozmonkey <JeremyMail...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Does Rails have support for fulltext queries?I think the acts_as_ferret plugin supports fulltext queries.> From my tests the following causes a SQL error: > > options[:order] = ["name = ? DESC", params[:username]] > > Because the resulting SQL is: > > ORDER BY name = ? DESCbobAh, interesting. I looked carefully at the documention before posting, and it did seem to me that :order allowed substitution. I didn''t actually try it, however. That''s annoying. But yes, then, you''re right - sanitize_sql is the ticket.> > > if params.has_key?("username") && !params[:username].nil? > > > conditions = "name LIKE :name" > > > Does that work? I would''ve thought you needed %''s in there. And I > > don''t understand :name. > > It seems to add the ''%'' characters for me. The resulting SQL is: > name LIKE ''%bob%''Sounds good to me, then! :)> The :name is for sanitized replacement, similar to the ''?''. Here''s > how it goes together in a find statement: > > User.find(:all, :conditions => ["name LIKE :name AND foo = :bar", > {:name => "bob", :foo => "baz"}]Cool - thanks for the info. ///ark --~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---