# SQL search

**URL:** <https://www.ruby-forum.com/t/sql-search/161590>\
**Category:** Rails\
**Created:** [February 27, 2009, 5:29pm UTC](https://www.ruby-forum.com/t/sql-search/161590 "2009-02-27T17:29:28Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 5:29pm UTC](https://www.ruby-forum.com/t/sql-search/161590/1 "2009-02-27T17:29:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 5:43pm UTC](https://www.ruby-forum.com/t/sql-search/161590/2 "2009-02-27T17:43:22Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 6:03pm UTC](https://www.ruby-forum.com/t/sql-search/161590/3 "2009-02-27T18:03:55Z")

</div>

I get:

undefined method `sanitize\_sql’

---

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 6:34pm UTC](https://www.ruby-forum.com/t/sql-search/161590/4 "2009-02-27T18:34:25Z")

</div>

Ga Ga wrote:

> I get:
> 
> undefined method `sanitize\_sql’

Help ?

---

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 8:11pm UTC](https://www.ruby-forum.com/t/sql-search/161590/5 "2009-02-27T20:11:12Z")

</div>

On Feb 27, 2009, at 11:42 AM, Rob B. wrote:

> match\_part =  
> self.class.
> 
> Rob B. [http://agileconsultingllc.com](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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 8:56pm UTC](https://www.ruby-forum.com/t/sql-search/161590/6 "2009-02-27T20:56:20Z")

</div>

Rob B. wrote:

> On Feb 27, 2009, at 11:42 AM, Rob B. wrote:
> 
> > match\_part =  
> > self.class.
> > 
> > Rob B. [http://agileconsultingllc.com](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?

---

<div class="post-metadata">

**Author:** ![giga](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/b9bd4f/32.png) [@giga](https://www.ruby-forum.com/u/giga)\
**Post date:** [February 27, 2009, 10:11pm UTC](https://www.ruby-forum.com/t/sql-search/161590/7 "2009-02-27T22:11:18Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid