ActiveRecord support for find by regular expressions?? Is this possible? / Is there a plugin for this?

rubyonrails-talk
1

Hi,

Is there any way of extending ActiveRecord to do something like: <model_object>find_by_description_regex(/.find this.) ??

That is ActiveRecord support for find via use of regular expressions? Perhaps a plugin? Or is there a reason this doesn’t exist?

2

Is there any way of extending ActiveRecord to do something like: <model_object>find_by_description_regex(/.*find this.*) ??

That is ActiveRecord support for find via use of regular expressions? Perhaps a plugin? Or is there a reason this doesn't exist?

Model.find(:all, :conditions => ["column LIKE ?", "%find this%"])

Would do a simple substring search. In some databases the above will *NOT* find "FIND THIS" because LIKE is case sensitive. PostgreSQL is like that. You can use ILIKE in that case, but don't know if that's supported elsewhere.

If you aren't worried about database agnostics you can look to see if your particular database support regex conditions...

For example PostgreSQL: Documentation: 8.3: Pattern Matching

-philip

3

Greg Hauptmann wrote:

Hi,

Is there any way of extending ActiveRecord to do something like: <model_object>find_by_description_regex(/.*find this.*) ??

That is ActiveRecord support for find via use of regular expressions? Perhaps a plugin? Or is there a reason this doesn't exist?

ActiveRecord's job is to turn a common Ruby DSL into a big SQL statement.

Anything that common back-ends can't do, ActiveRecord can't do.

Google [mysql regular expressions] to see if someone has added that to your database back-end. Then use find_by_sql (and scrub any tainted data yourself, to prevent SQL injection attacks like this one: xkcd: Exploits of a Mom )

Until then, just use LIKE:

   part = 'A'    Foo.find_all_by_group_id(group_id,            :conditions => ['name LIKE ?', part + '%' ] )

The % is a wildcard for any length of string - like * in a fileglob match.

4

thanks - I’m on mysql so I’m going to try a named_scope like below (haven’t run/tested it yet):

named_scope :regex_matched, lambda { |regex_str| { :condition => [" description REGEXP ‘?’ " , regex_str] } }

5

One more thing - if your real code is searching for things in descriptions, you may be better off with a full-text indexer like Ferret, Sphinx or Solr. At least in MySQL, a regexp condition will have to scan every row to find matches, which can get slow in a hurry.

--Matt Jones