Regexp inside of 'where' method

Question

I need a method that will go through database and return appropriate results. in this case its searching for books by author, title, publishing date or ISBN code. I decided to use where() method but i encountered two problems:

1) i have trouble searching by multiple fields. its easy looking for a title:

def self.browse(query)
    if query.nil?
      nil
    else
      self.where("title REGEXP :query", query: query) 
    end  
end

but i dont know how to set it to look for title OR author OR isbn etc. tried

self.where("(title OR author OR publishing_date OR isbn) REGEXP :query", query: query)

but it doesnt work

and second, i want my query to match only a beginning or the end of the word. in mysql Workbench its pretty easy but i have a hard time doing it in Rails. here's what i've tried so far (and failed):

self.where("title REGEXP :query", query: /^(query)*$/)

self.where("title REGEXP /^:query/", query: query)

self.where("title REGEXP :query", query: $"query"^)

Needless to say, on the internet i found many different docs or tutorials, one saying "^" should be at the end, the other it should be at the beginning...

I'd continue debugging by adding `.to_sql` after your `where`-call. That will spit out the SQL query that ActiveRecord generates so you can compare that with what you want. — theodorton, Nov 01 '13 at 14:45

CoolTapes · Answer 1 · 2013-11-01T15:04:18.583

This should do it:

self.where("title REGEXP ? OR author REGEXP ? OR publishing_date REGEXP ? OR isbn REGEXP ?", query, query, query, query)

The "?" will be subbed in order by the included variables. If you want to use the same regexp for each column, then just plug the code in as-is

As for the second part, you may want to check out the LIKE operator.

To match a column which starts with a given string you'd do:

self.where("title LIKE ?", (query + "%"))

And to match a column that ends in a particular string:

self.where("title LIKE ?", ("%" + query))

score 1 · Accepted Answer · answered Nov 01 '13 at 15:01

1) You will want to use parentheses and both AND and OR clauses in your where sql:

(title IS NOT NULL AND title REGEXP :id_query) OR (name IS NOT NULL AND name REGEXP :name_query)

2) You will want to use both ^ (beginning of line) and $ (end of line), like this.

(^something|something$)

Here is an example of the whole thing that I matched against my own code. Replace id and name with your own columns, and put extra OR's in there to match against more columns

Charity.where("(id IS NOT NULL AND id REGEXP :id_query) OR (name IS NOT NULL AND name REGEXP :name_query)", id_query:'1', name_query:'(^a|a$)')

Here is the to_sql output of the above:

Charity.where("(id IS NOT NULL AND id REGEXP :id_query) OR (name IS NOT NULL AND name REGEXP :name_query)", id_query:'1', name_query:'(^a|a$)').to_sql
=> "SELECT `charities`.* FROM `charities`  WHERE ((id IS NOT NULL AND id REGEXP '1') OR (name IS NOT NULL AND name REGEXP '(^a|a$)'))"

i'm almost there but have trouble with $ operator. i use it like u did in example, but it doesnt correctly find records that end with my query. eg when argument is "ord", "lord of the rings" is not in the returned scope. (it is when argument is "lor") — Leo, Nov 01 '13 at 15:11
Ah, that is because $ means "end of line", if you are checking against more than one word in your fields, $ won't work for you. Instead have a look at the answer here for MySQL word boundary matchers: http://stackoverflow.com/questions/7261496/more-efficient-word-boundary-query-in-mysql — Houen, Nov 02 '13 at 11:21

score 0 · Answer 3 · answered Nov 01 '13 at 14:48

0

create your sql query and pass into ActiveRecord execute method,it will excute sql query and do not need to change in ActiveRecord query

sql query = "your sql query"
ActiveRecord::Base.connection.execute(sql query)

answered Nov 01 '13 at 14:48

LHH

3,233
1
20
27

score 0 · Answer 4 · answered Nov 01 '13 at 15:01

0

You can use or:

class MyARModel < ActiveRecord::BAse
  scope :search, ->(rgx) do    
    where('title REGEXP ?', rgx)
    .or('author REGEXP ?' rgx)
    .or('publishing_date REGEXP ?' rgx)
    .or('isbn REGEXP ?' rgx)
  end
#...

answered Nov 01 '13 at 15:01

ichigolas

7,595
27
50

Regexp inside of 'where' method

4 Answers4