How to get related products?

[dr.pepper]

I've got an 'orders' table which contains

 

order_id

sub_total

total

date

 

I also have an 'orders_products' table which contains

 

order_product_id

order_id

product_id

product_name

quantity

price

 

Therefore, the orders table contains a list of all orders and the orders_products lists all products associated to each order using the order_id field. Therefore, there could be multiple products within orders_products which have the same order_id. E.g

 

Someone orders 2 books, 1 pen and 1 pencil. The order number is 17 so 17 will go into the orders table and the book, pen and pencil will go in the orders_products table with the order_id set to 17.

 

What I need to do is find all related/similar items for each product. Therefore, the pen will be related to the book and the pencil because someone bought this in the same order, in the same way as the book will be related to the pen and pencil. So when someone clicks on the book detail page I need to find all related/similar products. Ideally, the products that have been in the same order the most times would appear first. E.g if the pen has been in an order with the pencil 20 times and the book only 2 times then the pencil would appear before the book on the pen detail page. I would also like to limit this to the 3 highest products.

 

The first thing I assume I need to do is link the two tables so I have so far got the following query:

 

SELECT * FROM `orders` o INNER JOIN orders_products p ON o.order_id = p.order_id 

 

I would then pass in the product_id that I want to find the related products for e.g if the pen was product_id 24

 

SELECT * FROM `orders` o INNER JOIN orders_products p ON o.order_id = p.order_id WHERE p.product_id = '24'

 

I'm unsure where to go from here, if anyone can help?

 

[fenway]

OK -- so at this point you have all of the orders for any given product.

 

Now, you need to use this as a derived table, and join back the orders_products table, using the order_id as the join condition, and then group by product_id, and COUNT(*) all of the products ever ordered together.

[dr.pepper]

OK -- so at this point you have all of the orders for any given product.

 

Now, you need to use this as a derived table, and join back the orders_products table, using the order_id as the join condition, and then group by product_id, and COUNT(*) all of the products ever ordered together.

 

Thanks for your reply, I seem to be getting there now.

 

SELECT * FROM `orders` o 
INNER JOIN orders_products p ON o.order_id = p.order_id 
INNER JOIN orders_products p2 ON o.order_id = p2.order_id
WHERE p2.product_id = '24'
GROUP BY p.product_id

 

Tried the above which returns all related products. Would I group by p.product_id or p2.product_id?

 

Also, where would I count the ordered products?

[fenway]

If you call "p" the "originalProduct" and "p2" the "relatedProducts", it won't be confusing.

 

I don't know what you mean by "where to count".

[dr.pepper]

If you call "p" the "originalProduct" and "p2" the "relatedProducts", it won't be confusing.

 

I don't know what you mean by "where to count".

 

Thanks. You said to "COUNT(*) all of the products ever ordered together". Do you mean within the same query?

 

The query I have so far which returns all related products is:

 

SELECT `o` . * , `p` . *
FROM `orders` AS `o`
INNER JOIN `orders_products` AS `p` ON o.order_id = p.order_id
INNER JOIN `orders_products` AS `p2` ON o.order_id = p2.order_id
WHERE (
p2.product_id = '020577'
)
AND (
p.product_id != '020577'
)

[dr.pepper]

Actually, I think I seem to have done it now using the following query

 

SELECT `o`.*, `p`.*, COUNT(*) as num 
FROM `orders` AS `o` 
INNER JOIN `orders_products` AS `p` ON o.order_id = p.order_id 
INNER JOIN `orders_products` AS `p2` ON o.order_id = p2.order_id 
WHERE (p2.product_id = '020577') 
AND (p.product_id !='020577') 
GROUP BY `p`.`product_id` 
ORDER BY num DESC
LIMIT 3

 

This should return 3 of the most related products. Can you please have a check and confirm it looks ok?

[Muddy_Funster]

Actually, I think I seem to have done it now using the following query

 

SELECT `o`.*, `p`.*, COUNT(*) as num 
FROM `orders` AS `o` 
INNER JOIN `orders_products` AS `p` ON o.order_id = p.order_id 
INNER JOIN `orders_products` AS `p2` ON o.order_id = p2.order_id 
WHERE (p2.product_id = '020577') 
AND (p.product_id !='020577') 
GROUP BY `p`.`product_id` 
ORDER BY num DESC
LIMIT 3

 

This should return 3 of the most related products. Can you please have a check and confirm it looks ok?

personaly...I think it looks a shambles, but it should work

[dr.pepper]

Actually, I think I seem to have done it now using the following query

 

SELECT `o`.*, `p`.*, COUNT(*) as num 
FROM `orders` AS `o` 
INNER JOIN `orders_products` AS `p` ON o.order_id = p.order_id 
INNER JOIN `orders_products` AS `p2` ON o.order_id = p2.order_id 
WHERE (p2.product_id = '020577') 
AND (p.product_id !='020577') 
GROUP BY `p`.`product_id` 
ORDER BY num DESC
LIMIT 3

 

This should return 3 of the most related products. Can you please have a check and confirm it looks ok?

personaly...I think it looks a shambles, but it should work

 

If you know a better or neater way then the advice would be really helpful? Thanks

[Muddy_Funster]

not using select * (especialy not from two tables where at least one field on each has the same value) and not using pointless, senseless shorthand aliases for table or field names.  fix that and it will be much better. 

[fenway]

not using select * (especialy not from two tables where at least one field on each has the same value) and not using pointless, senseless shorthand aliases for table or field names.  fix that and it will be much better. 

Um, no.

 

The reason not to use * is that you're using GROUP BY.  The only valid column to return is p2.product_uid, along with the COUNT().

 

And there's nothing wrong with short aliases -- that's the whole point.  Though, as suggested earlier, p and p2 really aren't descriptive enough.

 

And there's no harm is getting to count for the original product uid either.

 

So:

 

SELECT  `pr`.`product_id` , COUNT(*) as num 
FROM `orders` AS `o` 
INNER JOIN `orders_products` AS `po` ON ( o.order_id = po.order_id )
INNER JOIN `orders_products` AS `pr` ON ( o.order_id = pr.order_id ) 
WHERE po.product_id = '020577'
GROUP BY `pr`.`product_id` 
ORDER BY num DESC
LIMIT 3

[dr.pepper]

I have made the changes you suggested and I agree it is a lot neater and makes more sense.

 

Thanks for both your input