Retrieve models with summed statistics

Question

Given the following DB schema and Sequel Models, how do I select a set of nodes summed by their hits, while retaining the result as model instances with all the data and working methods thereon?

Sequel.migration{ change{
  create_table :nodes do
    primary_key :id
    String      :name
    # other columns
  end

  create_table :node_hits do
    foreign_key :node_id, :nodes, null:false
    Date        :date, null:false
    Integer     :hits, null:false
    primary_key [:node_id,:date]
  end
end

class Node < Sequel::Model
  one_to_many :node_hits
  def is_awesome?
    # use data known about the node
  end
end

class NodeHit < Sequel::Model
  many_to_one :node
end

The following code appears to "work", but fails to actually have the properties needed for the Node.is_awesome? method to function properly (due to missing data about the node):

base   = Node.filter(redacted:false)
recent = base.join(:node_hits,node_id: :id).filter{ date>Date.today-7 }
totals = recent.select_more{[ id, sum(hits)as(hits) ]}.group_by(:id)

puts totals.sql
#=> SELECT "id", sum("hits") AS "hits"
#=> FROM "nodes"
#=> INNER JOIN "node_hits" ON ("node_hits"."node_id" = "nodes"."id")
#=> WHERE (("name" != 'phrogz')
#=>       AND ("archived" IS FALSE)
#=>       AND ("date" > '2012-07-13'))
#=> GROUP BY "id"

totals.all.first.is_awesome?
#=> Error: `name` is nil for the Node instance.

How can I fetch whatever fields Sequel normally fetches for the model?

My final requirements are to get the top N nodes based on the sum of hits within the last week, along with the number of hits for that week, while allowing the Node instances to be fully featured (supporting all data known for a Node).

Answer 1

You need to select all columns from the nodes table. Here's what I'd use:

totals = Node.filter(redacted:false).
  select_all(:nodes).
  select_more{sum(hits).as(hits)}.
  group(:nodes__id).
  join(:node_hits, node_id: :id).
  filter{ date>Date.today-7 }