With any ORM there will be instances where pure object relationships make for either very complex code or code that does not scale well performance-wise as the data set involved grows when compared to the efficiency of the underlying database's SQL engine.
In those circumstances remember that views and stored procedures are available - schema_plus is a gem that let's you define views as a part of your schema.r b file and while stored procedures violate the ActiveRecord design pattern they can be used with AR and are sometimes necessary in order to create a system that scales and performs well. Max On 1/6/12, Peter Vandenabeele <pe...@vandenabeele.com> wrote: > 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. > > -- 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.
