flyerhzm / eager_group Goto Github PK
View Code? Open in Web Editor NEWfix n+1 aggregate sql functions for rails
License: MIT License
fix n+1 aggregate sql functions for rails
License: MIT License
I have an association where I use a text string for my primary key.
has_many :children, class_name: "ActsAsTaggableOn::Tag", foreign_key: :parent_name, primary_key: :name, dependent: :nullify
define_eager_group :children_count, :children, :count, :*
when I do eager_group count, it is associating with the object's id instead of the association's PK (:name)
irb(main):016:0> tag = ActsAsTaggableOn::Tag.find_by(name: "Africa")
irb(main):017:0> tag.children.count
ActsAsTaggableOn::Tag Count (0.5ms) SELECT COUNT(*) FROM "aat_tags" WHERE "aat_tags"."parent_name" = $1 [["parent_name", "Africa"]]
=> 60
irb(main):018:0> tag.children_count
ActsAsTaggableOn::Tag Count (1.2ms) SELECT COUNT(*) AS "count_all", "aat_tags"."parent_name" AS "aat_tags_parent_name" FROM "aat_tags" WHERE "aat_tags"."parent_name" = $1 GROUP BY "aat_tags"."parent_name" [["parent_name", "3018"]]
=> 0
irb(main):019:0> tag.id
=> 3018
Let's say I have Departments and Employees, and every employee has an employee_type "temporary", "permanent", "retired", I dunno.
I wanna fetch a list of Departments and for each one have an attribute with an array of distinct employee_types found in that Department's employees.
I could hack out the proper GROUP BY query to get these (although I haven't yet). Using postgres-specific functions maybe even get the multiple unique/distinct values in a single SQL result row, as a pg array or something. Although I don't know if I'd need to do that if I was just writing it out manual.
Is there any hope of getting eager_group
flexible enough to let me write this and have it magically done and have the array assigned to a model attributes?
In eager_group_spec.rb
if I change data.
student4 = Student.create name: 'Student 4'
teacher1.students = [student1]
teacher2.students = [student2, student2, student3, student4]
Then teacher1.students_count
result will be 4.
The students_count
does not count by distinct records.
If I add post3 in data.rb
post1 = Post.create(title: "First post!")
post2 = Post.create(title: "Second post!")
post3 = Post.create(title: "Third post!")
post1.comments.create(status: 'created', rating: 4)
post1.comments.create(status: 'approved', rating: 5)
post1.comments.create(status: 'deleted', rating: 0)
post2.comments.create(status: 'approved', rating: 3)
post2.comments.create(status: 'approved', rating: 5)
eager_group_spec.rb
it 'gets both Post#approved_comments_count and Post#comments_average_rating' do
posts = Post.all.eager_group(:approved_comments_count, :comments_average_rating)
expect(posts[0].approved_comments_count).to eq 1
expect(posts[0].comments_average_rating).to eq 3
expect(posts[1].approved_comments_count).to eq 2
expect(posts[1].comments_average_rating).to eq 4
expect(posts[2].approved_comments_count).to eq 0
end
Result:
Failure/Error: expect(posts[2].approved_comments_count).to eq 0
expected: 0
got: nil
I found a issue with MySQL (or MariaDB in my case) and using sum
aggregation.
The Primary keys on the DB query seem to be returned with string primary_keys. Which then does not match the primary_key
of the given record for mapping the values.
As the tests only run against sqlite this issue does not seem present. But i thought it might be interesting for someone else.
My change can be seen here: https://github.com/maxigs/eager_group/blob/master/lib/eager_group/preloader.rb#L48
(I just copied the whole class into an initializer in my rails project for now, so i could test it with real database)
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.