CODEWITHSUNDEEP
ruby-on-railsrubyregexpostgresqlruby-on-rails-4
user1690146
user1690146

Reputation: 393

How to store regex or search terms in Postgres database and evaluate in Rails Query?

I am having trouble with a DB query in a Rails app. I want to store various search terms (say 100 of them) and then evaluate against a value dynamically. All the examples of SIMILAR TO or ~ (regex) in Postgres I can find use a fixed string within the query, while I want to look the query up from a row.

Example:

Table: Post 
column term varchar(256)
(plus regular id, Rails stuff etc)

input = "Foo bar"
Post.where("term ~* ?", input)

So term is VARCHAR column name containing the data of at least one row with the value: 

^foo*$

Unless I put an exact match (e.g. "Foo bar" in term) this never returns a result.

I would also like to ideally use expressions like 

(^foo.*$|^second.*$)

i.e. multiple search terms as well, so it would match with 'Foo Bar' or 'Search Example'.

I think this is to do with Ruby or ActiveRecord stripping down something? Or I'm on the wrong track and can't use regex or SIMILAR TO with row data values like this?

Alternative suggestions on how to do this also appreciated.

Upvotes: 2

Views: 1448

Answers (1)

Jack Noble
Jack Noble

Reputation: 2098

The Postgres regular expression match operators have the regex on the right and the string on the left. See the examples: https://www.postgresql.org/docs/9.3/static/functions-matching.html#FUNCTIONS-POSIX-TABLE

But in your query you're treating term as the string and the 'Foo bar' as the regex (you've swapped them). That's why the only term that matches is the exact match. Try:

Post.where("? ~* term", input)

Upvotes: 2

Related Questions