datamapper / dm-aggregates Goto Github PK
View Code? Open in Web Editor NEWDataMapper plugin providing support for aggregates on collections
Home Page: http://datamapper.org/
License: MIT License
DataMapper plugin providing support for aggregates on collections
Home Page: http://datamapper.org/
License: MIT License
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
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:in
map!'
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:in
start'
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
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]
#!/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
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
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
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:in
block 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:in
aggregate'
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:in
aggregate'
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: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 `
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"
Would be nice to have a ChangeLog to review differences between different versions.
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
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
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
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
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.
Created by Tony Pitale - 2009-07-27 22:00:09 UTC
Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/992
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
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.
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.