Topic: "Filtering" ActiveRecord objects?

Lets say I do a find_all on a table and I get an object called @books that contains all the results from the query.

Are there any methods that I can use to filter the results from @books as I see fit? I need to search for 20+ different things from the Books table and I don't think I am supposed to be doing a DB query for each of those things am I? Or should I really not be concerned?

Re: "Filtering" ActiveRecord objects?

Incidentally I'm currently creating an online book library (17books.com) so I decided to investigate this question a little deeper.

To this end I wrote the following script:

#!/usr/bin/env ruby
require File.dirname(__FILE__) + '/../config/environment'
require 'profiler'

# How long to find using database queries?
before = Time.now
results = Book.find_by_title("The Art of War")
after = Time.now
puts "Time to search by database query: #{after-before}"

# How long to find manually?
before = Time.now
all_books = Book.find_all
results = []
for book in all_books
  if book.title == "The Art of War"
    results << book
  end
end
after = Time.now
puts "Time to search manually: #{after-before}"


and placed it in script/test.rb.

These are the results:

$ ruby script/test.rb 
Books in database: 7
Time to search by database query: 0.008826
Time to search manually: 0.004264

So the manual search clearly beat the database query, but then again there are only 7 books in my (development) database.

Please feel free to use this script for your application and let us know your results!

Last edited by philipp13 (2007-02-03 08:07:31)

Re: "Filtering" ActiveRecord objects?

Wow.. that is actually very simple. I guess I was overthinking it too much wink From what I know, asking the DB is usually one of the slowest parts of a web app, I should have known there was an easier way.

Thanks a lot for the response.

Re: "Filtering" ActiveRecord objects?

I would attribute the performance hit in Philipp13's example to database overhead (it's meant to handle a lot more than 7 records). When you have a lot more than 7 records, your app will require a lot of memory just to receive the unfiltered query results if you choose that route. If the database server is on a different machine, this will mean a lot of network traffic.

You don't want to hit the database gratuitously, but when you do hit it I think you want to minimize the amount of data exchanged. And you don't want to write a new query engine in each piece of your code. If necessary it's better to take advantage of your database' performance optimizations (like indexes).

Re: "Filtering" ActiveRecord objects?

dkov is right on. Best to do the filtering through the database query - you will get the best performance this way when it matters (when there are a lot of books). You may also want to add pagination which definitely should be done at the database level so you don't fetch thousands of records when you only need to display 20.

Railscasts - Free Ruby on Rails Screencasts

Re: "Filtering" ActiveRecord objects?

Okay, that makes sense. If that is the case, is there a way to easily re-query the DB then? Lets say I have a calendar and I want to find out what is going on each day of the month, and each day can have as N events. There are 30-31 days in the month, so it sounds like I would be making 30 queries to the DB for each view. I have a controller action:

  def day_events(day_date)
    day_search = day_date.strftime("%Y-%m-%d")
    # ask the database for events from this month
    @day_events = Event.find_day_events(day_search) 
  end

which calls the model action:

class Event < ActiveRecord::Base
   def self.find_day_events(day_search)
    find(:all,
         :conditions => [ "date = :date",
                        { :date => day_search } ],
         :order      => "start_time")
  end
end

The "find" in the model is basically the same for each day, but the only thing that changes is the value in the :date hash. Would I access the "day_events" action from the View each time I need it? If so, is there a special way that I should be accessing the controller action from the view? I tried it and it didn't seem to like it:

undefined method `day_events' for...

Thanks for helping.

Re: "Filtering" ActiveRecord objects?

Controller methods cannot be accessed from the view. Instead it is best to put the method in the model or helper.

For best performance, try to keep the number of database queries to a minimum. In this case you have a calendar view where you want to display all events for a given month? The first step is to grab everything you want to display from the database. Best to do this in the controller:

# in controller
def list
  @events = Event.find(:all, :conditions => ['MONTH(date)=? AND YEAR(date)=?', 1, 2007])
end

This will find all events for January 2007 (which of course you can replace with variables). The next step is to group the events by day since you want to display the days separately, correct? You can do this in the view:

<% 1.upto(Time.days_in_month(1, 2007)) do |day| %>
  <% for event in @events.select { |event| event.date.day == day } %>
    <%= event.name %>
  <% end %>
<% end %>

Untested.

To sum up, filter the results using a database query, but group the results using Ruby.

Railscasts - Free Ruby on Rails Screencasts