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

Question

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.

I would also like to ideally use expressions like If you have a fixed set of terms use a ternary tree regex generator. See 175,000 word dictionary regex never takes more than 5 steps to find a word. — user557597
– user557597, Commented Jul 4, 2017 at 19:28

Jack Noble · Accepted Answer · 2017-07-03 23:05:10Z

2

+50

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)

answered Jul 3, 2017 at 23:05

Jack Noble

2,10815 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related