Rails OR query with postgres array

Question

I have a tagging system in rails using postgres' array data type. I'm trying to write a scope that will return any posts that include a tag. So far I have this scope working:

scope :with_tag, ->(tag) { where("tags @> ARRAY[?]", tag) }

I want to extend this scope so that I can query on multiple tags at the same time, ideally something like:

Post.with_tags(['some', 'tags', 'to', 'query'])

Which would return any Post that have one of those tags. I've thought about making a class method to handle iterating over the input array:

def self.with_tags(args)
  # start with empty activerecord relation
  # want to output AR relation
  results = Post.none
  args.each do |tag|
    results = results.concat(Post.with_tag(tag))
  end
  results.flatten
end

but this approach smells funny to me because it's creating a new query for each argument. It also doesn't return an ActiveRecord::Relation because of flatten, which I would really like to have as the output.

Can I accomplish what I'm after in a scope with an OR query?

You're really trying to do an array intersection right? You have an array of strings in the tags column and an array of strings in the Ruby args variable and you want rows where those arrays overlap? — mu is too short
– mu is too short, Commented Dec 31, 2014 at 18:25
Yes, I think. Except I want all of the tags that contain any of the values in args, they do not need to match all of the args — user2936314
– user2936314, Commented Dec 31, 2014 at 18:27

Jiří Pospíšil · Accepted Answer · 2014-12-31 18:25:34Z

9

I'm not running the code but I think the && operator does what you want:

scope :with_tags, ->(tags) { where("tags && ARRAY[?]", tags) }

answered Dec 31, 2014 at 18:25

Jiří Pospíšil

14.4k2 gold badges44 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liuda Over a year ago

is there any ability to perform case-insensitive search?

Collectives™ on Stack Overflow

Rails OR query with postgres array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related