Rails: searching with a wildcard within a Postgres string array

Question

Our app is running Rails 4.2 and Postgres, and makes use of string arrays. For example:

add_column :things, :identifiers, :string, array: true, default: []

and

Thing.create(identifiers: ['blahsomething', 'other'])
Thing.create(identifiers: ['blahother', 'yetanother'])

I'd like to use ActiveRecord to query for all the things with identifiers LIKE 'blah%'.

With raw SQL, I believe this is possible with Postgres's unnest command: Postgres Query of an Array using LIKE

But how can a LIKE within a string array be queried by an ActiveRecord scope, so that it can be chained with other ActiveRecord queries?

Drew · Accepted Answer · 2015-05-31 17:44:33Z

I did 2 queries to do this. I have created one table like :

sti_development=> select * from posts;
 id |            tags             |         created_at         |         updated_at         
----+-----------------------------+----------------------------+----------------------------
  1 | {blahsomething,other}       | 2015-05-30 06:08:51.668394 | 2015-05-30 06:08:51.668394
  2 | {blahother,yetanother}      | 2015-05-30 06:09:12.350234 | 2015-05-30 06:09:12.350234
  3 | {otherblahother,yetanother} | 2015-05-30 06:09:32.534039 | 2015-05-30 06:09:32.534039
(3 rows)

And here is my Rails query :

[arup@sti (master)]$ rails c
Loading development environment (Rails 4.2.1)
=> Unable to load pry
>> Post.where(id: Post.select("distinct x.id").from(Post.select("id, unnest(tags) tag"), :x).where("x.tag like ?", 'blah%').pluck("x.id"))
   (1.3ms)  SELECT x.id FROM (SELECT id, unnest(tags) tag FROM "posts") x WHERE (x.tag like 'blah%')
  Post Load (1.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (1, 2)
=> #<ActiveRecord::Relation [#<Post id: 1, tags: ["blahsomething", "other"], created_at: "2015-05-30 06:08:51", updated_at: "2015-05-30 06:08:51">, #<Post id: 2, tags: ["blahother", "yetanother"], created_at: "2015-05-30 06:09:12", updated_at: "2015-05-30 06:09:12">]>

Rails does support from method to support inner query.

Another way is to choose a delimter, which is not the part of your tags. Like below :

sti_development=> SELECT "posts".* from "posts" WHERE ('|' || array_to_string(tags, '|')) LIKE ('%|' || 'blah%');
 id |             tags             |         created_at         |         updated_at
----+------------------------------+----------------------------+----------------------------
  1 | {blahsomething,other}        | 2015-05-30 06:08:51.668394 | 2015-05-30 06:08:51.668394
  2 | {blahother,yetanother}       | 2015-05-30 06:09:12.350234 | 2015-05-30 06:09:12.350234
  4 | {otherblahother,blahanother} | 2015-05-30 06:46:12.929428 | 2015-05-30 06:46:12.929428
(3 rows)

sti_development=> \q

Now In Rails.

[arup@sti (master)]$ rails c
Loading development environment (Rails 4.2.1)
=> Unable to load pry
>> Post.where("'|' || array_to_string(tags, '|') LIKE ? ", "%|blah%")
  Post Load (1.5ms)  SELECT "posts".* FROM "posts" WHERE ('|' || array_to_string(tags, '|') LIKE '%|blah%' )
=> #<ActiveRecord::Relation [#<Post id: 1, tags: ["blahsomething", "other"], created_at: "2015-05-30 06:08:51", updated_at: "2015-05-30 06:08:51">, #<Post id: 2, tags: ["blahother", "yetanother"], created_at: "2015-05-30 06:09:12", updated_at: "2015-05-30 06:09:12">, #<Post id: 4, tags: ["otherblahother", "blahanother"], created_at: "2015-05-30 06:46:12", updated_at: "2015-05-30 06:46:12">]>
>>

Collectives™ on Stack Overflow

Rails: searching with a wildcard within a Postgres string array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related