【EC-CUBE2.13】レコメンド機能の「この商品を買った人はこんな商品も買っています」機能を実装するための商品集計SQL文

LC_Page_Products_Detail_Ex.php

<?php
/**
 * この商品を買った人はこんな商品も買っています。
 * @param int $product_id 商品ID
 * @param int $interval 集計期間（ヶ月）
 * @param int $limit 表示件数
 * @return type
 */
function lfGetRecommendProducts($product_id, $interval = 6, $limit = 10)
{
	$objQuery = & SC_Query_Ex::getSingletonInstance();

	$sql = <<< __EOS__
		SELECT
			T1.product_id
			,T1.name
			,T1.main_list_image
			,T2.product_code_min
			,T2.product_code_max
			,T2.price01_min
			,T2.price01_max
			,T2.price02_min
			,T2.price02_max
			,T2.stock_min
			,T2.stock_max
			,T2.stock_unlimited_min
			,T2.stock_unlimited_max
			,T2.point_rate
			,T2.deliv_fee
			,T2.class_count
		FROM dtb_products AS T1
		INNER JOIN (
			SELECT
				product_id,
				MIN(product_code) AS product_code_min,
				MAX(product_code) AS product_code_max,
				MIN(price01) AS price01_min,
				MAX(price01) AS price01_max,
				MIN(price02) AS price02_min,
				MAX(price02) AS price02_max,
				MIN(stock) AS stock_min,
				MAX(stock) AS stock_max,
				MIN(stock_unlimited) AS stock_unlimited_min,
				MAX(stock_unlimited) AS stock_unlimited_max,
				MAX(point_rate) AS point_rate,
				MAX(deliv_fee) AS deliv_fee,
				COUNT(*) as class_count
			FROM dtb_products_class
			WHERE 
				del_flg = 0 AND 
				(stock > 0 OR stock_unlimited = 1)
			GROUP BY 
				product_id
		) AS T2
		ON T1.product_id = T2.product_id
		INNER JOIN(
			SELECT
				T4.product_id,
				COUNT(T4.product_id) AS count_num
			FROM
				dtb_order_detail AS T4
			INNER JOIN dtb_order AS T5
			ON
				T5.order_id = T4.order_id AND 
				T5.commit_date >= NOW() - INTERVAL ? MONTH
			WHERE EXISTS (
				SELECT 
					T6.order_id
				FROM
					dtb_order_detail AS T6
				WHERE 
					T6.order_id = T4.order_id AND
					T6.product_id = ?
			) AND
			T4.product_id != ? 
			GROUP BY T4.product_id
		) AS T3
		ON T1.product_id = T3.product_id
		WHERE
			T1.del_flg = 0 AND
			T1.status = 1
		ORDER BY
			T3.count_num DESC
		LIMIT ?
__EOS__;

	$arrProducts = $objQuery->getAll($sql, array($interval, $product_id, $product_id, $limit));

	// 税込金額を設定する
	SC_Product_Ex::setIncTaxToProducts($arrProducts);

	return $arrProducts;
}