Thanks for your patience. That's neat. I didn't know you could declare a linkage table like that. I don't think that scenario would fit into what I'm thinking.
Please let me rephrase my example scenario. What about this scenario: id name 1 "foo" 2 "bar" 3 "hello" 4 "world" and filters table: id name 1 "top" 2 "sweatshirt" 3 "hoodie" 4 "emblem" and a product_filter table id product_id filter_id 1 1 1 2 1 2 3 2 3 4 3 1 5 4 1 6 4 3 7 4 4 Let's say that a user wants to search for all products which have the filter "Top". Then doing what you suggested, print 'tops', all_in_one_tops Would correctly give us all the products which have the filter "Top", which are: "foo", "hello", "world" So this is good. Now, let's say the user wants a product which is described as both "Top" AND "Sweatshirt". The desired result would be: "foo" Even though the product "hello" has the filter "Top", it is not included in the previous search because the user wanted a combination of two specific filters: "Top" AND "Sweatshirt". Similarly, if the user decides he wants the filter combination: "Top" AND "Hoodie", then the desired result is: "world" Because the product "world" has at least the filter combination "Top" AND "Hoodie", even though it also has the filter "emblem". The example code you wrote works great when trying to find out all products which belong to a single filter, but I'm wondering how one would build a query which would return products based on several filters. On Friday, May 10, 2013 3:02:40 PM UTC-4, Niphlod wrote: > > sorry, I didn't understood what you model was. > > So, let's say you have > products table: > > id name > 1 "foo" > 2 "bar" > > and filters table: > > id name > 1 "top" > 2 "sweatshirt" > 3 "hoodie" > > and a product_filter table > > id product_id filter_id > 1 1 1 > 2 1 2 > 3 2 3 > > so, basically, when you "de-normalize" it, it would be > "foo" is "top", "sweatshirt" > and > "bar" is "hoodie" > . > > you want to find all products that have the "filter" equal to "top". > ok. > > #all filters matching "hoodie" > myfilter = db(db.filters.name == "hoodie") > #all product_filters matching the previous id(s) > myfiltered_products = > db(db.product_filter.filter_id.belongs(myfilter._select(db.filters.id))) > #all products matching product_filter id(s) > top_products = > db(db.products.id.belongs(myfiltered_products._select(db.product_filter.product_id))) > print top_products.select() > > However, it's a little bit cumbersome to this syntax, although its > perfectly working. > > Another way can be > > #linkage between the 3 tables > all_in_one = db( > (db.products.id == db.product_filter.product_id) & > (db.filters.id == db.product_filter.filter_id) > ) > > all_in_one_hoodies = all_in_one(db.filters.name == "hoodie").select() > #optionally, you can just select(db.products.ALL) > all_in_one_tops = all_in_one(db.filters.name == "top").select() > all_in_one_sweatshirts = all_in_one(db.filters.name == "sweatshirt"). > select() > > print 'hoodies', all_in_one_hoodies > print 'tops', all_in_one_tops > print 'sweatshirts', all_in_one_sweatshirts > > > Sorry for the misunderstanding .... I hope it's a bit clearer now. > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
