CODEWITHSUNDEEP
mysqlruby-on-railsrubyregex
Leo
Leo

Reputation: 2103

Regexp inside of 'where' method

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

Upvotes: 1

Views: 128

Answers (4)

CoolTapes
CoolTapes

Reputation: 417

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))

Upvotes: 1

ichigolas
ichigolas

Reputation: 7725

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

Upvotes: 0

Houen
Houen

Reputation: 1059

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$)'))"

Upvotes: 1

LHH
LHH

Reputation: 3323

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)

Upvotes: 0

Related Questions