I want to expand UI on my CodeIgniter shop with suggestions on what other people bought with the current product (either when viewing product or when product is put in the cart, irrelevant now for the question).
I have came up with this query (orders table contains order details, while order items contains products that are in specific order via foreign key, prd alias is for products table where all important info about prduct is stored).
Query looks like this
COUNT(*) AS num,
orders AS o
INNER JOIN order_items AS po ON o.id = po.order_id
INNER JOIN order_items AS pr ON o.id = pr.order_id
INNER JOIN products AS prd ON pr.product_id = prd.id
po.product_id = '14211'
AND pr.product_id <> '14211'
SELECT p.num, prd.* FROM ( SELECT a.product_id, COUNT(*) AS num FROM orders AS o INNER JOIN order_items AS b ON o.id = b.order_id INNER JOIN order_items AS a ON o.id = a.order_id WHERE b.product_id = '14211' AND a.product_id <> '14211' GROUP BY a.product_id ORDER BY num DESC LIMIT 3 ) AS p JOIN products AS prd ON p.product_id = prd.id ORDER BY p.num DESC
Ignore the complaint about
GROUP BY and
ORDER BY coming from different tables -- that is a performance issue; you need it.
As for translating that back to CodeIgniter, good luck.