如何在Rails 4中查询数组列？ [英] How to query array columns in Rails 4?
问题描述
在Rails中找不到关于如何查询数组列的好文章。我遇到了需要在Rails中查询Array列的需求。
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.
让我们遵循文章中的示例，其中 Book 涵盖了许多主题和主题存储为数组列：
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: []
查询包含特定主题的书籍-例如历史记录
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想知道我该怎么做？
I wonder how I can do the following?
查询包含任何列出的主题的图书-例如小说或传记
查询不包含特定主题的图书-例如非物理
查询不包含任何主题的图书-例如不是（物理或化学或生物学）
Query books that doesn't contain ANY of the subjects - e.g. NOT (Physics OR Chemistry OR Biology)
有没有 Rails 的方法
推荐答案
-
不要在db中存储数组。曾经几乎总会有更好的解决方案（关联）：
书has_many：subjects＃或has_one / has_and_belongs_to_many
Subject Emirates_to：book＃或has_and_belongs_to_many
然后仅创建一个表主题，保存
And then just create a table subjects, save all your subjects there and you're set up.
您的查询：
查询包含任何列出主题的书籍-例如小说或
传记
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)"
这篇关于如何在Rails 4中查询数组列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
