Ruby on Rails - 有很多，通过:找到多个条件 [英] Ruby on Rails - Has Many, Through: find multiple conditions

问题描述

我意识到仅用文字很难解释我的问题，所以我将用一个例子来描述我想要做的事情.

I realised its quite difficult to explain my problem with only words, so i'm going to use an example to describe what i am trying to do instead.

例如:

#model Book 
has_many: book_genres
has_many: genres, through: :book_genres

#model Genre
has_many: book_genres
has_many: books, through: :book_genres

因此，查找仅属于一种类型的书籍相对简单，例如:

So finding books that belong to one genre only would be relatively straightforward, such as:

#method in books model

def self.find_books(genre)
  @g = Genre.where('name LIKE ?' , "#{genre}").take
  @b = @g.books
  #get all the books that are of that genre
end

所以在 rails 控制台中，我可以执行 Book.find_books("Fiction") 然后我会得到所有 fiction 类型的书籍.

So in rails console i can do Book.find_books("Fiction") and then i would get all the books that are of fiction genre.

但是我怎样才能找到所有既是青年"又是小说"的书?或者，如果我想查询具有 3 种类型的书籍，例如Young Adult"、Fiction"和Romance"，该怎么办?

But how can i find all the books that are both "Young Adult" and "Fiction" ? Or what if i would like to query for books that have 3 genres, such as "Young Adult", "Fiction" and "Romance" ?

我可以做 g = Genre.where(name: ["Young Adult", "Fiction", "Romance"]) 但随后我不能做 g.books 并获取与这 3 种类型相关的所有书籍.

I could do g = Genre.where(name: ["Young Adult", "Fiction", "Romance"]) but subsequent to that i cannot do g.books and get all the books that are related to this 3 genres.

我实际上对活动记录很糟糕，所以我什至不确定是否有更好的方法直接查询 Books 而不是查找 Genre 然后查找所有书籍与之相关.

I am actually quite bad with active record so im not even sure if theres a better way to query through Books directly instead of finding Genre then finding all books that are associated with it.

但是我无法理解的是如何获得具有多种(特定)流派的所有书籍?

But what i cannot wrap my head around is how do i get all the books that have multiple (specific)genres?

更新:

所以当前提供的答案 Book.joins(:genres).where("genres.name" => ["Young Adult", "Fiction", "Romance"]) 有效，但问题是它返回所有类型为 Young Adult OR Fiction OR Romance 的书籍.

So the current answers provided Book.joins(:genres).where("genres.name" => ["Young Adult", "Fiction", "Romance"]) works, but the problem is it returns all books that has the genre of Young Adult OR Fiction OR Romance.

我通过什么查询才能使返回的图书包含所有 3 个流派，而不仅仅是 3 个流派中的 1 个或 2 个?

What query do i pass so that the books return has ALL 3 Genres and not only 1 or 2 out of the 3?

推荐答案

匹配任何给定的流派

以下应该适用于数组和字符串:

Matching any of the given genres

The following should work for both an Array and a String:

Book.joins(:genres).where("genres.name" => ["Young Adult", "Fiction", "Romance"])
Book.joins(:genres).where("genres.name" => "Young Adult")

一般来说，最好是将 Hash 传递给 where，而不是尝试自己编写 SQL 片段.

In general, it's better to pass a Hash to where, rather than trying to write a SQL snippet yourself.

有关更多详细信息，请参阅 Rails 指南:

See the Rails Guides for more details:

• http://guides.rubyonrails.org/active_record_querying.html#hash-conditions
• http://guides.rubyonrails.org/active_record_querying.html#specifying-conditions-on-the-joined-tables

可以构建单个查询，然后将其传递给 .find_by_query:

A single query could be built and then passed to .find_by_query:

def self.in_genres(genres)
  sql = genres.
    map { |name| Book.joins(:genres).where("genres.name" => name) }.
    map { |relation| "(#{relation.to_sql})" }.
    join(" INTERSECT ")

  find_by_sql(sql)
end

这意味着调用 Book.in_genres(["Young Adult", "Fiction", "Romance"]) 将运行如下所示的查询:

This means that calling Book.in_genres(["Young Adult", "Fiction", "Romance"]) will run a query that looks something like this:

(SELECT books.* FROM books INNER JOIN … WHERE genres.name = 'Young Adult')
INTERSECT
(SELECT books.* FROM books INNER JOIN … WHERE genres.name = 'Fiction')
INTERSECT
(SELECT books.* FROM books INNER JOIN … WHERE genres.name = 'Romance');

它的好处是让数据库完成合并结果集的繁重工作.

It has the upside of letting the database do the heavy lifting of combining the result sets.

缺点是我们使用的是原始 SQL，所以我们不能将它与其他 ActiveRecord 方法链接起来，例如 Books.order(:title).in_genres(["Young Adult", "Fiction"]) 将忽略我们尝试添加的 ORDER BY 子句.

The downside is that we're using raw SQL, so we can't chain this with other ActiveRecord methods, for example Books.order(:title).in_genres(["Young Adult", "Fiction"]) will ignore the ORDER BY clause we've tried to add.

我们还将 SQL 查询作为字符串进行处理.我们可以使用 Arel 来避免这种情况，但是 Rails 和 Arel 处理绑定查询值的方式使这变得非常复杂.

We're also manipulating SQL queries as strings. It's possible we could avoid this using Arel, but the way Rails and Arel handle binding query values makes this pretty complicated.

也可以使用多个查询:

def self.in_genres(genres)
  ids = genres.
    map { |name| Book.joins(:genres).where("genres.name" => name) }.
    map { |relation| relation.pluck(:id).to_set }.
    inject(:intersection).to_a

  where(id: ids)
end

这意味着调用 Book.in_genres(["Young Adult", "Fiction", "Romance"]) 将运行四个如下所示的查询:

This means that calling Book.in_genres(["Young Adult", "Fiction", "Romance"]) will run four queries that look something like this:

SELECT id FROM books INNER JOIN … WHERE genres.name = 'Young Adult';
SELECT id FROM books INNER JOIN … WHERE genres.name = 'Fiction';
SELECT id FROM books INNER JOIN … WHERE genres.name = 'Romance';
SELECT * FROM books WHERE id IN (1, 3, …);

这里的缺点是，对于 N 种类型，我们要进行 N+1 次查询.好处是这可以与其他 ActiveRecord 方法结合使用；Books.order(:title).in_genres(["Young Adult", "Fiction"]) 将进行流派过滤，并按标题排序.

The downside here is that for N genres, we're making N+1 queries. The upside is that this can be combined with other ActiveRecord methods; Books.order(:title).in_genres(["Young Adult", "Fiction"]) will do our genre filtering, and sort by title.

这篇关于Ruby on Rails - 有很多，通过:找到多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！