Read more

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

Henning Koch
April 23, 2012Software engineer at makandra GmbH

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

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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
April 23, 2012Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2012-04-23 12:16)