what product do other customers have in there cart

Hello

i would need the sql statement to do something similar to the "what product other customers also bought", which refers to the Ordertable.
i would need "what product do other customers have in there cart (cart saved in a blob at customer-table.)

this SQL Statement does: if you want to know the product which a customer has also bought.

$sql = "
SELECT
DISTINCT
p.product_id
, p.tax_class_id
, pd.name AS name
, p.image
, p.price
, p.model
, m.name AS manufacturer
, ss.name AS stock
, (SELECT
AVG(r.rating)
FROM `" . DB_PREFIX . "review` r
WHERE p.product_id = r.product_id
GROUP BY r.product_id) AS rating
, COUNT(*) AS cnt
FROM `" . DB_PREFIX . "order_product` op
INNER JOIN `" . DB_PREFIX . "order` o
ON o.order_id = op.order_id
INNER JOIN `" . DB_PREFIX . "product` p
ON op.product_id = p.product_id
LEFT OUTER JOIN `" . DB_PREFIX . "product_description` pd
ON p.product_id = pd.product_id
LEFT OUTER JOIN `" . DB_PREFIX . "product_to_store` p2s
ON p.product_id = p2s.product_id
LEFT OUTER JOIN `" . DB_PREFIX . "manufacturer` m
ON p.manufacturer_id = m.manufacturer_id
LEFT OUTER JOIN `" . DB_PREFIX . "stock_status` ss
ON p.stock_status_id = ss.stock_status_id
WHERE o.customer_id IN (
SELECT
o.customer_id
FROM `" . DB_PREFIX . "order_product` op
INNER JOIN `" . DB_PREFIX . "order` o
ON o.order_id = op.order_id
WHERE op.product_id = '" . (int)$product_id . "'
GROUP BY o.customer_id)
AND p.product_id != '" . (int)$product_id . "'
AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "'
AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "'
AND p.date_available <= NOW()
AND p.status = '1'
GROUP BY op.product_id
ORDER BY COUNT(*) DESC
LIMIT 0, ".$max_row.";
";


How can i read that same from the cartcolumn in the customer table? how can i search though the cartblobfield?

best
ralf

This would be a little tricky. You'd have to run a query against the customers table to grab all carts and then from there you'd need to deserialize the data in order to be able to see which items each customer had. During deserialization I'd just create an array that is indexed by the product_id and perhaps set the value equal to the total quantity of that item that every had. Then you could sort that array and return your results back based on which cart products are currently the most popular.

For some code examples on deserialization, you can check to see how the login feature adds the blog data to the session. Just check out /system/library/cart.php and look at the login method:

wonderful. many thanks. i will check this and think that could work.

Done (VQMod): http://forum.opencart.com/viewtopic.php?f=123&t=50771