CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Hegwin
Hegwin

Reputation: 405

How to write Rails finder with several subqueries

This is a library system, people can borrow books here. And each book belongs to a category. We'd like to give people some suggestions according to what kind of books they borrowed most.

Here are four models:

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

And I wrote SQL to find the books


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)

This seems to be working, but I wonder how could I write the finder in the Rails way...

Upvotes: 3

Views: 515

Answers (3)

prashantsahni
prashantsahni

Reputation: 2195

You can do following things, write following in person.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

Though it results in more than 1 query, but its clean, you just have to do:

   @user.suggested_books

Upvotes: 2

cdesrosiers
cdesrosiers

Reputation: 8892

With active record, you can eliminate two of the three subqueries in favor of joins: 

Book.where(
  category_id: Category.limit(1)
    .joins(:books => :borrows)
    .where("borrows.person_id = ?", 10000)
    .group("categories.id")
    .order("COUNT(*) DESC")
    .pluck("categories.id")
  ).joins(:borrows).where("borrows.person_id != ?", 10000)

Still not the best solution because it generates two separate queries (one for the inner query on Category). Depending on your needs, this may not be so bad, if, say, you decide to use the result of the inner query (the most borrowed category of the user in question) for something else.

Upvotes: 1

Florent L.
Florent L.

Reputation: 413

May be something like that :

@person = Person.find(10000)
@categories = @person.books.map{|b| b.category}.uniq!
@suggestions = @categories.map{|c| c.books} - @person.books

In order to have '@person.books' working, you have to add in your Person model :

has_many :books, :through => :borrows

Upvotes: 0

Related Questions