14

I can't find any good articles about how to query array columns in Rails. I came across the need to query an Array column in Rails.

I found from an article teaching how to do basic query here.

Let's follow the example in the article where Book covers many subjects and subjects is stored as an array column:

add_column :books, :subjects, :text, array: true, default: []

Query books that contains a certain subject - e.g. History

Book.where("'history' = ANY (subjects)")

Query books that contains all listed subjects - e.g. Finance AND Business AND Accounting

Book.where("subjects @> ?", "{Finance,Business,Accounting}")

I wonder how I can do the following?

Query books that contains any of the listed subjects - e.g. Fiction OR Biography

Query books that doesn't contain a certain subject - e.g. NOT Physics

Query books that doesn't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)

And is there any Rails way of doing the above queries?

Community
  • 1
  • 1
Chris Yeung
  • 2,613
  • 6
  • 34
  • 57

2 Answers2

30

For,

Query books that contains any of the listed subjects - e.g. Fiction OR Biography

Book.where("subjects &&  ?", "{Fiction,Biography}")

Query books that doesn't contain a certain subject - e.g. NOT Physics

Book.where("subjects <>  ?", "{Physics}")

Query books that don't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)

Book.where.not("subjects &&  ?", "{Physics,Chemistry,Biology}")

You can see the array functions of Postgres for reference.

https://www.postgresql.org/docs/8.2/functions-array.html

manthan andharia
  • 321
  • 3
  • 6
8

  1. Usually, associations are a preferable way of approaching the problem:

    Book has_many :subjects # or has_one/has_and_belongs_to_many

    Subject belongs_to :book # or has_and_belongs_to_many

    And then just create a table subjects, save all your subjects there and you're set up.

  2. Your queries:

Query books that contains any of the listed subjects - e.g. Fiction OR Biography

Book.find_by_sql "SELECT * FROM books WHERE 'Fiction' = ANY (subjects) OR 'Biography' = ANY (subjects)"

Query books that doesn't contain a certain subject - e.g. NOT Physics

Book.where.not("subjects @> ?", "{Physics}")

Query books that doesn't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)

Book.find_by_sql "SELECT * FROM books WHERE books NOT IN (SELECT * FROM books WHERE 'Physics' = ANY (subjects) OR 'Chemistry' = ANY (subjects) OR 'Biology' = ANY (subjects)"
Andrey Deineko
  • 51,333
  • 10
  • 112
  • 145
  • Hmm, I saw that postgres and Rails 4 came out with the array column. I guess sometimes the performance is actually better when storing as array columns? I may ask a new question to see if anyone can evaluate when to use array column and when to use associations – Chris Yeung Jul 22 '15 at 16:55
  • then why is array column created? I assume it is rails intention to adopt some noSQL approach of storing data? I am no expert in it and would like to learn more, I think if Rails 4 / Postgres decides to implement it, there must be a reason? We can offload this discussion to another thread. => http://stackoverflow.com/questions/31569511/when-should-i-use-array-columns-v-s-associations-in-rails – Chris Yeung Jul 22 '15 at 17:00
  • 1
    @ChrisYeung Despite the results of the separate question let me know, if this answer was helpful with regards to queries – Andrey Deineko Jul 22 '15 at 18:03
  • 1
    Bad advice in the first paragraph - there are valid reasons to store array data in database. If there wasn't a good enough use case for it, the data type wouldn't exist (although I agree in OP's case it *should* be an association). However, +1 for actually providing the solution of how to query the array datatype and answering OP's question. – Chris Cirefice Dec 17 '18 at 17:36