Problems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)

Question

See updates at bottom of question.

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
has_many :seasons

#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

Update

I think the problem is that the named_scope is being called once for each season. There needs to be something in the named_scope that narrows the returned products by season id.

What's happening right now is:

user = get me user
seasons = get me user's seasons (say, there are 3 seasons for the user)
products = get me the products
products += get me the products
products += get me the products

Give me each of the products

So what's happening is not that uniq is breaking, but rather than there's no delimeter on the named scope. (I think).

I tried the following, but it throws this exception: odd number list for Hash

named_scope :by_product_name, lambda { |seasons| { season_ids = seasons.map { |season| season.id }; :joins => :product, :conditions => { :seasons { :id => season_id } }  :order => 'products.name' } }  

Ideas?

Update #2

Ok, now I'm thinking maybe it's not the named scoped at all.

In #user/show, I just changed the loop to bypass the named scope:

<% @user.seasons.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%>    

The above doesn't use the named scope, but I'm still getting the same result. In other words, I'm still seeing all instances of each product, instead of just one.

The code above that creates the first loop is the same as my original code that I listed at the top of this question. The difference is that this code is looping through seasons to hit the products, whereas my original code looped through products. This difference is where the problem is hiding, but I don't know how to fix it.

Also, I mentioned in my original question that I couldn't get the varieties loop working either. You can see the line commented in the code directly above. When looping through the seasons, instead of products, when Rails hits that varieties loop, it throws a name error:

undefined local variable or method `product'  

Seems like that might be another symptom of the same problem?

Any other ideas?

Answer 1

I believe the issue is the formatting of the lambda. I obviously can't run the SQL, but the following lambda DOES create an apporpriate hash:

lambda { |seasons| season_ids = seasons.map { |season| season.id }; { :joins => :product, :conditions => { :seasons => { :id => season_ids } }, :order => 'products.name' } }

The output of that call with two seasons with ids 1 and 2 is:

{:joins=>:product, :conditions=>{:seasons=>{:id=>[1, 2]}}, :order=>"products.name"}