I'm not sure if this is a rails question or sql. Anyway...
I have three models: book, author, publisher
I have has_and_belongs_to_many relationships between book and author
and between book and publisher, so tables:
authors_books and books_publishers
Now, I want to find books that match a particular author(s) and also a
particular publisher(s).
class Book < ActiveRecord::Base
has_and_belongs_to_many :publishers
has_and_belongs_to_many :authors
def self.find_with_authors_and_publishers(id)
find(id, :include => [:authors, :publishers])
end
end
book = Book.find_with_authors_and_publishers(1)
book.authors # => array of authors
book.publishers # => array of publishers
note that the include is what creates the joins and reduces it all to
one query.
if you do not use the include it will still work but will make three
separate queries but will not query again but will pull the results from
a cache because the authors/publishers methods are singletons. the
process created by the include is referred to as eager loading.
def self.find_all_by_author_and_publisher(author, publisher)
q =<<-END
select b.* from books b
join books_publishers bp on bp.book_id = b.id
join author_books ab on ab.book_id = b.id
where bp.publisher_id = #{publisher.id}
and ab.author_id = #{author.id}
END
find_by_sql(q)
end
end
NOT TESTED.. salt to taste..
ActiveRecord simply rules the roost but sometimes you are quicker to
just get your hands dirty for the sake of simplicity and speed..
(Loading all books in your db as you suggested earlier is akin to
suicide and you seem like a really nice guy so please reconsider)
Eager loading makes perfect sense in instances where you KNOW you will
be using the associations.. If you just need the books then don't bother
with it.. (Beware of premature optimization and overpriced consultants)
No, I'm pretty sure it works just fine. But you mean it wouldn't be
performant.
as you would be left with a lot of book on
both sides and then would have to find the intersection between the 2
sets which could be quite costly depending upon the number of books,
authors, and publishers within the dataset.
It _could_ be costly, but is it, for this application?
There are many solutions to this particular problem but only a few of them scale appropriately
Do we know this has to scale?
Seriously, of course I understand your objections (and of course I
understand the relative inefficiency of my suggestion), but when in
doubt, I just do the simplest thing that could possibly work and move
on. Whether the intersection thing "could possibly work" is a question
for the OP. I don't think it rates an automatic "this won't work."
At any rate, it was an idea, not necessarily a recommendation.
That doesn't work (I tried it). My understanding is that the
find_by_<attribute> dynamic finder only works if that attribute is in
the Model you're calling it on, and in table relationships, the
attribute exists only in the belongs_to side of a has_xxx
relationship.
def self.find_all_by_author_and_publisher(author, publisher)
q =<<-END
select b.* from books b
join books_publishers bp on bp.book_id = b.id
join author_books ab on ab.book_id = b.id
where bp.publisher_id = #{publisher.id}
and ab.author_id = #{author.id}
END
find_by_sql(q)
end
end
That works, thank you.
Now, what about a situations where a user can choose multiple values
for each attribute? For example, you list every author in the Author
table and the user can select which ones he/she is interested in. The
user can choose 1 author, 5 authors or all of them.
Now, what about a situations where a user can choose multiple values
for each attribute? For example, you list every author in the Author
table and the user can select which ones he/she is interested in. The
user can choose 1 author, 5 authors or all of them.
I've figured this out. Just to modify IIan's code:
class Book < ActiveRecord::Base
def self.find_all_by_author_and_publisher(author, publisher)
q =<<-END
select b.* from books b
join books_publishers bp on bp.book_id = b.id
join author_books ab on ab.book_id = b.id
where bp.publisher_id IN (#{publisher == 'all' ? 'publisher_id':
publisher})
and ab.author_id IN (#{author == 'all' ? 'author_id': author})
END
find_by_sql(q)
end
end
Now both arguments (author and publisher) will accept a string of the
form '1, 3, 5....' (and can contain little or many numbers as you
want) to find the matching rows and also take the string 'all', which
will effectively disregard that argument.
I wonder if it would be worth making this more ActiveRecordy by using
Book.find_all with :conditions, and :join. The nice thing about that
would be you could construct proper where clauses instead of using the
author_id = author_id trick. I'd actually proposed the latter as a
solution to another problem a while ago, but someone posted the ARy
solution and it might have advantages.
I wonder if it would be worth making this more ActiveRecordy by using
Book.find_all with :conditions, and :join. The nice thing about that
would be you could construct proper where clauses instead of using the
author_id = author_id trick. I'd actually proposed the latter as a
solution to another problem a while ago, but someone posted the ARy
solution and it might have advantages.
///ark
How would you re-write it? I'm not aware of alternatives to the
author_id = author_id trick.
You just construct your :conditions clause programmatically. If 'all'
is desired, then you don't even have a condition for that part.
Another nice thing about using :conditions gives you some niceties,
such as generating IN (...) when the condition contains an array.
