Reporting number of hours volunteered

[Tony Olivo]

Hello again all. Time for my Sunday end of day e-mail.

So, what I've been working on today is generating a report that shows all the volunteers that have volunteered >=x number of hours over a date range. This relies on the start_at and end_at attributes of the visits model in my fork. I'm having problems calculating information from the database and basing finders off of it.

My original plan was to have a method in the Person model like so:

  def volunteer_hours(from=Date.today - 365,to=Date.tomorrow)
    hours = 0
    self.visits.after(from).before(to).each do |visit|
      if visit.end_at and visit.start_at and visit.volunteer
        hours += visit.end_at - visit.start_at
      end
    end
    hours/3600
  end

That would allow you to take a Person and find out how many hours that particular person has volunteered over a date range. When I got around to trying to make a named_scope finder in the Person model for that, things fell apart. I think it would be better to have the volunteer_hours work on visits instead, so it can benefit from the before and after finders it already has.

So I think I want to be able to run something like this

people.visits.before('2010-08-01').after('2010-06-01').volunteer_hours(10)

Where volunteer_hours I guess is another finder that acts only on the visits parsed out by before and after but still gives me back the people instead :\

I think I got in over my head on this one (again?). So what do you think, jumping from person to visit back to person, can it work?

Thanks,
-Tony

[Alon Salant]

Hmm.... Lots of ways to skin this cat. Let me just throw out some
ideas that come to mind and we can see where that goes.

One of the basic questions is how much work to push to the database
and how much to do in code. The more you do in code, the greater the
chance of reuse and the easier it may be to understand, but the slower
it will be when there is a lot of data, either because the date range
is really big or there are a lot of people and visits (or both). The
more you do in the database, the more complex SQL you have to write,
maintain and understand and the more specific it is likely to be for
the report you are creating.

I'm okay with simpler, slower solutions esp for reporting since they
are not run often. They can always be optimized later which is easier
when the slow implementation is well tested and easy to understand.

Would it make things any easier if instead of 'end_at' you were
storing 'duration' (from which you could deduce end_at, or you could
store both)? Then you could have a sql query that would go something
like (in pseudo code) find all people where the sum of visit durations
with start_at between dates x and y is greater than z. ActiveRecord
isn't going to do too much for you in creating the where clause for
this query. You'll probably just have to write that SQL.

If you had duration saved with each visit, you could also use that in
Person.volunteer_hours either iterating through the visits for the
date range or with a similar query to above.

You don't have to use named_scope or work with the ActiveRecord query
API. Sometimes straight SQL is just simpler. Though your desire to
reuse the existing scopes makes sense too.

Helpful at all?

> --
> You received this message because you are subscribed to the Google Groups
> "Freehub Users" group.
> To post to this group, send email to freehu...@googlegroups.com.
> To unsubscribe from this group, send email to
> freehub-user...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/freehub-users?hl=en.
>

[Michael Kirk]

Hi, 

I'm Michael, I'm new to the list, coming from the Bicycle Kitchen (Los Angeles), and I wanted to introduce myself.

Also, I think Alon's suggestion to record the duration is a simple and efficient one.

M

[Tony Olivo]

Yeah, the duration attribute on the Visit does seem to be the most straight forward way. For a while I was thinking the volunteered hours would be something I stored on the person, but that doesn't work out well for arbitrary date ranges. I'll be out of town for the next week or so. I'll let you all know if I come up with something when I get back.

Here I was thinking I was going to get away without writing any SQL :]

Welcome to the list, Michael!

-Tony

[Michael Kirk]

On Tue, Aug 3, 2010 at 9:52 AM, Tony Olivo <tony....@gmail.com> wrote:

Yeah, the duration attribute on the Visit does seem to be the most straight forward way. For a while I was thinking the volunteered hours would be something I stored on the person, but that doesn't work out well for arbitrary date ranges. I'll be out of town for the next week or so. I'll let you all know if I come up with something when I get back.

Here I was thinking I was going to get away without writing any SQL :]

Unless I'm missing something, I think you could do it from behind the ActiveRecord abstraction if you wanted.

1. Add the duration column in a migration.

2. Hook into the "visits" ActiveRecord lifecycle (maybe at before_validation[1]), calculate and set the duration attribute, then ActiveRecord should take over from there.

