Thanks for the tips. I cleaned up all order items in zen_order_products to remove any references to older products. It turned out that there was actually no overlap in product IDs however. Digging in deeply, I did find that most of the also purchased items were actually "also purchased", but that it was the rare cases that were being triggered instead of the most common ones. Digging around in define_queries.php, it appears that the SQL is returning the most recent matches rather than the most popular ones:
Code:
DEFINE('SQL_ALSO_PURCHASED', "select p.products_id, p.products_image
from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
. TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
where opa.products_id = '%s'
and opa.orders_id = opb.orders_id
and opb.products_id != '%s'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
group by p.products_id
order by o.date_purchased desc
limit " . MAX_DISPLAY_ALSO_PURCHASED);
I modified this to do a match count and return the most popular matches first, which seems to work much better:
Code:
DEFINE('SQL_ALSO_PURCHASED', "select p.products_id, p.products_image, COUNT(p.products_id) num_matches
from " . TABLE_ORDERS_PRODUCTS . " opa, " . TABLE_ORDERS_PRODUCTS . " opb, "
. TABLE_ORDERS . " o, " . TABLE_PRODUCTS . " p
where opa.products_id = '%s'
and opa.orders_id = opb.orders_id
and opb.products_id != '%s'
and opb.products_id = p.products_id
and opb.orders_id = o.orders_id
and p.products_status = 1
group by p.products_id
order by num_matches desc
limit " . MAX_DISPLAY_ALSO_PURCHASED);
This works much better for me. Please consider it as a possible contribution/fix for future versions of ZC.
Bookmarks