datamapper/dm-aggregates

DataMapper plugin providing support for aggregates on collections

dm-aggregates's Issues

incorrect sql for limit + count (DM 1.0)

In DM 1.0, the expression Foo.all(:limit=>2).count
produces the query
SELECT COUNT(*) FROM foos LIMIT 2
which is incorrect, as the precedence of LIMIT applies to the one row
returned by count.
This is a big deal with many pagination plugins.

---

Created by Gary Yngve - 2010-06-23 04:55:07 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1334

Aggregate does not accept properties on relationships in :fields

Problem.aggregate(:id.count, :fields => [:user_id, "users.email"], :unique => true, :links => [:user])

ArgumentError: +field+ should be DataMapper::Query::Operator or Symbol or DataMapper::Property, but was String
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/functions.rb:158:in block in aggregate' from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/functions.rb:158:inmap!'
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/functions.rb:158:in aggregate' from (irb):2 from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/railties-3.0.8/lib/rails/commands/console.rb:44:instart'
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/railties-3.0.8/lib/rails/commands/console.rb:8:in start' from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/railties-3.0.8/lib/rails/commands.rb:23:in<top (required)>'
from script/rails:6:in require' from script/rails:6:in

'

aggegrates don't work in Ruby 2.0

require 'data_mapper'
require 'dm-migrations'
require 'dm-aggregates'

class DmNilTest
include DataMapper::Resource
property :id, Serial
property :test_value, Float
end

DataMapper::Logger.new(STDOUT, :debug)
DataMapper.setup(:default, 'postgres://elmar@localhost/dm_nil')

DataMapper.auto_upgrade!
DataMapper.finalize

DmNilTest.all.destroy
10.times do
DmNilTest.create(test_value: rand)
end

puts DmNilTest.aggregate(:test_value.min, :test_value.max, :test_value.avg).inspect

SQL statement is the same both in Ruby 1.9.3 and 2.0.0:
SELECT MIN("test_value"), MAX("test_value"), AVG("test_value") FROM "dm_nil_tests"

but in the latter case the result is [nil, nil, nil]

Incorrect query with M:M

#!/usr/bin/env ruby
#
# encoding: utf-8

require 'dm-sqlite-adapter'
require 'dm-aggregates'
require 'dm-migrations'

DataMapper::Logger.new $stdout, :debug
DataMapper.setup :default, "sqlite::memory:"

class Foo
  include DataMapper::Resource
  property :id, Serial
  has n, :foo_bars
  has n, :bars, :through => :foo_bars
end

class Bar
  include DataMapper::Resource
  property :id, Serial
  has n, :foo_bars
  has n, :foos, :through => :foo_bars
end

class FooBar
  include DataMapper::Resource

  belongs_to :foo, :key => true
  belongs_to :bar, :key => true
end

DataMapper.finalize.auto_migrate!

f = Foo.create
b1 = Bar.create
b2 = Bar.create
b3 = Bar.create
f.bars = [b1, b2, b3]
f.save

puts Foo.all(Foo.bars.id => [1,3]).inspect
puts Foo.all(Foo.bars.id => [1, 3]).count

Output:

 ~ (0.000046) SELECT "foos"."id" FROM "foos" INNER JOIN "foo_bars" ON "foos"."id" = "foo_bars"."foo_id" INNER JOIN "bars" ON "foo_bars"."bar_id" = "bars"."id" WHERE "bars"."id" IN (1, 3) GROUP BY "foos"."id" ORDER BY "foos"."id"
