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


#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?


Greg
http://blog.gregnet.org/


#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
http://www.postgresql.org/docs/8.3/static/functions-matching.html

-philip


#3

Greg H. 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: http://xkcd.com/327/ )

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.


Phlip


#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] }
}

On Fri, Jan 16, 2009 at 11:37 AM, Phlip removed_email_address@domain.invalid wrote:

ActiveRecord’s job is to turn a common Ruby DSL into a big SQL statement.
part = ‘A’


Greg
http://blog.gregnet.org/


#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 J.

On Jan 15, 8:42 pm, “Greg H.” removed_email_address@domain.invalid