[Dbix-class] COUNT over relationship

[Christian Lackas]

Hi Everybody,

hope you can help me formulating this in DBIx:
So, I have Groups, which have Events and Members, each Event has a
duration (time required per Member) and I now want to efficiently get
the total time required for each event (duration * number of members in
group, event belongs to) as well as the time per group (sum of event
durations * number of members).

Unfortunately, I even don't know how to do this in raw SQL, but
for the 2nd case something like this (not working):

    SELECT SUM(events.duration)*COUNT(members.id) WHERE groups.id = ?
        AND events.group_id = groups.id 
        AND members.group_id = groups.id;

Main problem here is the COUNT().

An example:

    Group has_many Events
          has_many Members
    Event belongs_to Group
    Member belongs_to Group

    Group 1:
        Event 1: duration 1.0
        Event 2: duration 2.0
        Member 1
        Member 2
        Member 3

Total duration for Event 1 = 3*1.0 = 3, Event 2 = 3*2.0 = 6.
Total duration for Group 1 = 3*(1.0+2.0).

I know, of course, how to fetch all data and then do the math in Perl,
however, I hoped it would be possible to let the RDBMS do the work here.

Thanks for your input.
 Christian

-- 
http://www.inviCRO.com