ruby-on-rails - 如何使用多个子查询编写 Rails 查找器

Question

这是一个图书馆系统，人们可以在这里借书。每本书都属于一个类别。我们想根据人们借阅最多的书籍类型给他们一些建议。

这里有四个模型:

class Person < AR
  has_many :borrows
end


class Borrow < AR
  belongs_to :person
  belongs_to :book
end

class Category < AR
  has_many :books
end

class Book < AR
  has_many :borrows
  belongs_to :category
end

然后我写了SQL来找书

SELECT * FROM books WHERE category_id = 
  (SELECT category_id FROM books WHERE id IN
    (SELECT book_id FROM borrows WHERE person_id =10000)
  GROUP BY category_id ORDER BY count(*) DESC LIMIT 1)
AND id NOT IN
  (SELECT book_id FROM borrows WHERE person_id =10000)

这似乎可行，但我想知道如何以 Rails 方式编写查找器...

Answer 1

你可以做下面的事情，写下面的人.rb

has_many :books, :through => :borrows
has_many :categories_of_books, :through => :books,  :source => :category

&

def suggested_books
 Book.where("category_id IN (?) AND id NOT IN (?)",  self.categories_of_books, self.books)
end

虽然它会导致超过 1 个查询，但它很干净，您只需要做:

   @user.suggested_books