Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to query array columns in Rails 4?

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?

like image 895
Chris Yeung Avatar asked Jul 22 '15 16:07

Chris Yeung


2 Answers

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

like image 141
manthan andharia Avatar answered Sep 28 '22 11:09

manthan andharia


  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)"
like image 31
Andrey Deineko Avatar answered Sep 28 '22 12:09

Andrey Deineko