# Triggering a query within a loop

**URL:** <https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298>\
**Category:** Rails\
**Created:** [December 14, 2006, 12:25am UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298 "2006-12-14T00:25:53Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 14, 2006, 12:25am UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/1 "2006-12-14T00:25:53Z")

</div>

Any help with this would be very appreciated, I’m completely new to  
rails. I’m building an application that’s looking to do the following:

First off i’m trying to convert my site from ColdFusion to RoR, this is  
the site: [http://www.walkenclips.com/](http://www.walkenclips.com/)

It’s one page that basically does this:

Query the db to get a list of movies (movies table)  
Loop through the movies and output each movie to the browser  
Query the db within the loop to get files names of movie quotes from  
that specific movie (movie\_files table)  
Loop through the movie quote names and output each quote to the browser  
Continue to the next movie, rinse and repeat till the end of the loop

I’m able to display the the movies fine with the following code:

Controller:  
class HomeController \< ApplicationController  
def index  
@movie = Movie.find(:all, :order =\> “title”)  
end  
end

Index.rhtml  
\<% for movies in @movie -%\>

 \<%= movies.title %\> 
\<% end %\>

movie.rb  
class Movie \< ActiveRecord::Base  
end

While I’m looping through the results in the index.rhtml, how can I tell  
the controller I need the movie file names for that specific movie  
within the “movie\_files” table?

---

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 14, 2006, 9:59am UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/2 "2006-12-14T09:59:13Z")

</div>

in the controller  
@movie = Movie.find(:all, :order =\> “title”)  
movie\_filed = @movies.movie\_files

Off course, you need to define the model first:  
class MovieFile \< ActiveRecord::Base  
belongs\_to :movie  
end

class Movie \< ActiveRecord::Base  
has\_many :movie\_files  
end

---

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 14, 2006, 2:39pm UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/3 "2006-12-14T14:39:41Z")

</div>

Hi Bill

Bill wrote:

> How can I tell the controller I need the movie  
> file names for that specific movie within the  
> “movie\_files” table?

In general, you’re going to do a find on the movie\_files table for each  
movie. What the find looks like depends on what your movie\_files table  
schema looks like.

The other thing you’ll need to address is the data structure to pass to  
your  
view. My personal preference would probably be to construct a hash of  
hashes in the controller, but there are other ways. Share some more  
info  
and we can probably be of more help.

Best regards,  
Bill

---

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 14, 2006, 5:10pm UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/4 "2006-12-14T17:10:04Z")

</div>

Bill W. wrote:

> Hi Bill
> 
> Bill wrote:
> 
> > How can I tell the controller I need the movie  
> > file names for that specific movie within the  
> > “movie\_files” table?
> 
> In general, you’re going to do a find on the movie\_files table for each  
> movie. What the find looks like depends on what your movie\_files table  
> schema looks like.
> 
> The other thing you’ll need to address is the data structure to pass to  
> your  
> view. My personal preference would probably be to construct a hash of  
> hashes in the controller, but there are other ways. Share some more  
> info  
> and we can probably be of more help.
> 
> Best regards,  
> Bill

Thanks for the replies guys, here is my table schema:

# movies table field - type - extra

id - int - auto increment (priamry key)  
title - varchar  
picture - varchar  
year - varchar  
rating - varchar  
played - varchar  
director - varchar

# movie\_files table field - type - extra

id - int - auto increment  
movie\_id - int  
name - varchar  
downloaded - int  
played - int

---

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 14, 2006, 5:40pm UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/5 "2006-12-14T17:40:17Z")

</div>

Hi Frank,

Is movie\_id declared as a foreign key? It should be. Once it is, you  
can  
do something like…

movies = Movie.find(:all)  
movies.each {|this\_movie|  
clips = MovieFile.find\_all\_by\_movie\_id(this\_movie.id)  
}

As I noted before, however, you’re going to need to decide how you want  
to  
structure this data for use in your views. IMHO, there are three  
obvious  
approaches. 1) You could construct a hash of hashes. 2) You could name  
each of the clips arrays so they’re ‘associated’ with some field in your  
movies records. Either of these would work with your existing model.  
The  
hashes approach is cleaner (again IMHO) and there are, of course, other  
approaches. The third option is the easiest. 3) De-normalize your  
data.

Lest you want to invite the displeasure of the RoRing Gods, the one  
thing  
you don’t want to do (although it’s certainly possible) is to do the  
clips  
find in the view.

hth,  
Bill

---

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 14, 2006, 6:05pm UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/6 "2006-12-14T18:05:16Z")

</div>

Thanks for such a quick response Bill. Since I’m such a newbie to RoR  
I’m still a bit confused about method 1 you mentioned. How do I setup  
the hashes and have them interact with the view?

---

<div class="post-metadata">

**Author:** ![bill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/ac91a4/32.png) [@bill](https://www.ruby-forum.com/u/bill)\
**Post date:** [December 15, 2006, 5:11pm UTC](https://www.ruby-forum.com/t/triggering-a-query-within-a-loop/79298/7 "2006-12-15T17:11:20Z")

</div>

Hi Frank,

Sorry for my delay in responding. I was out of the office all afternoon  
yesterday. Will be again today so please don’t take offense if you have  
a  
follow-on that I don’t respond to til tomorrow.

I took another look at the models you posted and think perhaps I’ve  
misunderstood your needs. My initial understanding was that you had  
multiple quotes, _and_ a clip associated with each one, that you wanted  
to  
output for each movie. In rereading your description, it seems that you  
only want to output the quotes. If that’s true, then you don’t need a  
hash  
of hashes. A hash of arrays will do fine.

If my (new) understanding is correct, in an action in your controller…

@movie\_quotes = Hash.new  
@movies = Movie.find(:all)  
@movies.each {|this\_movie|  
@movie\_quotes[this\_movie] =  
MovieFile.find\_all\_by\_movie\_id(this\_movie.id)  
}

In your view…

\<% @movie\_quotes.each do |movie\_name, quotes| %\>  
\<%=h( movie\_name )%\>  
\<% quotes.each do |this\_quote| %\>  
\<%=h( this\_quote )%\>  
\<% end %\>  
\<% end %\>

hth,  
Bill