Rails Query within Query

rubyonrails-talk
1

Hello,

I am looking for a better way to do the following (my code examples are crappy pseudocode):

I have two associated models, Book and Chapter, where book has_many chapters. I want to first select several books, for example,

books_by_dr_seuss = Book.find(:all, :conditions => {:author => "Seuss" })

Then, I want to select all the "Chapter 3"'s from this result, i.e. Chapter 3 of Cat in the Hat, Chapter 3 of Fox in Socks, etc.

I want to do something like:

chapter_threes = Chapter.find(:all, :include=>[:book], :conditions => {chapter.book in books_by_dr_seuss } )

How would I do this without resorting to something like "select * from chapters where chapter.book_id=1 or chapter.book_id=2, etc"?

Thanks for your help. A

2

Wouldn't it be something like the following:

suess_chapter_three = Chapter.find(:all, :include=>:book, :conditions => ['books.auther like "suess" and chapters.chapter = 3'])

Simon

3

Simon Macneall wrote:

Wouldn't it be something like the following:

suess_chapter_three = Chapter.find(:all, :include=>:book, :conditions => ['books.auther like "suess" and chapters.chapter = 3'])

Simon

Thanks for the response Simon -- I think my example didn't fully capture what I am trying to do :slight_smile:

I want to select from a collection or list of items. I can't use 'books.author like' because there is no criteria that ties them all together.

A better example would be: User is presented with a list of checkboxes (let's say, book names) in a form. He checks several of these checkboxes. The form gets submitted and returns all of the chapters 3 from the selected books.

For example say the user selects:

Fox in Sock by Dr Seuss War and Peace by Leo Tolstoy The Godfather by Mario Puzo

The form passes the ids of these selections (let's say, [1000, 1001, 1002]) to the controller, which then calls a query on these selected plates:

selected_books = params[:selected_books] Chapter.find(:all, :include=>:book, :conditions => 'chapter.book_id = book.id and book.id in books and chapters.chapter=3' )

Is there a way to do that statement? "in books" is obviously not a valid command...

Thanks! A

4

I think something like the following:

book_ids = ids of selected books (as gleened from the controller) chapter_threes = Chapter.find(:all, :include => :book, :conditions => ['book.id in ? and chapter.chapter_number = 3', book_ids])

obviously not tested, but once you fix any syntax errors it should work.

Simon

5

Thank you for the reply Simon. What you said worked for me, although I did it in a rather inelegant way -- I'm not sure about the correct syntax of my solution, but it works. It requires creating a string with the ids in parens...

book_ids = "(100, 101, 102)" chapters= Chapter.find(:all, :include => :books, :conditions => ["books.id in #{book_ids} and chapter='2'"]

first off that can be done nicer: book_ids = [1,2,3]

chapters= Chapter.find(:all, :include => :books, :conditions => ["books.id in (?) and chapter='2'", book_ids]

or even

chapters= Chapter.find(:all, :include => :books, :conditions => { 'books.id' => book_ids, 'chapter' => 2})

You don't even need to get an array of book_ids - an array of activerecord objects will do fine too.

And last of all:

Chapter.find :all, :joins => :books, :conditions => {'books.author' => 'Seuss', 'chapter' => 2}

Fred

6

Hi Fred,

Thanks for your response -- Your second example is exactly what I needed.

The final example, though -- why do you use :joins instead of :include?