Chained aggregate call across association in DataMapper (ruby)

Question

I am working on a simple budget app using Sinatra and DataMapper in Ruby.

I want to get the sum of all transactions across all income accounts within the last 30 days.

Something like Account.income_accounts.account_entries.sum(:amount, :transaction_date.gte => Date.today - 30) should work. Instead, the limiting condition on transaction_date is getting ignored, returning the sum of the amount for all entries for all income accounts.

Given the following:

class Account
  include DataMapper::Resource

  has n, :account_entries

  property :id,        Serial
  property :name,      String
  property :acct_type, String

  def self.income_accounts
    all(:acct_type => 'Income')
  end
end

class AccountEntry
  include DataMapper::Resource

  belongs_to :account

  property :id,               Serial
  property :account_id,       Integer
  property :description,      String
  property :amount,           BigDecimal
  property :transaction_date, DateTime
end

I am properly requiring dm-aggregates. I am new to DataMapper. If it matters, I am using a sqlite3 database. I really don't want to resort to using ruby to sum the results. It also feels wrong to resort to executing raw SQL for this type of simple aggregate query.

Can anyone shed some light on this? I would love to be pointed in the right direction regarding chained finders in DataMapper, particularly with aggregates. My spelunking into the API and the DataMapper site hasn't yielded a solution as of yet.

Answer 1

I don't know the answer to this off the top of my head, but the #datamapper channel on irc.freenode.org is pretty active with the core team and other enthusiasts. I'm sure they'd be able to help you out.

Answer 2

DateTime uses second as it's base unit Date.today - 30 is 30 seconds ago. Try Date.today - 30.days

Answer 3

Did you try DateTime.now-30 or maybe even Time.now-30*3600*24 instead of Date.today-30 ?

Answer 4

User error. I mucked around with to_s on DateTime to use the time formats in strftime. When removed, the chained aggregate worked as anticipated.

Answer 5

I just wrote a small stand-alone script to test your example, and it appears to return the correct results. Please note I am using edge extlib, dm-core, and dm-more all installed from git:

#!/usr/bin/env ruby -Ku

# encoding: utf-8

require 'rubygems'
require 'dm-core'
require 'dm-aggregates'

DataMapper::Logger.new($stdout, :debug)
DataMapper.setup(:default, 'sqlite3::memory:')

class Account
  include DataMapper::Resource

  property :id,        Serial
  property :name,      String
  property :acct_type, String

  has n, :account_entries

  def self.income_accounts
    all(:acct_type => 'Income')
  end
end

class AccountEntry
  include DataMapper::Resource

  property :id,               Serial
  property :description,      String
  property :amount,           BigDecimal
  property :transaction_date, Date

  belongs_to :account
end

DataMapper.auto_migrate!

account = Account.create(
  :name      => 'Test Account',
  :acct_type => 'Income'
)

5.times do |n|
  account.account_entries.create(
    :description      => "Account Entry #{n}",
    :amount           => 1.00,
    :transaction_date => Date.today
  )
end

puts Account.income_accounts.account_entries(:transaction_date.gte => Date.today - 30).sum(:amount).to_s('F')  # => 5.0

Can you run the above program and let me know what it returns for you? If you get something other than 5.0, try updating to the latest packages and retry.