New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

has_many :through records duplication problem #27478

Closed
hothero opened this Issue Dec 28, 2016 · 2 comments

Comments

Projects
None yet
4 participants
@hothero

hothero commented Dec 28, 2016

I think this problem is kind of this issue #16791.

There are my models:

class Transaction < ActiveRecord::Base  
  has_many :transaction_items
end

class TransactionItem < ActiveRecord::Base  
  belongs_to :transaction_record
  belongs_to :project
end

class Project < ActiveRecord::Base  
  has_many :transaction_items
  has_many :transaction_records, through: :transaction_items
end

Example Data:
image

I got the wrong value when querying my database due to the sql join:

Project.find(1).transaction_records.sum(:money)
# should 4200, but got 9800

It's a sql join issue not rails issue, and I try the DISTINCT to fix duplication records but still wrong

class Project < ActiveRecord::Base  
  has_many :transaction_items
  has_many :transaction_records, -> { distinct }, through: :transaction_items
end  

It works on query records, but not works on summarize. So when the same statement to sum money, I got this SQL:

SELECT DISTINCT SUM("transactions"."money") FROM "transactions" INNER JOIN "transaction_items" ON "transactions"."id" = "transaction_items"."transaction_id" WHERE "transactions"."deleted_at" IS NULL AND "transaction_items"."deleted_at" IS NULL AND "transaction_items"."project_id" = $1  [["project_id", 1]]

It selects the distinct field of SUM("transactions"."money"), so doesn't work on the duplication.

In last, I make a solution to use subquery.

Transaction.where(id: Project.find(1).transaction_records)

But it's not a good solution if we need more straight thinking . Anyone have idea about how to face the has_many :through with duplication?

@mximos

This comment has been minimized.

mximos commented Jan 3, 2017

Able to reproduce the issue here https://github.com/mximos/RailsIssue27478/

@Sen-Zhang

This comment has been minimized.

Contributor

Sen-Zhang commented Feb 4, 2017

This should work correctly, but not very efficient.

class Project < ActiveRecord::Base  
  has_many :transaction_items
  has_many :transaction_records, -> { distinct }, through: :transaction_items
end 

Project.find(1).transaction_records.sum(&:money)

@rails-bot rails-bot bot added the stale label May 5, 2017

@rails-bot

This comment has been minimized.

rails-bot bot commented May 5, 2017

This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails team are limited, and so we are asking for your help.
If you can still reproduce this error on the 5-1-stable branch or on master, please reply with all of the information you have about it in order to keep the issue open.
Thank you for all your contributions.

@rails-bot rails-bot bot closed this May 12, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment