Forum: Ruby on Rails Rails Query within Query

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Avi I. (Guest)
on 2009-03-13 02:24
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
Simon M. (Guest)
on 2009-03-13 02:32
(Received via mailing list)
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
Avi I. (Guest)
on 2009-03-13 02:46
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 :)

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
Simon M. (Guest)
on 2009-03-13 02:54
(Received via mailing list)
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
Avi I. (Guest)
on 2009-03-18 19:02
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!
Frederick C. (Guest)
on 2009-03-18 19:37
(Received via mailing list)
On Mar 18, 5:02 pm, Avi Ir <removed_email_address@domain.invalid> 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
Avi I. (Guest)
on 2009-03-18 19:59
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?
This topic is locked and can not be replied to.