select sum(table_3.col_1) as number,
(CASE
when table_1.line_1 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_1, '(?i)City\s*(\d+)'))[1])
when table_1.line_2 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_2, '(?i)City\s*(\d+)'))[1])
when table_1.line_3 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_3, '(?i)City\s*(\d+)'))[1])
when table_1.line_4 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_4, '(?i)City\s*(\d+)'))[1])
when table_1.line_5 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_5, '(?i)City\s*(\d+)'))[1])
when table_1.line_6 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_6, '(?i)City\s*(\d+)'))[1])
when table_1.line_7 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_7, '(?i)City\s*(\d+)'))[1])
when table_1.line_8 ~* '(?i)City\s*(\d+)' then 'C' || (select (regexp_matches(table_1.line_8, '(?i)City\s*(\d+)'))[1])
else 'City'::varchar
end
) as string_result
from table_1
join table_2 on table_1.id = table_2.table_1_id
join table_3 on table_2.id = table_3.table_2_id
join table_4 on table_3.table_4_id = table_4.id
where table_4.id = 2
group by string_result
When I run the above query in pgadmin, I get all the information I am expecting. over 20 rows. But when I run this in rails using a heredoc and ActiveRecord::Base.connection.execute(sql) I only get the last row returned. I've done this almost exact same thing(different data of course) in other projects with great success. I'm at a loss at to why here only one row is returned when run in Rails
UPDATE: I was able to figure out why this is happening, but still having issues correcting it. The problem is that when the query is passed into the ActiveRecord::Base.connection.execute(sql) it is escaping the space and digit requirements of my regex. I'm still going through the postgres pattern matching docs, but I have a hard time with regex's at the moment since I'm still pretty new at using them. But I'm trying to capture the digits in strings matching something like 'City 24' where it matches with City case insensitvely
Finally starting to wrap my head around regexes. I was finally able to solve my issue. It was the digits capture. It worked in pgadmin with (\d+) but when passed through the ActiveRecord::Base.connections.execute(sql) it didn't work for some reason. The fix was to give it a range with [0-9] and since the + is simply one or more I gave it {1,2}. So now the regex is (?i)City\s*([0-9]{1,2}) This will match the string case insensitively for 'City' followed by any number of spaces. And the will grab the first two digits after that.