Forum: Ruby on Rails SQL search

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Fresh M. (Guest)
on 2009-02-27 18:29
How I write it in rails format? Articles.find( ????)

SELECT *, MATCH(title, body) AGAINST('$keyword') AS score FROM articles
WHERE MATCH(title, body) AGAINST('$keyword') ORDER BY score DESC
Rob B. (Guest)
on 2009-02-27 18:43
(Received via mailing list)
On Feb 27, 2009, at 11:29 AM, Ga Ga wrote:
> How I write it in rails format? Articles.find( ????)
>
> SELECT *, MATCH(title, body) AGAINST('$keyword') AS score FROM
> articles
> WHERE MATCH(title, body) AGAINST('$keyword') ORDER BY score DESC


Obviously, you'd have to try it yourself, but:

match_part = sanitize_sql(['MATCH(title, body) AGAINST(?)', keyword])
Article.find(:all, :select => "*, #{match_part} AS score",
              :conditions => match_part, :order => 'score DESC')

I'm guessing that the $keyword is from Perl, but I've assumed that you
have a local variable called keyword. Look closely at whether the
conditions end up correct or if things get double-escaped in the final
SQL.

-Rob

Rob B.    http://agileconsultingllc.com
removed_email_address@domain.invalid
Fresh M. (Guest)
on 2009-02-27 19:03
I get:

undefined method `sanitize_sql'
Fresh M. (Guest)
on 2009-02-27 19:34
Ga Ga wrote:
> I get:
>
> undefined method `sanitize_sql'

Help ?
Rob B. (Guest)
on 2009-02-27 21:11
(Received via mailing list)
On Feb 27, 2009, at 11:42 AM, Rob B. wrote:
> match_part =
self.class.
>
> Rob B.    http://agileconsultingllc.com
> removed_email_address@domain.invalid


Sorry, if you look at the docs, sanitize_sql is a protected class
method of ActiveRecord::Base

-Rob

Rob B.    http://agileconsultingllc.com
removed_email_address@domain.invalid
Fresh M. (Guest)
on 2009-02-27 21:56
Rob B. wrote:
> On Feb 27, 2009, at 11:42 AM, Rob B. wrote:
>> match_part =
> self.class.
>>
>> Rob B.    http://agileconsultingllc.com
>> removed_email_address@domain.invalid
>
>
> Sorry, if you look at the docs, sanitize_sql is a protected class
> method of ActiveRecord::Base
>

Is there some alternative way to it, and not to use protected classes?
Rob B. (Guest)
on 2009-02-27 23:11
(Received via mailing list)
On Feb 27, 2009, at 2:56 PM, Ga Ga wrote:
>
> Is there some alternative way to it, and not to use protected classes?
> --


It's not a protected "class", it's a protected method in the class
ActiveRecord::Base which your Article class is a sub-class.  If you
don't like the form of the call, make your own class method like this:

class Article
   def self.keyword_find(keyword)
     match_part = sanitize_sql(['MATCH(title, body) AGAINST(?)',
keyword])
     find(:all, :select => "*, #{match_part} AS score",
          :conditions => match_part, :order => 'score DESC')
   end
end

Then just call "normally":

good_articles = Article.keyword_find('chocolate')

Does that make you happier?  There's nothing wrong with using
sanitize_sql, you just need to call it from the Article class rather
than 'directly'.

-Rob

Rob B.    http://agileconsultingllc.com
removed_email_address@domain.invalid
This topic is locked and can not be replied to.