I need to select rows form a table based on the first letter of the name
column. Like a teledex there will be a letter provided by a request.
Currently i have
@artists = Artist.find(:all,
:conditions => [ ''LOWER(name) LIKE ?'',
''%'' + params[:id].downcase + ''%'' ],
:order => ''name ASC'',
:limit => 8)
However this returns rows that have the letter anywhere in the name
value. I need only rows that have the first letter of their value.
Thanks for any help
--
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?hl=en
-~----------~----~----~----~------~----~------~--~---
thats because you are using %[letter]% % is a wildcard and so you are asking the db to return any row where [letter] appears in the name column. if you want any last name that starts with [letter], use ''[letter]%'' Chris On 12/3/06, Stewart <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > I need to select rows form a table based on the first letter of the name > column. Like a teledex there will be a letter provided by a request. > Currently i have > > @artists = Artist.find(:all, > :conditions => [ ''LOWER(name) LIKE ?'', > ''%'' + params[:id].downcase + ''%'' ], > :order => ''name ASC'', > :limit => 8) > > However this returns rows that have the letter anywhere in the name > value. I need only rows that have the first letter of their value. > > Thanks for any help > > -- > 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?hl=en -~----------~----~----~----~------~----~------~--~---
On Monday 04 December 2006 01:17, Stewart wrote:> I need to select rows form a table based on the first letter of the > name column. Like a teledex there will be a letter provided by a > request. Currently i have > > @artists = Artist.find(:all, > :conditions => [ ''LOWER(name) LIKE ?'', > ''%'' + params[:id].downcase + ''%'' ], > :order => ''name ASC'', > :limit => 8) > > However this returns rows that have the letter anywhere in the name > value. I need only rows that have the first letter of their value.params[:name].downcase + ''%'' ], Remove the first ''%'', it''s a placeholder for an arbitrary string. I take it, params[:id] was not what you intended to use. Also, bear in mind that if you have a database index on the name column it will most likely not be consulted as names are verbatim in the index, not in lowercase. To make use of an index in a case like this, the DBMS has to support functional indexes. MySQL apparently is too dumb for it, but PostgreSQL has this feature. http://www.postgresql.org/docs/8.1/interactive/indexes-expressional.html Michael -- Michael Schuerig mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org http://www.schuerig.de/michael/ --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---