Topic: Best way to query database (how do I avoid multiple queries?)

Hello,

I've looked around the forum for a while and can't find an instance of this covered...

I'm working on a project for my school newspaper (The DoG Street Journal @ the COllege of William and Mary).

Part of the admin section has a list of all the articles currently on the section front pages. So there is 4 sections of the page--Sports, News, Opinion, and Style. Each one of these sectiosn has 6-7 articles listed under it.

All the articles are stored within the same table. Currently, I am querying the DB 4 times to render the page--once for each section (WHERE section = '$sectionid') ... I'd really like to reduce the processing time by only querying the DB once and then seperating the articles into their respective sections after the fact...

What is the best way to do this? Or is there a way to speed up my DB query. Currently I'm using variations on this:

@stories = Story.find(:all, :conditions => ["section_id = :section_id and status = '6')

status is simply a variable that tells what stage the article is in (draft, published, archived, etc).

Thanks in advance for you help!

Re: Best way to query database (how do I avoid multiple queries?)

I'm assuming Section has_many :stories. If so, you can query the sections and include the stories in the query. Like this:

@sections = Section.find(:all, :include => :stories)

And then in your controller, loop through each section and display the stories:

<% for section in @sections %>
  <h2><%=h section.name %></h2>
  <ul>
  <% for story in section.stories %>
    <li><%=h story.name %></li>
  <% end %>
  </ul>
<% end %>

This way it will only use one query. Hope that helps.

Railscasts - Free Ruby on Rails Screencasts

Re: Best way to query database (how do I avoid multiple queries?)

Well, it helped me solve a similar problem.

Thanks Ryan.

Re: Best way to query database (how do I avoid multiple queries?)

Of Couse! I feel silly that it was so simple:) Thanks a million!

Ryan