Ruby and MySQL Db query group limit

I'm trying to return for a given user (current_user) activities created through the week (last 7 days) and group by day with sum of time spent on each.

current_user has_many projects currents_user has_many activities through :projects

For each of the last 7 days I want to sum up time spent on activities (each activity has time field to store time spent) where project is internal (project.internal is boolean)

In other words here is what I need. Todays is Wednesday.

Today:

• Total activities time for internal project
• Total activities time for not internal project

Yesterday ...

2.days.ago ... and so on for the last 7 days

What goes in the model, controller and views?

Ideally it would need to work with MySQL开发者_运维百科 and SQLite. Thank you in advance.

Methods like this are best defined in your models and exercised in your views. The controller only really serves to set things up so that the view can do its work.

The advantage to defining these in the model is you can write unit tests for them which are usually easier to set up than functional or integration tests. Your methods are also easy to test from the console command line.

For a lot of things you can simply exercise the sum method on your relationships. It may look something like this:

user.activities.sum(:time_spent)

You can apply scopes to the activities relationship to better target the result.

Update

To group together by different parameters, you can always express this as a SQL join and extract the computed values. The query would look something like this:

SELECT users.id, projects.internal, SUM(activities.time) AS total_time, activities.on_date
  FROM users
  LEFT JOIN projects ON projects.user_id=users.id
  LEFT JOIN activities ON activities.project_id=projects.id
  GROUP BY users.id, projects.internal, activities.on_date

Once you've got that set up properly, adjusting the column names as required, you can wrap it in a call that will make it easy to extract values:

class User < ActiveRecord::Base
  def self.time_spent_on_projects(user_id = nil)
    query = "..." # Query from above

    # Allow scoping to a particular user by adding a WHERE clause
    query = self.sanitize_sql([ "#{query} WHERE users.id=?", user_id ])

    self.connection.select_all(query)
  end

  def time_spent_on_projects
    self.class.time_spent_on_projects(self.id)
  end
end

This will return all values as String or nil, so you may need to convert to numbers as required.