3. Calculating a persons time would be something like

def time_spent(start, finish)

  total=0

  # filter by volunteer status within the ORM for better performance

  person.visits.find(:all, :conditions => {:volunteer => true }).before(finish).after(start).each {|visit| total += visit.duration}

end

Is there something else worth explicitly doing in the DB or am I missing something?

yours,

M

[1] http://api.rubyonrails.org/classes/ActiveRecord/Callbacks.html#M001378

[Tony Olivo]

Hey Michael,

I didn't know about the ActiveRecord callbacks, that looks interesting and makes it easy to put the duration in without having to change a lot of other code. I did some additional reading here to get an idea of where to go http://guides.rubyonrails.org/activerecord_validations_callbacks.html#callbacks-overview

I follow how you are calculating the person's time. It's similar to the original way I did it, but makes use of duration and has the performance boost as you note. It's also a bit cleaner ;]

Will be working on it for another few hours today... irc.freenode.net/#dbc

-Tony

[Tony Olivo]

Didn't have great luck with this last week. Running into a lot of trouble with find and other functions returning arrays instead of ActiveRecord collections, so I can't run visits.find(conditions...).before('2010-01-01'), because I can't chain on visits.find. I think I have all the visits where someone has volunteered in a date range, but getting back to the people is still a hassle I haven't worked out, and I think the path I'm on is bad so far. It all starts at line 114 here http://github.com/Thav/freehub/blob/volunteer-report/app/controllers/reports_controller.rb

I'm going to drop this for a while. Work on doing some database import stuff and getting freehub rolled out in our shop.

-Tony

[Michael Kirk]

I'm not very familiar with named scopes, but it seems like you can chain off of them (even though you can't chain off of "find").

So while this won't work:

Visit.find(:all, :conditions => other_conditions).before(time1).after(time2)

It seems like this will:

Visit.before(time1).after(time2).find(:all, :conditions => other_conditions)

I'm also not familiar with "chain_finders", but it looks like it just takes a dictionary of method names and their parameters, serially calling one after the other. You're including a :volunteered_greater key in the dictionary, yet :volunteered_greater doesn't seem to correspond to any existing method, rather you're explicitly using it later to compare against. If I've understood it correctly, I think including the :volunteered_greater key in the dictionray, when the method doesn't exist, would cause a problem.

Ultimately, what data structures do you want to have at the end of the method? 

I think it's a list of volunteers who've worked more than :volunteered_greater, with their total time calculated (within the given time frame), and all the visits that went into calculating that total volunteer time, right? Anything else?

M

M

[Alon Salant]

Comments below...

On Sun, Aug 22, 2010 at 2:05 PM, Michael Kirk
<michael....@gmail.com> wrote:
> I'm not very familiar with named scopes, but it seems like you can chain off
> of them (even though you can't chain off of "find").
> So while this won't work:
> Visit.find(:all, :conditions => other_conditions).before(time1).after(time2)
> It seems like this will:
> Visit.before(time1).after(time2).find(:all, :conditions => other_conditions)

Correct. Finders and named scopes do return different objects so are
not chainable as you'd expect. This is one of the big changes in
ActiveRecord for Rails 3 - everything returns the same proxy object
and everything is chainable.

> I'm also not familiar with "chain_finders", but it looks like it just takes
> a dictionary of method names and their parameters, serially calling one
> after the other.

I wrote this to support some of the reporting forms. The
implementation is in lib/named_scope_extensions.rb. Your description
of what it does is correct.

>> Didn't have great luck with this last week. Running into a lot of trouble
>> with find and other functions returning arrays instead of ActiveRecord
>> collections, so I can't run visits.find(conditions...).before('2010-01-01'),
>> because I can't chain on visits.find. I think I have all the visits where
>> someone has volunteered in a date range, but getting back to the people is
>> still a hassle I haven't worked out, and I think the path I'm on is bad so
>> far.

Do you think you could write the SQL (or close to it) to get what you
want? In complex cases, you really need to understand the query you
want to make (in database terms) in order to get ActiveRecord to do it
for you.

For example, here's a query to find all people who visited after a
specific date:L

select * from people left join visits on visits.person_id = people.id
where visits.arrived_at > '2006-08-01' group by people.id

Alon