..')) AS description
FROM order_detail od1
JOIN order_detail od2
ON od1.order_id = od2.order_id
JOIN product p
ON od1.product_id = p.product_id
JOIN shopping_cart
ON od2.product_id = shopping_cart.product_id
WHERE shopping_cart.cart_id = ?
-- Must not include products that already exist
-- in the visitor's cart
AND od1.product_id NOT IN
(-- Returns the products in the specified
-- shopping cart
SELECT product_id
FROM shopping_cart
WHERE cart_id = ?)
-- Group the product_id so we can calculate the rank
GROUP BY od1.product_id
-- Order descending by rank
CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 469
ORDER BY COUNT(od1.product_id) DESC
LIMIT 5";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inCartId;
EXECUTE statement USING @p1, @p1, @p2, @p2;
END$$
How It Works: Getting Product Recommendations from the Database
The stored procedures you??™ve just written simply apply the techniques you??™ve learned in the first part of the chapter.
In other editions of this book (using PostgreSQL and SQL Server), at this point, we also presented alternative
implementations of these stored procedures that used subqueries instead of table joins to obtain the necessary
data. In MySQL, using subqueries is not possible because of some limitations of using LIMIT, which are documented
at http://dev.mysql.com/doc/refman/5.1/en/subquery-errors.
Pages:
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590