Creating DB views in Ruby on Rails

Question

Hey guys,

I'm adding reporting functionality to a project.

3 roles here:

1) Volunteers (they report what hours they volunteered)

2) Supervisors (they look at the reported stuff, note: one supervisor can view all projects)

3) Projects (Represents a work project that some collection of volunteers work on)

To explain what it does:

A report will be specified by the supervisor to generate based on a query of what he needs.

These could be plausible reports:

1) The total number of volunteers, and the total amount of volunteer hours on this project

2) All the volunteer's names and emails associated with a project

3) The number of active projects vs. the total number of projects

I was thinking maybe that creating a database view in Rails and storing the name of that view so Rails will just check the view whenever a supervisor wants to pull up the report.

Is a view really the answer or is it better to just save a query?

Can Rails do this or is there an even better or more simple way of achieving this functionality?

Edit: A report is essentially a sql query in the back. We want supervisors to be able to create their own reports as well. We intend to build some front end UI to make it easier.

I just thought that having a table storing the views would be more descript (ReportID, ViewName) than a table storing the SQL query (ReportID, SQLQuery). Also I was thinking of saving space very specialized SQL queries get very large and confusing. I apologize for being confusing since I am really new to Ruby and Rails

For example: I was thinking what could be powering the #2 report would be this query (or equivalent code for view: select v.volunteerName, v.volunteerEmail from volunteers v,volunteering vg where v.volunteerId = vg.volunteerId AND vg.projectId = (Some Arguement passed by user)

Note that the volunteering database is a relationship database as a volunteer can volunteer for more than one project.

Cheers, -Jeremiah Tantongco

Answer 1

Hi Jeremiah,

Your question is a bit confusing confusing, but I think you need to:

1. Create models for users, projects, and activities (hours)
2. Create basic time entry form for volunteers
3. Create a new controller for reports (call it 'reports_controller')
4. Create views (and actions) for each report you will have (example: 'projects.html.erb')
5. Create an index action within the report and provide links for each report
6. Add authentication (try using Authlogic)
7. Add authorization (try using CanCan)

Kevin