current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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">]>
>>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.