Ruby Rails Complex SQL with aggregate function and DayOfWeek

Question

Rails 2.3.4

I have searched google, and have not found an answer to my dilemma.

For this discussion, I have two models. Users and Entries. Users can have many Entries (one for each day).

Entries have values and sent_at dates.

I want to query and display the average value of entries for a user BY DAY OF WEEK. So if a user has entered values for, say, the past 3 weeks, I want to show the average value for Sundays, Mondays, etc. In MySQL, it is simple:

SELECT DAYOFWEEK(sent_at) as day, AVG(value) as average FROM entries WHERE user_id = ? GROUP BY 1

That query will return between 0 and 7 records, depending upon how many days a user has had at least one entry.

I've looked at find_by_sql, but while I am searching Entry, I don't want to return an Entry object; instead, I need an array of up to 7 days and averages...

Also, I am concerned a bit about the performance of this, as we would like to load this to the user model when a user logs in, so that it can be displayed on their dashboard. Any advice/pointers are welcome. I am relatively new to Rails.

Answer 1

You can query the database directly, no need to use an actual ActiveRecord object. For example:

ActiveRecord::Base.connection.execute "SELECT DAYOFWEEK(sent_at) as day, AVG(value) as average FROM entries WHERE user_id = #{user.id} GROUP BY DAYOFWEEK(sent_at);"

This will give you a MySql::Result or MySql2::Result that you can then use each or all on this enumerable, to view your results.

As for caching, I would recommend using memcached, but any other rails caching strategy will work as well. The nice benefit of memcached is that you can have your cache expire after a certain amount of time. For example:

result = Rails.cache.fetch('user/#{user.id}/averages', :expires_in => 1.day) do
  # Your sql query and results go here
end

This would put your results into memcached for one day under the key 'user//averages'. For example if you were user with id 10 your averages would be in memcached under 'user/10/average' and the next time you went to perform this query (within the same day) the cached version would be used instead of actually hitting the database.

Answer 2

Untested, but something like this should work:

@user.entries.select('DAYOFWEEK(sent_at) as day, AVG(value) as average').group('1').all

NOTE: When you use select to specify columns explicitly, the returned objects are read only. Rails can't reliably determine what columns can and can't be modified. In this case, you probably wouldn't try to modify the selected columns, but you can'd modify your sent_at or value columns through the resulting objects either.

Check out the ActiveRecord Querying Guide for a breakdown of what's going on here in a fairly newb-friendly format. Oh, and if that query doesn't work, please post back so others that may stumble across this can see that (and I can possibly update).

---

Since that won't work due to entries returning an array, we can try using join instead:

User.where(:user_id => params[:id]).joins(:entries).select('...').group('1').all

Again, I don't know if this will work. Usually you can specify where after joins, but I haven't seen select combined in there. A tricky bit here is that the select is probably going to eliminate returning any data about the user at all. It might make more sense just to eschew find_by_* methods in favor of writing a method in the Entry model that just calls your query with select_all (docs) and skips the association mapping.