Querying an indirect relationship

Question

I have an app that has Users, Libraries and Books.

Users have many Libraries. Libraries have many Users and Books. But Users and Books do not have a direct relationship. Users can only borrow Books from Libraries.

I'm stuck trying to get the collection of books that belongs to the libraries that a user is a member of. In other words, I want to get all of the books in a library that a given user belongs to.

To be clear, I don't want to go through the borrowing association that currently exists between users and books, because I want all the books in that library, not just the ones currently borrowed by the user.

I've tried to come up with this query for hours using SQL and Squeel, without luck... thanks in advance for your help.

Answer 1

You can do something like this...

Book.where(:library_id => user.library_ids)

Or you can do this. Given this model setup...

class Book < ActiveRecord::Base
  belongs_to :library

class Library < ActiveRecord::Base
  has_many :books
  has_many :memberships
  has_many :users, :through => :memberships

class User < ActiveRecord::Base
  has_many :memberships
  has_many :libraries, :through => :memberships
  has_many :books, :through => :libraries

With this, you can ask for @user.books, which in Rails will generate SQL like:

# 1 is @user.id
SELECT "books".* FROM "books" INNER JOIN "libraries" ON "books"."library_id" = "libraries"."id" INNER JOIN "memberships" ON "libraries"."id" = "memberships"."library_id" WHERE "memberships"."user_id" = 1  

Answer 2

Table_User
user_id user_name library_id membership_id

Table_Library
library_id user_id book_id membership_id

Table_Books
books_id book_name library_id


Table_Membership

membership_id user_id library_id

select book_name from Table_Books
inner join Table_Library on Table_Books.library_id=Table_Library.library_id 
inner join Table_User on Table_User.library_id=Table_Library.library_id
where Table_User.library_id = Table_Membership.library_id 
and Table_User.user_id =Table_Membership.user_id