Problems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)
- by MikeH
I had to make some tweaks to my app to add new functionality, and my changes seem to have broken the :uniq option that was previously working perfectly.
Here's the set up:
#User.rb
has_many :products, :through = :seasons, :uniq = true
has_many :varieties, :through = :seasons, :uniq = true
#product.rb
has_many :seasons
has_many :users, :through = :seasons, :uniq = true
has_many :varieties
#season.rb
belongs_to :product
belongs_to :variety
belongs_to :user
named_scope :by_product_name, :joins = :product, :order = 'products.name'
#variety.rb
belongs_to :product
has_many :seasons
has_many :users, :through = :seasons, :uniq = true
First I want to show you the previous version of the view that is now breaking, so that we have a baseline to compare. The view below is pulling up products and varieties that belong to the user. In both versions below, I've assigned the same products/varieties to the user so the logs will looking at the exact same use case.
#user/show
<% @user.products.each do |product| %>
<%= link_to product.name, product %>
<% @user.varieties.find_all_by_product_id(product.id).each do |variety| %>
<%=h variety.name.capitalize %></p>
<% end %>
<% end %>
This works. It displays only one of each product, and then displays each product's varieties. In the log below, product ID 1 has 3 associated varieties. And product ID 43 has none.
Here's the log output for the code above:
Product Load (11.3ms) SELECT DISTINCT `products`.* FROM `products` INNER JOIN `seasons` ON `products`.id = `seasons`.product_id WHERE ((`seasons`.user_id = 1)) ORDER BY name, products.name
Product Columns (1.8ms) SHOW FIELDS FROM `products`
Variety Columns (1.9ms) SHOW FIELDS FROM `varieties`
Variety Load (0.7ms) SELECT DISTINCT `varieties`.* FROM `varieties` INNER JOIN `seasons` ON `varieties`.id = `seasons`.variety_id WHERE (`varieties`.`product_id` = 1) AND ((`seasons`.user_id = 1)) ORDER BY name
Variety Load (0.5ms) SELECT DISTINCT `varieties`.* FROM `varieties` INNER JOIN `seasons` ON `varieties`.id = `seasons`.variety_id WHERE (`varieties`.`product_id` = 43) AND ((`seasons`.user_id = 1)) ORDER BY name
Ok, so everything above is the previous version which was working great. In the new version, I added some columns to the join table called seasons, and made a bunch of custom methods that query those columns. As a result, I made the following changes to the view code that you saw above so that I could access those methods on the seasons model:
<% @user.seasons.by_product_name.each do |season| %>
<%= link_to season.product.name, season.product %>
#Note: I couldn't get this loop to work at all, so I settled for the following:
#<% @user.varieties.find_all_by_product_id(product.id).each do |variety| %>
<%=h season.variety.name.capitalize %>
<%end%>
<%end%>
Here's the log output for that:
SQL (0.9ms) SELECT count(DISTINCT "products".id) AS count_products_id FROM "products" INNER JOIN "seasons" ON "products".id = "seasons".product_id WHERE (("seasons".user_id = 1))
Season Load (1.8ms) SELECT "seasons".* FROM "seasons" INNER JOIN "products" ON "products".id = "seasons".product_id WHERE ("seasons".user_id = 1) AND ("seasons".user_id = 1) ORDER BY products.name
Product Load (0.7ms) SELECT * FROM "products" WHERE ("products"."id" = 43) ORDER BY products.name
CACHE (0.0ms) SELECT "seasons".* FROM "seasons" INNER JOIN "products" ON "products".id = "seasons".product_id WHERE ("seasons".user_id = 1) AND ("seasons".user_id = 1) ORDER BY products.name
Product Load (0.4ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name
Variety Load (0.4ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 2) ORDER BY name
CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name
Variety Load (0.4ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 8) ORDER BY name
CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name
Variety Load (0.4ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 7) ORDER BY name
CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 43) ORDER BY products.name
CACHE (0.0ms) SELECT count(DISTINCT "products".id) AS count_products_id FROM "products" INNER JOIN "seasons" ON "products".id = "seasons".product_id WHERE (("seasons".user_id = 1))
CACHE (0.0ms) SELECT "seasons".* FROM "seasons" INNER JOIN "products" ON "products".id = "seasons".product_id WHERE ("seasons".user_id = 1) AND ("seasons".user_id = 1) ORDER BY products.name
CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name
CACHE (0.0ms) SELECT * FROM "products" WHERE ("products"."id" = 1) ORDER BY products.name
CACHE (0.0ms) SELECT * FROM "varieties" WHERE ("varieties"."id" = 8) ORDER BY name
I'm having two problems:
(1) The :uniq option is not working for products. Three distinct versions of the same product are displaying on the page.
(2) The :uniq option is not working for varieties. I don't have validation set up on this yet, and if the user enters the same variety twice, it does appear on the page. In the previous working version, this was not the case.
The result I need is that only one product for any given ID displays, and all varieties associated with that ID display along with such unique product.
One thing that sticks out to me is the sql call in the most recent log output. It's adding 'count' to the distinct call. I'm not sure why it's doing that or whether it might be an indication of an issue. I found this unresolved lighthouse ticket that seems like it could potentially be related, but I'm not sure if it's the same issue: https://rails.lighthouseapp.com/projects/8994/tickets/2189-count-breaks-sqlite-has_many-through-association-collection-with-named-scope
I've tried a million variations on this and can't get it working. Any help is much appreciated!
