On Apr 12, 3:41 am, Ciryus <[email protected]> wrote:
> Hi.
>
> I'd like to do a "stats" association with Sequel, but I find myself a
> bit lost. I'm pretty sure there's a way to do that since association
> seem so powerful, but I'm kinda overwhelmed by the number of options
> and which one to use exactly.
>
> Basically, I have two models: Project and Ticket. Each Project has
> Tickets, and each Ticket has hours (the number of hours spent working
> on it). I'd like to be able to load each Project with its total number
> of hours. Something like:
> select sum(hours) as hours from tickets group by project_id;
>
> I'd like to make that an association so that I can eagerly load it. I
> used datamapper before and it handles the (n+1) query problem
> automatically. I'm not exactly sure how to do that in Sequel so that I
> get the stats for all my project without launching N queries.
>
> Any idea?
It's news to me that DataMapper can handle an aggregate association
and eager load it correctly automatically (good for them if that's the
case). Anyway:
Project.many_to_one :ticket_hours, :read_only=>true, :key=>:id,
:dataset=>proc{Ticket.filter(:project_id=>id).select{sum(hours).as
(hours)}},
:eager_loader=>(proc do |kh, projects, a|
projects.each{|p| p.associations[:ticket_hours] = nil}
Ticket.filter(:musicid=>kh[:id].keys).
group(:project_id).
select{[project_id, sum(hours).as(hours)]}.
all do |t|
p = kh[:id][t.values.delete(:project_id)].first
p.associations[:ticket_hours] = t
end
end)
The associations code above uses virtual row blocks without arguments,
so you need to be running on the master branch, or using 2.12 with
Sequel.virtual_row_instance_eval = true.
Note that project.ticket_hours will give you a Ticket object with a
single :hours attribute, instead of an integer (which I'm guessing you
prefer). You can fix this by doing:
class Project
def ticket_hours
if s = super
s[:hours]
end
end
end
While Sequel's associations can do what you want, I would recommend
using a sum cache. If you are using PostgreSQL, my
sequel_postgresql_triggers extension implements one using a trigger,
so the related project gets updated automatically whenever a ticket is
inserted, updated, or delete.
Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
