My migration looks like this:
create table :posts do |t|
t.string :tags, array: true, default: []
end
How can I get an array of all the unique tags stored under tags in all posts?
Add a comment
|
3 Answers
You can do it inside the database with:
select distinct unnest(tags) from posts
So if you just want the strings then you can go straight to the database with:
tags = Post.connection.select_rows('select distinct unnest(tags) from posts').flatten
If there is a lot overlap amongst the tags arrays or a lot of arrays then that should be faster then pulling all the arrays out of the database and doing the data wrangling in Ruby.
3 Comments
nzifnab
I'd say this answer is better than my own. I wasn't sure of the method to query on an array value in the database, but I was pretty sure it exists. I might suggest trying to change the call slightly by doing something like
Post.select('DISTINCT unnest(tags) AS uniq_tags').pluck(:uniq_tags).flatten -- select_rows seems like a bit of a hackish way of getting at it.
mu is too short
@nzifnab: You could wrap all the AR stuff around it if you wanted but I'm as comfortable with SQL as I am with Ruby and I find the usual "nothing but Ruby" attitude a bit puzzling; presumably you'd wrap this in a
Post class method anyway. I don't think you'd need that flatten call, you need it with select_rows because it returns an AoA even if each row in the result set has only one entry.nzifnab
Good point, don't need flatten. And ya either way will get you the same result. I suppose it's a matter of preference.
I believe you could also do this:
Post.pluck(:tags).flatten.uniq
If you have a ton of posts this may be a fairly significant hit on performance though...
2 Comments
tadman
There's really no way around this as
Post.pluck(:tags).uniq would return all the unique tag-sets. This is the down-side to using an array column instead of a many-to-many tag association table.mu is too short
@tadman: There are ways around it, PostgreSQL has a fairly rich set of array operators and functions.
As soon as I posted this, I found a working solution, although there may be a more elegant way of solving this:
Post.select(:tags).map(&:tags).flatten.uniq
I was being thrown off by the array of objects, solved by the .map.