SELECT
brand.brand_id AS brand_id,
brand.short_name AS brand,
brand.url_stub AS brand_url_stub,
product.product_name AS product_name,
product.url_stub,
GROUP_CONCAT(product.product_id) AS product_ids,
SUM(IF(product.gender='u',1,0)) AS has_unisex_version,
SUM(IF(product.gender='m',1,0)) AS has_mens_version,
SUM(IF(product.gender='w',1,0)) AS has_womens_version,
SUM(IF(product.gender IN('k','b','g'),1,0)) AS has_kids_version,
IF(SUM(IF(product.gender IN(NULL,'','u','m','w'),1,0))=0,1,0) AS kids_only,
UNIX_TIMESTAMP(product.date_available) AS date_available,
pt2.producttype_id AS producttype_id,
pt2.singular_name AS category_singular_name,
# IF(pt2.plural_name = '',CONCAT(pt2.singular_name,'s'),pt2.plural_name) AS category_plural_name,
product_totals.overall_num_reviews AS num_reviews,
product_totals.overall_average_rating AS average_rating,
product_totals.overall_weighted_rating AS weighted_rating,
IF(product_totals.overall_average_rating>0,1,0) AS has_rating,
IF(MIN(product_totals.price_retail_low) IS NOT NULL,MIN(product_totals.price_retail_low),product.price_msrp)+0.0 AS price_low,
IF(MAX(product_totals.price_retail_high) IS NOT NULL,MAX(product_totals.price_retail_high),product.price_msrp)+0.0 AS price_high,
IF(product.price_msrp >0,1,IF(MAX(product_totals.price_retail_high)>0,1,0)) AS has_price,
SUM(product_totals.num_retailers) AS num_retailers,
IF(SUM(product_totals.num_retailers)>0,1,0) AS has_retailers,
# sorting
(
# 4 points for having retailers
IF(SUM(product_totals.num_retailers)>0,4,0)
# 2 points for having reviews
+ IF(product_totals.overall_num_reviews>0,2,0)
# 1 point for having an adult version
+ IF(SUM(IF(product.gender IN(NULL,'','u','m','w'),1,0))>0,1,0)
# products new w/in ~6 months get additional boosts
+ IF(
UNIX_TIMESTAMP(product.date_available) >= UNIX_TIMESTAMP('2008-10-07') - 6*30*24*60*60 || (product.source = 'manual' && UNIX_TIMESTAMP(product.date_added) >= UNIX_TIMESTAMP('2008-10-07') - 6*30*24*60*60),
(
# 1 point for lack of reviews
IF(product_totals.overall_num_reviews>0,0,1)
# 2 points for lack of retailers
+ IF(SUM(product_totals.num_retailers)>0,0,2)
),
0
)
) AS rough_default_sort,
(
# 1 to 5 multiplier for weighted rating
(
(
IF(product_totals.overall_weighted_rating>1,
product_totals.overall_weighted_rating
,3
)
)
) # AS rating_score,
# 1 to 5 multiplier for recency
# products new w/in ~6 months get additional boosts
+ (
(
IF(
UNIX_TIMESTAMP(product.date_available) >= UNIX_TIMESTAMP('2008-10-07') - 6*30*24*60*60,
(((6*30*24*60*60)-(ABS(UNIX_TIMESTAMP(product.date_available)-UNIX_TIMESTAMP('2008-10-07'))))/(6*30*24*60*60)*5),
0
)
)
) # AS recency_score,
# 1 to 5 multiplier for multiple retailers
+ (
(
IF(
SUM(product_totals.num_retailers)>=4,
4,
IF(
SUM(product_totals.num_retailers) IS NULL,
0,
SUM(product_totals.num_retailers)
)
)+1
)
) # AS retailer_score
) AS fine_default_sort
# IF(MAX(product.thumb_url) != '',CONCAT('http://www.trailspace.com/images/gear/',MAX(product.thumb_url)),MAX(product_totals.retailer_thumb_url)) AS product_thumb_url
FROM
producttype AS pt1,
producttype AS pt2,
product
# optimization is flakey here; brand joins much more quickly as a left join
LEFT JOIN
brand
ON
brand.brand_id = product.brand_id
LEFT JOIN
product_totals
ON
product_totals.product_id = product.product_id
WHERE
pt1.producttype_id = '186'
AND pt2.lft BETWEEN pt1.lft AND pt1.rgt
AND pt2.visibility = 'visible'
AND product.producttype_id = pt2.producttype_id
AND product.brand_id = '336'
GROUP BY
brand,
product_name,
product.producttype_id
HAVING
brand IS NOT NULL
ORDER BY
rough_default_sort DESC,
# has_retailers DESC,
# has_rating DESC,
# kids_only ASC,
fine_default_sort DESC,
# weighted_rating DESC,
num_reviews DESC,
MD5(product_name)
# brand ASC,
# product_name ASC
Performance Pants and Tights from Horny Toad - Trailspace.com
