Databases
StackOverflow2013
Postsdbo
POProblems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)
    primarykey
    data
    text
    <p>See updates at bottom of question.</p> <p>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. </p> <p>Here's the set up:<br> <code>#User.rb</code><br> has_many :products, :through => :seasons, :uniq => true<br> has_many :varieties, :through => :seasons, :uniq => true<br> has_many :seasons </p> <p><code>#product.rb</code><br> has_many :seasons<br> has_many :users, :through => :seasons, :uniq => true<br> has_many :varieties </p> <p><code>#season.rb</code><br> belongs_to :product<br> belongs_to :variety<br> belongs_to :user<br> named_scope :by_product_name, :joins => :product, :order => 'products.name'</p> <p><code>#variety.rb</code><br> belongs_to :product<br> has_many :seasons<br> has_many :users, :through => :seasons, :uniq => true </p> <p>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.</p> <p><code>#user/show</code> </p> <pre><code>&lt;% @user.products.each do |product| %&gt; &lt;%= link_to product.name, product %&gt; &lt;% @user.varieties.find_all_by_product_id(product.id).each do |variety| %&gt; &lt;%=h variety.name.capitalize %&gt;&lt;/p&gt; &lt;% end %&gt; &lt;% end %&gt; </code></pre> <p>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. </p> <p>Here's the log output for the code above: </p> <pre><code>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 </code></pre> <p>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 <code>seasons</code>, 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 <code>seasons</code> model: </p> <pre><code>&lt;% @user.seasons.by_product_name.each do |season| %&gt; &lt;%= link_to season.product.name, season.product %&gt; #Note: I couldn't get this loop to work at all, so I settled for the following: #&lt;% @user.varieties.find_all_by_product_id(product.id).each do |variety| %&gt; &lt;%=h season.variety.name.capitalize %&gt; &lt;%end%&gt; &lt;%end%&gt; </code></pre> <p>Here's the log output for that: </p> <pre><code>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 </code></pre> <p>I'm having two problems:<br> (1) The :uniq option is not working for <code>products</code>. Three distinct versions of the same product are displaying on the page.<br> (2) The :uniq option is not working for <code>varieties</code>. 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.</p> <p>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. </p> <p>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: <a href="https://rails.lighthouseapp.com/projects/8994/tickets/2189-count-breaks-sqlite-has_many-through-association-collection-with-named-scope" rel="nofollow noreferrer">https://rails.lighthouseapp.com/projects/8994/tickets/2189-count-breaks-sqlite-has_many-through-association-collection-with-named-scope</a></p> <h2>Update</h2> <p>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. </p> <p>What's happening right now is: </p> <p>user = get me user<br> seasons = get me user's seasons (say, there are 3 seasons for the user)<br> products = get me the products<br> products += get me the products<br> products += get me the products </p> <p>Give me each of the products </p> <p>So what's happening is not that uniq is breaking, but rather than there's no delimeter on the named scope. (I think). </p> <p>I tried the following, but it throws this exception: odd number list for Hash </p> <pre><code>named_scope :by_product_name, lambda { |seasons| { season_ids = seasons.map { |season| season.id }; :joins =&gt; :product, :conditions =&gt; { :seasons { :id =&gt; season_id } } :order =&gt; 'products.name' } } </code></pre> <p>Ideas?</p> <h2>Update #2 </h2> <p>Ok, now I'm thinking maybe it's not the named scoped at all. </p> <p>In <code>#user/show</code>, I just changed the loop to bypass the named scope: </p> <pre><code>&lt;% @user.seasons.each do |season| %&gt; &lt;%= link_to season.product.name, season.product %&gt; #Note: I couldn't get this loop to work at all, so I settled for the following: #&lt;% @user.varieties.find_all_by_product_id(product.id).each do |variety| %&gt; &lt;%=h season.variety.name.capitalize %&gt; &lt;%end%&gt; &lt;%end%&gt; </code></pre> <p>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. </p> <p>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 <code>seasons</code> to hit the <code>products</code>, whereas my original code looped through <code>products</code>. This difference is where the problem is hiding, but I don't know how to fix it. </p> <p>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: </p> <pre><code>undefined local variable or method `product' </code></pre> <p>Seems like that might be another symptom of the same problem? </p> <p>Any other ideas?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMikeH
    1. USMikeH
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POProblems with :uniq => true/Distinct option in a has_many_through association w/ named scope (Rails)
      1. USMikeH
      1. VTBountyStart
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload