Regular expression in Rails scope not capturing the right values

Question

I would like to capture rows with "0" when I have values such as this:

"0,1,8,9,10"
"10"
"0,1"
"4,5"
"1,5,10"

The code that I inherited was capturing values both "0" and "10" which is wrong:

scope :short_result_contains_value, -> (game_id, value) { where(game_id: game_id).where('short_result LIKE ?', "%#{value}%")

I'm trying to use Regex to tighten up the search query but I couldn't get the regex to capture any values at all:

scope :short_result_contains_value, -> (game_id, value) { where(game_id: game_id).where('short_result ~ ?', "/\b(#{value})\b/")}

When I run it, I see in the log that the following query is being run:

SELECT "game_results".* FROM "game_results" WHERE "game_results"."game_id" = $1 AND "game_results"."game_id" = $2 AND (short_result ~ (0')

ActiveRecord is not capturing any of the values when it should be. How can the regex be fixed? Thanks!

I am using Postgres and am on Rails 4.2, Ruby 2.3.7.

Answer 1

You may use

where('short_result LIKE ?', "#{value},%")

It will only return rows starting with value and followed with a comma.

The LIKE pattern should match the whole record. Here, value is 0, so the record should start with 0. Then, a comma should follow. Then the % wildcard matches any 0+ chars there are to the end of the record.

If you plan to match the value as a whole word you need

.where('short_result ~ ?', "\\y#{value}\\y")

The \y in PostgreSQL regex syntax is a word boundary, same as \b in Perl-like regexps.

If you only want to match a value in between commas or start/end of string use

.where('short_result ~ ?', "(^|,)#{value}($|,)")

The (^|,) matches a start of string (^) or (|) a comma, and ($|,) matches either the end of a string ($) or a comma.