Re: People who bought this also bought (amazon style) functionality...logic problem

On 9/26/06, Richard Lynch <ceo@l-i-e.com> wrote:

On Tue, September 26, 2006 7:18 am, Ryan A wrote: I want to write a "module" for xcart (a commercial 'shopping cart') which would be like what Amazon.com offers when you go to shop on their site... when you click on a product to see its details you get a little box below that recommends more products based on what others have purchased eg: If you click on a toy helicopter it would recommend extra rotor blades,landing gear etc eg 2: If you click on Eminem's latest album it would show you his other two albums that people bought with this album etc I'm trying to work out the logic to do this... and quite frankly have hit a brick wall. Possibly because Amazon's algorithm does not down-scale well... So far: ------- I was thinking when someone (Joe) purchases X with product id Y I would put that in a seperate table, then when someone (Jane) else comes looking at item Y I would display everything that Joe bought... but then it gets a little complicated, if Jane buys Y and other stuff that Joe didnt buy... how do I merge what Joe and Jane bought to display to the next visitor? (I can only dispaly 5 recommendations at once) You want something like this: select other_stuff.product_id, count(order_id) as score from orders as other_stuff, orders as this_product where other_stuff.order_id = this_product.order_id and other_stuff.product_id != $product_id and this_product.product_id = $product_id group by other_stuff.id order by sold DESC limit 5 The point being that you get the 5 things that are MOST purchased in other orders with this same product. The above query may well bring your DB to its knees, and you may need to optimize the heck out of it.

Yeah i was just thinking about that as i read it. The one thing I would do is generate some sort of table on a scheduled bases that links ids of the items that were bought into a some sort of group. and then just use that table on the page you are showing. Curt.