Rails ActiveRecord group results into sub-collections by date

Question

I'm trying to do an ActiveRecord query in Rails 3.1 where I sort the results into sub-collections of grouped items, in this case grouped by date.

I think my code can explain it best. This is my method, which works but issues 4 queries to get the job done. It doesn't seem very efficient to do it this way.

def entry_days
  days = @user.entry_groups.find(
    :all,
    :select => 'date',
    :limit => 3,
    :group => 'date').map(&:date)

  entry_days = days.map do |date|
    { :date =>  date,
      :entry_groups => @user.entry_groups.find_all_by_date(date)
    }
  end      
end

Using the suggestion from Dave Newton below to use group_by, I have re-written the method like this:

def entry_days
  dates_with_entries = @user.entry_groups.find(
    :all,
    :select => 'date',
    :limit => 3,
    :group => 'date').map(&:date)

  @user.entry_groups.where(:date => dates_with_entries).all.group_by(&:date).
    map do |date, entry_groups|
      { :date => date,
        :entry_groups => entry_groups }
    end
end

At least I have it down to only 2 queries now.

Then I re-wrote the method again like this:

  dates_with_entries = user.entry_groups.all(
      :select => 'date',
      :limit => num_days,
      :order => 'date DESC',
      :group => 'date').map(&:date)

  entry_groups = user.entry_groups.
      where(
        :date => dates_with_entries
      ).
      all(:order => 'date DESC')

  entry_days = entry_days.group_by(&:date).
      map { |date, entry_groups|
        {
          :date => date,
          :entry_groups => entry_groups
        }
      }

On a side note: Should I not be chaining so many methods together, and what is the preferred indentation format for nested methods and hashes?

This question: http://stackoverflow.com/questions/6953512/rails-3-1-with-postgresql-group-by-must-be-used-in-an-aggregate-function/6953704#6953704, discusses something similar — rubish, Sep 05 '11 at 10:42

score 8 · Accepted Answer · answered Sep 05 '11 at 02:46

8

Why not select them all then use something like group_by?

answered Sep 05 '11 at 02:46

Dave Newton

158,873
26
254
302

2

Eventually this collection will be backed by thousands of rows and I only want the most recent 3 days worth of entries, and those days should have at least 1 entry each. If I'm not mistaken, your suggestion is to pull down all the rows for the query. I could do that but I want to keep the behavior I described. – Chris M. Welsh Sep 05 '11 at 03:35
I suppose I could do the mapping of days to entries using the group_by method, after I do my 2nd select. That would bring the query total down to 2. – Chris M. Welsh Sep 05 '11 at 03:40
If there will always be at least one entry per day, seems like you could just select based on a date range and group once retrieved. – Dave Newton Sep 05 '11 at 06:22
1

I want at most 3 different dates sorted descending. It's possible to have gaps in the range, so I want entries from today, yesterday and 2 weeks ago if those are the 3 most recent days with entries. – Chris M. Welsh Sep 05 '11 at 07:29
Ah; I misunderstood "[...] and those days should have at least 1 entry each"--my bad. I'd probably do this with plain ol' SQL nested select. – Dave Newton Sep 05 '11 at 15:44

Rails ActiveRecord group results into sub-collections by date

1 Answers1

Linked