Rails Query within Query

Rails
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

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

4

Simon M. 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

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’”]

Any suggestions for clearer code welcome, but thank you for your help!

6

On Mar 18, 5:02 pm, Avi Ir [email protected] wrote:

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

7

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?