Пример сложного запроса с подзапросом, выбираем номера книжек раздела, после чего выбираем авторов, книжки у которых иданы и принадлежат типу и номер входит в номера книжек раздела

classic_hard.rb

def self.for_classic(compilation = nil)
    if compilation.nil?
      classic_ids_query =   sprintf('SELECT "books".id FROM "books" 
                          INNER JOIN "books_products" ON "books_products".book_id = "books".id 
                          INNER JOIN "products" ON "products".id = "books_products".product_id 
                          WHERE (products.id = %d)
                          AND ("books"."status" IN (%s, %s))', Product.classic_product.id, "E'published'", "E'republishing'")
    else
      classic_ids_query =  sprintf('SELECT "books".id FROM "books" 
                          INNER JOIN "books_compilations" ON "books".id = "books_compilations".book_id 
                          WHERE ("books_compilations".compilation_id = %d ) 
                          AND ("books"."status" IN (%s, %s))', compilation, "E'published'", "E'republishing'")
    end
    Author.all( :joins => ['left outer join authors_books on authors_books.author_id = authors.id'], 
                :select => ['count(authors_books.book_id) as author_books, authors.full_name, authors.id'], 
                :group => 'authors.id, authors.full_name', 
                :order => 'authors.full_name DESC',
                :conditions => "authors_books.book_id in (#{classic_ids_query})")
  end