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.

Try `where('short_result LIKE ?', "#{value},%")` if you plan to only return rows starting with `value` and followed with a comma. — Wiktor Stribiżew, Oct 09 '18 at 11:14
I got the following query in the log: (short_result LIKE '0, %'), it also didn't return any results.. — MLZ, Oct 09 '18 at 11:21
Are rows always sorted like in example (so that 0 is always at beginning)? — Vasfed, Oct 09 '18 at 11:23

Wiktor Stribiżew · Accepted Answer · 2018-10-09T11:51:16.830

2

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.

edited Oct 09 '18 at 11:51

answered Oct 09 '18 at 11:30

Wiktor Stribiżew

607,720
39
448
563

This will not work for values that are not in the beginning or single values without a comma – Vasfed Oct 09 '18 at 11:33
@Vasfed Sure, it should not match those NOT in the beginning, see OP. – Wiktor Stribiżew Oct 09 '18 at 11:33
See comments to OP - values are in random order, also even if the data is like in original example - try searching for `5` or `9` – Vasfed Oct 09 '18 at 11:38
@Vasfed I added several additional solutions. – Wiktor Stribiżew Oct 09 '18 at 11:41
I believe the correct one is the one with whole word matching – Vasfed Oct 09 '18 at 11:49
@Vasfed you are right, the one that worked for me was the one for the whole word matching. Thank you both for your feedback, much appreciated. – MLZ Oct 09 '18 at 13:01

Regular expression in Rails scope not capturing the right values

1 Answers1