Why has_many :through associations can return the same record multiple times

An association defined with has_many :through will return the same record multiple times if multiple join models for the same record exist (a n:m relation). To prevent this, you need to add ->{ uniq } as second argument to has_many (below Rails 4 it is a simple option: has_many :xyz, :uniq => true).

Example

Say you have an Invoice with multiple Items. Each Item has a Product:

class Invoice < ActiveRecord::Base
  has_many :items
  has_many :products, :through => :items
end

class Item < ActiveRecord::Base
  belongs_to :invoice
  belongs_to :product
end

class Product < ActiveRecord::Base
  has_many :items
end

Further say you have multiple items for the same product and invoice in the items table:

id invoice_id product_id
11 1001 55
12 1001 55

Now #products will return the product with ID #55 two times for this invoice:

invoice = Invoice.find(1001)
invoice.products # => [#<Product id: 55>, #<Product id: 55>]

You probably wanted Invoice#products to mean "all products involved with the invoice", so you need to use the :uniq option of has_many:

class Invoice < ActiveRecord::Base
  has_many :items
  has_many :products, :through => :items, :uniq => true
end

Now the product is only returned once:

invoice = Invoice.find(1001)
invoice.products # => [#<Product id: 55>]

How the :uniq option works

In the example above it changes the MySQL query for Invoice#products to something like

SELECT DISTINCT * FROM products ...

Issues with PostgreSQL

In Postgres, SELECT DISTINCT * FROM products ... will fail when any column of products is of type json:

ActiveRecord::StatementInvalid Exception: PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json

This has not been fixed yet. Show archive.org snapshot

Henning Koch Almost 12 years ago