On Fri, Jan 6, 2012 at 2:13 AM, Linus Pettersson <linus.petters...@gmail.com > wrote:
> Hi! > > I have an app that manages products. I import the products from several > resellers and they all name their categories different. Because of this I > have resellercategories that are mapped to my own subcategories. > > Categories > - Subcategories (belongs_to Category) > > Resellercategories (belongs_to Subcategory) > > Products (belongs_to Resellercategory) > > > > Now I want to show the categories in a special way. If the user filters > products by gender, let's say 'female', then I only want to show the > categories and subcategories which have products for females. This is where > I'm stuck! > > I created a query like this that actually gets the correct "Categories". > But it doesn't seem restrict the subcategories. > > The query: > http://snipt.net/Linuus/categories-1?key=2d5d54fd573f0afe60eaa3c47a23fd4d > How did you actually _retrieve_ the subcategories. By using the "joins" there, there is a chance your query only retrieves the categories (as in "SELECT "categories".* FROM "categories" INNER JOIN "subcategories" ...). So maybe you have a second query for the subcategories (that is not correctly filtered then) Maybe you do `category.subcategories` @menu_categories = Category.joins(:subcategories) .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id") .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id") .group("categories.id") .order("categories.name ASC") .where("p.gender = 'unisex' OR p.gender = ?", session[:gender]) @menu_categories.each do |mc| mc.subcategories .. end This `mc.subcategories` executes a new query that is not taking into account the filtering. So, you are doing 1+n queries (and the n queries are not what you expect). Maybe you need @menu_categories = Category.includes(:subcategories) # INCLUDES here .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id") .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id") .group("categories.id") .order("categories.name ASC") .where("p.gender = 'unisex' OR p.gender = ?", session[:gender]) That will get the subcategories from the db in 1 (more complex) query with results that are filtered. If you then do @menu_categories.each do |mc| mc.subcategories # this should not trigger new SQL queries .. end Do this rails console and read the SQL carefully (or look in your development log carefully which SQL is executed). HTH, Peter -- Peter Vandenabeele http://twitter.com/peter_v http://rails.vandenabeele.com -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
