In Rails, how can I return a set of records based on a count of items in a relation OR criteria about the relation?

Question

I'm writing a Rails app in which I have two models: a Machine model and a MachineUpdate model. The Machine model has many MachineUpdates. The MachineUpdate has a date/time field. I'm trying to retrieve all Machine records that have the following criteria:

1. The Machine model has not had a MachineUpdate within the last 2 weeks, OR
2. The Machine model has never had any updates.

Currently, I'm accomplishing #1 with a named scope:

named_scope :needs_updates,
            :include => :machine_updates,
            :conditions => ['machine_updates.date < ?', UPDATE_THRESHOLD.days.ago]

However, this only gets Machine models that have had at least one update. I also wanted to retrieve Machine models that have had no updates. How can I modify needs_updates so the items it returns fulfills that criteria as well?

Answer 1

One solution is to introduce a counter_cache:

# add a machine_updates_count integer database column (with default 0)
# and add this to your Machine model:
counter_cache :machine_updates_count

and then add OR machine_updates_count = 0 to your SQL conditions.

However, you can also solve the problem without a counter cache by using a LEFT JOIN:

named_scope :needs_updates,
  :select => "machines.*, MAX(machine_updates.date) as last_update",
  :joins  => "LEFT JOIN machine_updates ON machine_updates.machine_id = machines.id",
  :group  => "machines.id",
  :having => ["last_update IS NULL OR last_update < ?", lambda{ UPDATE_THRESHOLD.seconds.ago }]

The left join is necessary so that you are sure you are looking at the most recent MachineUpdate (the one with MAX date).

Note also that you have to put your condition in a lambda so it is evaluated every time the query is run. Otherwise it will be evaluated only once (when your model is loaded on application boot-up), and you will not be able to find Machines that have come to need updates since your app started.

UPDATE:

This solution works in MySQL and SQLite, but not PostgreSQL. Postgres does not allow naming of columns in the SELECT clause that are not used in the GROUP BY clause (see discussion). I'm very unfamiliar with PostgreSQL, but I did get this to work as expected:

named_scope :needs_updates, lambda{
  cols = Machine.column_names.collect{ |c| "\"machines\".\"#{c}\"" }.join(",")
  {
    :select => cols,
    :group  => cols,
    :joins  => 'LEFT JOIN "machine_updates" ON "machine_updates"."machine_id" = "machines"."id"',
    :having => ['MAX("machine_updates"."date") IS NULL OR MAX("machine_updates"."date") < ?', UPDATE_THRESHOLD.days.ago]
  }
}

Answer 2

If you can make changes in the table, then you can use the :touch method of the belongs_to association.

For instance, add a datetime column to Machine named last_machine_update. Thereafter in the belongs_to of MachineUpdate, add :touch => :last_machine_update. This will cause that field to become updated with the last time you either added or modified a MachineUpdate connected to that Machine, thus removing the need for the named scope.

Otherwise I would probably do it like Alex proposes.

Answer 3

I just ran into a similar problem. It's actually pretty simple:

Machine.all(
  :include => :machine_updates,
  :conditions => "machine_updates.machine_id IS NULL OR machine_update.date < ?", UPDATE_THRESHOLD.days.ago])

If you were doing a named scope, just use lambdas to ensure that the date is re-calculated every time the named scope is called

named_scope :needs_updates, lambda { {
  :include => :machine_updates,
  :conditions => "machine_updates.machine_id IS NULL OR machine_update.date < ?", UPDATE_THRESHOLD.days.ago]
} }

If you want to avoid returning all of the MachineUpdate records in your query, then you need to use the :select option to only return the columns you want

named_scope :needs_updates, lambda { {
  :select => "machines.*",
  :conditions => "machine_updates.machine_id IS NULL OR machine_update.date < ?", UPDATE_THRESHOLD.days.ago]
} }