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