Say I have two entries in my database: 1) Bartlett 2) Peabody A user runs a search, and I want to return all matches to the name above. So the user types in "Bartlett Street", and I want it to return #1 above. My ruby code looks like: @term = params[:terms] @return = Return.find(:all, :conditions=>[''(fieldName <= ?)'', @term]) Note that because the user types in "Street" at the end, I can not do a explicit "==" comparison. When I type in "Peabody" as the search term, why does it also return "Bartlett". I understand that "B" comes before "P" in the alphabet, but I would expect the "<=" to compare on each character in the string, not just the first ... but it seems like it only checks the first character? I guess it is more of a SQL syntax question, but does anyone have a better way to code this in ruby? Seems like such a simple thing to be able to do. Thanks in advance! LAB --~--~---------~--~----~------------~-------~--~----~ 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 2 Oct 2007, at 00:55, LAB wrote:> > When I type in "Peabody" as the search term, why does it also return > "Bartlett". I understand that "B" comes before "P" in the alphabet, > but I would expect the "<=" to compare on each character in the > string, not just the first ... but it seems like it only checks the > first character? >That''s a fairly standard lexicographic ordering: starting from the first character, compare pairs of characters until you find a pair that differ (or until you run out of pairs)> I guess it is more of a SQL syntax question, but does anyone have a > better way to code this in ruby? Seems like such a simple thing to be > able to do. >Correct. You want to use the % sql wildcard, ie "where name like ''Barlett%''" Fred
Hi LAB, LAB wrote:> I guess it is more of a SQL syntax question, but does anyone have a > better way to code this in ruby? Seems like such a simple thing to be > able to do.Not tested, but expect you should experiment with LIKE... Probably end up with something like.... Return.find(:all, :conditions => ''fieldName LIKE %Q{@term}%) HTH, Bill --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks for those suggestions!! Inspired by those, I came up with a
new solution ....
In my case @term is always going to be longer than fieldName, so I
would need to search for the wildcard as part of the fieldName, and
not the @term. Instead, I decided that what I really need to do, is
shorten @term. So if @term = "Bartlett Street", I really only want to
match against the first string there ... "Bartlett" and lose the
"Street" part of the @term.
Here''s what I did:
@term = params[:terms] //"Bartlett Street"
@termArray = @term.split // ["Bartlett", "Street"]
@match = @termArray.first // "Bartlett
if (!@match.nil?)
@return= Return.find(:all, :conditions=>[''(fieldName like
?)'',
@match])
end
That seems to get me what I want ... but it is a pretty unique case.
I''m sure using the wildcard would provide a more general solution, but
I wasn''t sure how to apply the wildcard to the "fieldName" in
the SQL
statement ... I just kept getting syntax errors. This will work fine
for me now.
Thanks again for all the feedback!
LAB
On Oct 1, 8:32 pm, "Bill Walton"
<bill.wal...-xwVYE8SWAR3R7s880joybQ@public.gmane.org>
wrote:> Hi LAB,
>
> LAB wrote:
> > I guess it is more of a SQL syntax question, but does anyone have a
> > better way to code this in ruby? Seems like such a simple thing to be
> > able to do.
>
> Not tested, but expect you should experiment with LIKE... Probably end up
> with something like....
>
> Return.find(:all, :conditions => ''fieldName LIKE %Q{@term}%)
>
> HTH,
> Bill
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
try this:
term = params[:term].split.first
unless term.empty?
match = "%#{term}%"
@return= Return.find(:all, :conditions=>[''(fieldName like
?)'',term])
end
LAB wrote:> Thanks for those suggestions!! Inspired by those, I came up with a
> new solution ....
>
> In my case @term is always going to be longer than fieldName, so I
> would need to search for the wildcard as part of the fieldName, and
> not the @term. Instead, I decided that what I really need to do, is
> shorten @term. So if @term = "Bartlett Street", I really only
want to
> match against the first string there ... "Bartlett" and lose the
> "Street" part of the @term.
>
> Here''s what I did:
>
> @term = params[:terms] //"Bartlett Street"
> @termArray = @term.split // ["Bartlett", "Street"]
> @match = @termArray.first // "Bartlett
>
> if (!@match.nil?)
> @return= Return.find(:all, :conditions=>[''(fieldName like
?)'',
> @match])
> end
>
> That seems to get me what I want ... but it is a pretty unique case.
> I''m sure using the wildcard would provide a more general solution,
but
> I wasn''t sure how to apply the wildcard to the
"fieldName" in the SQL
> statement ... I just kept getting syntax errors. This will work fine
> for me now.
>
> Thanks again for all the feedback!
>
> LAB
>
> On Oct 1, 8:32 pm, "Bill Walton"
<bill.wal...-xwVYE8SWAR3R7s880joybQ@public.gmane.org> wrote:
>
>> Hi LAB,
>>
>> LAB wrote:
>>
>>> I guess it is more of a SQL syntax question, but does anyone have a
>>> better way to code this in ruby? Seems like such a simple thing to
be
>>> able to do.
>>>
>> Not tested, but expect you should experiment with LIKE... Probably end
up
>> with something like....
>>
>> Return.find(:all, :conditions => ''fieldName LIKE
%Q{@term}%)
>>
>> HTH,
>> Bill
>>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---