Posted over 9 years ago. Visible to the public. Repeats.

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. 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:

Copy
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:

Copy
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:

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

Now the product is only returned once:

Copy
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

Copy
SELECT DISTINCT * FROM products ...

Issues with PostgreSQL

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

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

This has not been fixed yet.

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

Avatar
Henning Koch
Last edit:
over 5 years ago
by Martin Straub
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Henning Koch to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more