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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
