Developer forum

Hi Tin

I Guess that you use this loop:

eCom:Related.CustomersWhoBoughtThisItemAlsoBought

Quantity is not taken into consideration - just the product id, also disregarding any variants.

This is the SQL that it builds:

            sql.Append("SELECT DISTINCT TOP 10 * ");
            sql.Append("FROM EcomProducts ");
            sql.Append("INNER JOIN ");
            sql.Append("(");
            sql.Append("    SELECT DISTINCT(E2.OrderLineProductID), ");
            sql.Append("    (");
            sql.Append("        SELECT COUNT(*) ");
            sql.Append("        FROM EcomOrderLines E3 ");
            sql.Append("        WHERE E3.OrderLineProductID = E2.OrderLineProductID ");
            sql.Append("        AND E3.OrderLineOrderID IN ");
            sql.Append("        (");
            sql.Append("            SELECT DISTINCT(OrderLineOrderID) ");
            sql.Append("            FROM EcomOrderLines E4 ");
            sql.Append("            WHERE E4.OrderLineProductID = '{0}'");
            sql.Append("        )");
            sql.Append("    ) AS Frequency ");
            sql.Append("    FROM EcomOrderLines AS E1 ");
            sql.Append("    INNER JOIN EcomOrderLines AS E2 ");
            sql.Append("    ON E1.OrderLineOrderID = E2.OrderLineOrderID ");
            sql.Append("    INNER JOIN EcomOrders AS O ");
            sql.Append("    ON E1.OrderLineOrderID = O.OrderID ");
            sql.Append("    WHERE E1.OrderLineProductID = '{0}' ");
            sql.Append("    AND E2.OrderLineProductID <> '{0}' ");
            sql.Append("    AND O.OrderComplete = {1} ");
            sql.Append(") AS SelectorTable ");
            sql.Append("ON EcomProducts.ProductID = SelectorTable.OrderLineProductID ");
            sql.Append("WHERE EcomProducts.ProductVariantID IS NULL OR EcomProducts.ProductVariantID = '' ");

            if (!string.IsNullOrEmpty(eCommerce.Common.Context.LanguageID))
                sql.AppendFormat("AND EcomProducts.ProductLanguageID = '{0}' ", Database.SqlEscapeInjection(eCommerce.Common.Context.LanguageID));

            sql.Append("ORDER BY Frequency DESC");