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

Updated . Posted . Visible to the public. Repeats.

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
Last edit
Daniel Straßner
Keywords
duplicate, uniq, id
License
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra dev (2012-04-23 10:16)