For this, you need an additional join with the orders
table, which contains the created_on field. The following query calculates product recommendations
based on orders placed in the past 30 days:
SELECT od2.product_id, od2.product_name
FROM order_detail od1
JOIN order_detail od2 ON od1.order_id = od2.order_id
JOIN orders o ON od1.order_id = o.order_id
WHERE od1.product_id = 4 AND od2.product_id != 4
AND DATE_SUB(NOW(), INTERVAL 30 DAY) < o.created_on
CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 467
GROUP BY od2.product_id
ORDER BY COUNT(od2.product_id) DESC
LIMIT 5;
?– Note We won??™t use this trick at this time in TShirtShop, but it??™s important to keep it in mind. Over time, as
you sell more and more via your site, you will need to limit the number of orders that are used to calculate
the recommendations??”otherwise, your recommendations will not only be dated but will be calculated on
ever larger sets of data. This situation reduces the effectiveness of the recommendations and takes a toll on
the site??™s performance. After all, do we really want to recommend Christmas t-shirts in the month of May?
And how will your shoppers feel about having had to wait while your server crunches on outdated recommendations?
It??™s a good bet that they will begin to ignore your recommendations altogether if they are too
often irrelevant. If you decide to use this technique, adding an index on the created_on field would increase
the query performance.
Pages:
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587