[RESOLVED] "people who bought this product also bought..."

sidsel

When online shops show a list of previous bought products together with a listed product I assume they do a search in previous orders and pick the top most sold.

My tables looks like this:

itemstable:

id | itemid

1 | 1000
2 | 1001
3 | 1002

ordertable:

orderid | itemid

111 | 1000
111 | 1002
112 | 1000
112 | 1001
113 | 1000
113 | 1002

If a user is looking at itemid 1000, item 1002+1001 should be listed below in that order.

Any ideas to make a query that picks the top most sold itemids ?

I am afraid that a query like this will be a little heavy if it has to loop through every order on every product page. Any ideas to optimize this?

cgraz

You need to count the most sold items as you said, but only within orders that contain the existing itemid (this is the part that ensures you're only grabbing products that were purchased along with this product).

SELECT i.id, 
   i.itemid, 
   COUNT( o.orderid ) AS qtysold
FROM (
      SELECT orderid, itemid
      FROM ordertable AS t
      WHERE t.itemid = 1000
      GROUP BY orderid
   ) AS t
INNER 
   JOIN ordertable AS o 
      ON o.orderid = t.orderid
INNER 
   JOIN itemstable AS i 
      ON i.itemid = o.itemid
      AND i.itemid != 1000
GROUP BY o.itemid 
ORDER BY qtysold DESC 
LIMIT 5;

or a faster query:

SELECT i.id, 
   i.itemid, 
   COUNT( o.orderid ) AS qtysold
FROM itemstable AS i
INNER
   JOIN ordertable AS o 
      ON o.itemid = i.itemid
INNER
   JOIN ordertable AS t 
      ON o.orderid = t.orderid
      AND t.itemid = 1000
WHERE i.itemid != 1000
GROUP BY o.itemid 
ORDER BY qtysold DESC 
LIMIT 5;

The i.itemid != 1000 ensures that you don't count the current product in your result set as it will be in every order, and the customer is already viewing it anyways.

cgraz

I noticed you marked this thread resolved. Did those work out for you then? Find another solution? Just curious...

sidsel

I tried the second query and it worked with no problems... thanks for the help.

sidsel

I know I marked this thread resolved but I got another question...

I want to show products only sold more than once. I tried to add another condition to the query like below but if fails... it seems that qtysold can't be used as reference like I did.

SELECT i.id,
i.itemid,
COUNT( o.orderid ) AS qtysold
FROM itemstable AS i
INNER
JOIN ordertable AS o
ON o.itemid = i.itemid
INNER
JOIN ordertable AS t
ON o.orderid = t.orderid
AND t.itemid = 1000
WHERE i.itemid != 1000 AND qtysold > 1
GROUP BY o.itemid
ORDER BY qtysold DESC
LIMIT 5;

cgraz

If you need to filter in this way on some sort of aggregate, you need to using HAVING

GROUP BY ....
HAVING qtysold > 1