Hello
I have a table called Books And a table called Authors
Books belong_to authors
How can I find only authors that have books?
Thanks
Fernando Brito wrote:
Hello
I have a table called Books And a table called Authors
Books belong_to authors
How can I find only authors that have books?
Thanks
I think an author can write many books so that you can put has_many :books in Author ActiveRecord
so that you can find books easily
@author = Author.find(:first)
@books = @author.books
Reinhart http://teapoci.blogspot.com
I thought I’d point out too that one book can have multiple authors, so you might need something like
Book id name
Writings book_id author_id
Author id name
and use something like
Books has_many :authors, :through => :writings Writings belongs_to :book, belongs_to :author Author has_many :books, :through => :writings
To get that many to many relationship working.
As for your question, and using the above, now all you need to do is construct a find query to get authors if COUNT(*) FROM writings WHERE author_id = [id here] and check its size is > 0
That would be my solution anyway. A more exp. rails dev might have a faster option.
Regards Kieran
This is not optimal code, but you can make a method in Author:
def self.find_all_with_books authors = for author in self.find(:all) authors << author if author.books.count > 0 end authors end
If you're using Rails 2.1 you can check out named_scope. Not familiar with it yet, but it goes something like this
class Author < .... has_many :books named_scope :published, :include => :books, :conditions => ["books.count > 0"]
I'm pretty sure the code won't work. Watch this to be more familiar with it though #108 named_scope - RailsCasts
I like that, though what about finding the db records with sql and a standard table join?
I suppose it'd be the same in terms of cpu cycles, etc.
No it wouldn’t, doing it in SQL would result in one query and less cycles.
Something like:
Author.find(:all, :joins => :books)
Should do the trick, this should do a query with an inner equijoin on Authors and Books, so Authors without books would not be returned.
Note that I haven’t actually tested this code.
There's always the cheat's version: have a counter_cache on books.
Fred
Hey everyone! Well what do you know? I had to do this today, right after I emailed.
If you're going to use named_scope, you have to setup a counter cache.
then, in Author:
named_scope :published, :conditions => ["books_count > 0"]
if not named_scope, try:
Author.find(:all, :conditions => ["books_count > 0"])
Either way, counter_cache would be a good thing to setup since doing these kinds of finds will be a lot less CPU intensive.