[#<Foo @id=1>]
 ~ (0.000037) SELECT COUNT(*) FROM "foos" INNER JOIN "foo_bars" ON "foos"."id" = "foo_bars"."foo_id" INNER JOIN "bars" ON "foo_bars"."bar_id" = "bars"."id" WHERE "bars"."id" IN (1, 3)
2

Aggregate doesn't work with :fields=>[]

I did this:
Point.all(:fields=> [:player_id], :unique=> true, :order=>[:player_id.asc]).aggregate(:player_id.count)
was trying to get : "SELECT COUNT(player_id) FROM points WHERE deleted_at IS NULL GROUP BY :player_id ORDER BY player_id"
ended up with: "SELECT COUNT("player_id") FROM "points" WHERE "deleted_at" IS NULL" in log

Aggregates support for YAML

dm-aggregates should support YAML-based models.

Currently, merb-admin used on a YAML-based database raises this exception:

undefined method `aggregate' for DataMapper::Adapters::YamlAdapter

A more complete backtrace:

undefined method `aggregate' for #<DataMapper::Adapters::YamlAdapter:0x7f06eed16090> - (NoMethodError)
/tmp/t/gems/gems/dm-aggregates-0.10.2/lib/dm-aggregates/repository.rb:6:in `aggregate'
/tmp/t/gems/gems/dm-aggregates-0.10.2/lib/dm-aggregates/aggregate_functions.rb:168:in `aggregate'
/tmp/t/gems/gems/dm-aggregates-0.10.2/lib/dm-aggregates/aggregate_functions.rb:40:in `count'
/tmp/t/gems/gems/merb-admin-0.8.1/lib/datamapper_support.rb:13:in `count'
/tmp/t/gems/gems/merb-admin-0.8.1/lib/datamapper_support.rb:28:in `paginated'
/tmp/t/gems/gems/merb-admin-0.8.1/app/controllers/main.rb:28:in `list'

---

Created by gioele - 2010-05-13 13:32:29 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1268

Aggregate breaks when using :links => []

Problem.aggregate(:id.count, :fields => [:user_id], :unique => true, :links => [:user])

Will break like so:

DataObjects::SyntaxError: ERROR: missing FROM-clause entry for table "users"
LINE 1: ... "problems" INNER JOIN "problems" "problems_1" ON "users"."i...
^
(code: 16908420, sql state: 42P01, query: SELECT "problems"."user_id", COUNT("problems"."id") FROM "problems" INNER JOIN "problems" "problems_1" ON "users"."id" = "problems_1"."user_id" GROUP BY "problems"."user_id" ORDER BY "problems"."user_id", uri: postgres:tpitale@localhostdocket_development?adapter=postgres&username=tpitale&password=&host=localhost&database=docket_development&path=docket_development)
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/adapters/dm-do-adapter.rb:20:in execute_reader' from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/adapters/dm-do-adapter.rb:20:inblock in aggregate'
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-do-adapter-1.1.0/lib/dm-do-adapter/adapter.rb:276:in with_connection' from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/adapters/dm-do-adapter.rb:14:inaggregate'
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/repository.rb:8:in aggregate' from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/dm-aggregates-1.1.0/lib/dm-aggregates/functions.rb:183:inaggregate'
from (irb):1
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/railties-3.0.8/lib/rails/commands/console.rb:44:in start' from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/railties-3.0.8/lib/rails/commands/console.rb:8:instart'
from /Users/tpitale/.rvm/gems/ruby-1.9.2-p180@docket/gems/railties-3.0.8/lib/rails/commands.rb:23:in <top (required)>' from script/rails:6:inrequire'
from script/rails:6:in `

'

The SQL generated is strange indeed:

SELECT "problems"."user_id", COUNT("problems"."id") FROM "problems" INNER JOIN "problems" "problems_1" ON "users"."id" = "problems_1"."user_id" GROUP BY "problems"."user_id" ORDER BY "problems"."user_id"

I would expect
SELECT "problems"."user_id", COUNT("problems"."id") FROM "problems", "users" WHERE "users"."id" = "problems"."user_id" GROUP BY "problems"."user_id" ORDER BY "problems"."user_id"

Add a ChangeLog

Would be nice to have a ChangeLog to review differences between different versions.

Unexpected behavior when count is used with -

suppose a and b are collections return by finders on a class A:

suppose (a - b).count == 5

suppose we have a chained finder which returns only a subset of items of A:

(a - b).my_filter.count should be <= (a - b).count, but it is actually larger.

See the test case here:

https://gist.github.com/3569c20a3bf30f269699

---

Created by Matt - 2010-11-13 02:18:34 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1446

count method included in resource without dm-aggregates

It's confusing that the count method is included on a resource without dm-aggregates being included. What's more confusing is it appears to return the proper count when no query hash is specified but otherwise will always return 0.

---

Created by Alex - 2010-08-10 17:09:31 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1384

aggregate function count broken for queries with :limit

This is related to the problem and fix posted over a year ago:

 http://groups.google.com/group/datamapper/browse_thread/thread/503cfb37c9ca23e5/3031713f309910b1?lnk=gst&q=size+count#3031713f309910b1

dkubb commented out the optimization of size calling count

 http://github.com/datamapper/dm-more/commit/35ac591458ba477c875b3541f0b419a2c06f00e7 

to use Array#size to properly recognize :limit in the query.

If size recognizes :limit in the query, shouldn't count also?

For instance, dm-pager assumes this of count and breaks on queries with :limit:

 http://github.com/visionmedia/dm-pagination/blob/master/lib/dm-pager/pagination.rb

 options.merge! :total => count(query), :page => page

(The root cause is probably do_mysql adapter not translating the query correctly.)

---

Created by Ming - 2010-02-24 20:34:11 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1203

Can't order by aggregated fields

I'm trying to use the aggregate method and sort the results by a summed field, but it doesn't work.

For example, given the following model, I want to select the top 5 user_ids, by sum of quantity (descending):

class Gift
  include DataMapper::Resource

  belongs_to :user

  property :id, Integer, :serial => true
  property :user_id, Integer, :nullable => false, :index => true
  property :quantity, Integer, :nullable => false, :default => 1
end

This gives me 5 user_ids ordered by the single largest quantity (not quite right):

Gift.aggregate(:fields => [:user_id, :quantity.sum], :limit => 5, :order => [:quantity.desc])

SELECT `user_id`, SUM(`quantity`) FROM `gifts` WHERE (`deleted_at` IS NULL) GROUP BY `user_id` ORDER BY `quantity` DESC LIMIT 5

What I'm looking for is something that will generate the query:

SELECT `user_id`, SUM(`quantity`) FROM `gifts` WHERE (`deleted_at` IS NULL) GROUP BY `user_id` ORDER BY SUM(`quantity`) DESC LIMIT 5

...the most logical syntax for which would be something like:

Gift.aggregate(:fields => [:user_id, :quantity.sum], :limit => 5, :order => [:quantity.sum.desc])

---

Created by Erik Michaels-Ober - 2009-01-27 21:34:31 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/789

SEL for count?

Associations seem to be eager-loading when all that is required is a count. This can take awhile when the count of rows is even in the 10k+ area, if not more.

http://gist.github.com/156762

---

Created by Tony Pitale - 2009-07-27 22:00:09 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/992

dm-aggregates doesn't work when loaded after the model

require 'dm-core'
require 'dm-migrations'

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

class Group
include DataMapper::Resource

property :number, Serial
property :groupname, String, :length => 40
end

DataMapper.auto_migrate!

require 'dm-aggregates'

Group.count

END

ruby-1.9.2-preview3 mungo:dm-snippets snusnu$ bundle exec ruby zem.rb
~ (0.000102) SELECT sqlite_version(*)
~ (0.000146) DROP TABLE IF EXISTS "groups"
~ (0.000014) PRAGMA table_info("groups")
~ (0.000319) CREATE TABLE "groups" ("number" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "groupname" VARCHAR(40))
~ (0.000069) SELECT "number", "groupname" FROM "groups" ORDER BY "number"

---

Created by Martin DeMello - 2010-06-28 23:47:25 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/1344

Avg returns 0.0 for empty table (dm-1.2.0)

Hi,

when i do

MyModel.avg(:something)

i get 0.0 for an empty table (sqlite3, integer property). I think this is wrong and should return nil instead (SQL AVG returns null).
I guess this is caused by the to_f in:

def avg(property, value)
    property.primitive == ::Integer ? value.to_f : property.load(value)
end

in the dm-aggregates/adapters/dm-do-adapter.rb.

A quick workaround i use is to call MyModel.aggregate(:all.count, :something.sum) and compute the average myself.

Incorrect query when counting has n, :through relationships.

I've reproduced a bug where dm-aggregates would not generate a GROUP BY clause to remove duplicate entries when counting a has n, :through relationship, thus resulting in inflated numbers.

git clone https://github.com/postmodern/dm-bug-report.git -b relationship_count
cd dm-bug-report/
bundle install
ruby bug.rb
DEBUG=true ruby bug.rb