using a regex with datamapper

Question

is it possible to use a regular expression with the DataMapper ".like" conditional syntax?

for example, i would like to find only those users whose hobby starts with the string "skating".

the regex would look something like this:

^skating

currently, i am only able to find all users whose hobby includes the words "skating", thus returning more records than i want.


>> User.all(:hobby.like => "%skating%") 

# what i get

+--------------+---------------+------------------+---------------------+
| login        | first_name    | last_name        | hobby               |
+--------------+---------------+------------------+---------------------+
| jefferson    | Tom           | Jefferson        | skating             |
| adams        | John          | Adams            | skating             |
| washington   | George        | Washington       | speedskating        |
+--------------+---------------+------------------+---------------------+

# what i want to get 

+--------------+---------------+------------------+---------------------+
| login        | first_name    | last_name        | hobby               |
+--------------+---------------+------------------+---------------------+
| jefferson    | Tom           | Jefferson        | skating             |
| adams        | John          | Adams            | skating             |
+--------------+---------------+------------------+---------------------+

>> User.all(:hobby.like => "%^skating%")

# yields no results

thanks for any feedback!

score 0 · Answer 1 · answered Oct 11 '12 at 19:34

0

How about :

    User.all(:hobby.like => "skating%")

answered Oct 11 '12 at 19:34

mkz

212
2
6

score 0 · Answer 2 · answered Jul 21 '17 at 04:03

Datamapper does allow you to query mysql using regex through its :conditions attribute when finding records using :all, :first and :last.

See section marked "Compatibility": http://datamapper.org/docs/find.html

For instance, this works (I have tested and verified):

User.all(:conditions => ['hobby REGEXP ?', '^skating'])

This would be equivalent to MySQL:

SELECT * FROM `user` WHERE (hobby REGEXP '^skating');

In the Datamapper example above, REGEXP refers to MySQL's support for regular expressions:

https://dev.mysql.com/doc/refman/5.7/en/regexp.html#operator_regexp

And, for clarity, the question mark in the Datamapper example above helps to avoid bugs and SQL injection.

using a regex with datamapper

2 Answers2