Implementing Customers Also Bought In ASP.NET Web Pages

Most successful e-commerce sites implement a mechanism that draws the customer's attention to other products that might be of interest to them. These products are selected on the basis that shoppers who purchased the item currently being viewed by the customer also bought other products. This article looks at a away of implementing this kind of facility in an ASP.NET Web Pages site.

The key to implementing this feature is the way in which you store the details of orders. The Northwind sample database provides an excellent example that shows how to do this. Orders are stored in the Orders table, which links an order with a customer. The details of the order are stored in a separate table - one row per order item - called OrderDetails:

Order Details Table

Each order item is related to its parent order via the OrderId value, and each the product that is ordered is related to its entry in the Products table via the ProductId value. Note that the UnitPrice is also stored in the OrderDetails table even though it is already present in the Products table. Why, then, should it be stored with the order line? Well, if you increase the price in the Products table, you do not want the value of historical orders to be affected, so you store the price that the item was sold for at the time it was sold as part of the order line entry.

Now that you are storing order details in this way, it is possible to obtain all orders for a specific product via SQL:

SELECT OrderID From OrderDetails WHERE ProductID = 1

And it is also possible to obtain a list of all other products that featured in those orders:

SELECT DISTINCT ProductName FROM OrderDetails 
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE OrderID IN (
    SELECT OrderID FROM OrderDetails WHERE ProductID = 1
) 
AND OrderDetails.ProductID <> 1

But what you really want is the products that featured most often in those orders. So instead of using DISTINCT to weed out duplicate values, you count the number of times each product appears and use that value to order the result, then take the first 5:

SELECT TOP 5 ProductName, COUNT(ProductName) AS CountOfProducts FROM OrderDetails 
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE OrderID IN (
    SELECT OrderID FROM OrderDetails WHERE ProductID = 1
) 
AND OrderDetails.ProductID <> 1 
GROUP BY ProductName ORDER BY CountOfProducts DESC

Here's how it all goes together in a sample app to illustrate how this works. The sample application presents all the product categories in a dropdown list.

View categories

When the user selects a category, an AJAX request retrieves all the products within that category which are returned in a grid.

Grid

Each product features its own button. When it is clicked, the user is presented with the 5 most popular other products that featured in orders that included the selected product:

also bought

First, the _Layout,cshtml file:

@{
    
}

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>@Page.Title</title>
        <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />
        <script src="~/Scripts/jquery-1.9.1.min.js" type="text/javascript"></script>
        @RenderSection("script", false)
    </head>
    <body>
        <div id="wrapper">
            @RenderBody()
        </div>
    </body>
</html>

Note that it references jQuery. This is used to manage the AJAX requests and to update the DOM with the response. Here's the main page (Default.cshtml):

@{
    Page.Title = "Home";
    var db = Database.Open("Northwind");
    var data = db.Query("SELECT CategoryID, CategoryName FROM Categories");
    var categories = data.Select( c => new SelectListItem {
            Value = c.CategoryId.ToString(),
            Text = c.CategoryName
        });
}

<div id="selector">
    @Html.Label("Select Category: ", "categoryId")
    @Html.DropDownList("categoryId", "Choose Category", categories)
</div>
<div id="grid"></div>
<div id="alsoBought"></div>
@section script{
    <script>
        $(function () {
            $('#categoryId').change(function () {
                $('#alsoBought').empty();
                $('#grid').load('/Products/' + $(this).val());
            });
            $(document).on('click', '.alsoBought', function () {
                $('#alsoBought').load('AlsoBought/' + $(this).attr('id'));
            })
        });
    </script>
}

The dropdown is populated when the page loads. An event handler is registered which fires when the dropdown's selection changes. It clears the "alsoBought" div of any content and loads the "grid" div with the response from Products.cshtml:

@{
    Layout = null;
    var categoryId = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
    var db = Database.Open("Northwind");
    var commandText = @"SELECT ProductId, ProductName, QuantityPerUnit, UnitPrice FROM Products 
                        WHERE CategoryID = @0";
    var data = db.Query(commandText, categoryId);
    var grid = new WebGrid(data, canPage: false, canSort: false);
}

@grid.GetHtml(tableStyle : "table",
    columns: grid.Columns(
                grid.Column("ProductName", "Product"),
                grid.Column("QuantityPerUnit", "Per Unit"),
                grid.Column("UnitPrice", "Unit Price"),
                grid.Column("", format: @<button class="alsoBought" id="@item.ProductId">Also bought?</button>)
            )
)

This file is responsible solely for generating an HTML table populated with the product details. Therefore the Layout property at the top of the file is set to null to prevent any other HTML from getting in the way. A default value of 1 is used for the Category ID value to prevent errors if the page is requested without a valid value being passed in the URL. Event handlers are wired up the last column's buttons in the home page. They fire when the buttons are clicked. They request AlsoBought.cshtml, passing in the product ID as a UrlData value:

@{
    Layout = null;
    var productId = UrlData[0].IsInt() ? UrlData[0].AsInt() : 1;
    var db = Database.Open("Northwind");
    var commandText = @"SELECT TOP 5 ProductName, COUNT(ProductName) AS CountOfProducts FROM OrderDetails 
                        INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
                        WHERE OrderID IN (
                            SELECT OrderID FROM OrderDetails WHERE ProductID = @0
                        ) 
                        AND OrderDetails.ProductID <> @0 
                        GROUP BY ProductName ORDER BY CountOfProducts DESC";
    var products = db.Query(commandText, productId);
}

<h3>Customers who bought this item also bought</h3>
@foreach(var product in products){
    <div>@product.ProductName</div>
}

AlsoBought.cshtml has its layout property set to null to prevent any stray HTML from interfering with the response. It also features the same default value for the product ID value as the Products.cshtml page as a precaution. Then, using the SQL that was discussed earlier, the 5 most popular products are obtained and then rendered. The response is displayed in the "alsoBought" div by jQuery.

There is no reason why you couldn't extend this pattern to cover other activities, such as blog articles read for example. You can store the articles read by a visitor during a single session in a similar way to order lines and apply the same logic to retrieving those that seem related to the current one.

The sample application is available as a download from GitHub.

Date Posted:
Last Updated:
Posted by:
Total Views to date: 16886

5 Comments

- yousaid

GREAT article !! This is what I was looking for.
Thanks again Mike !

- yousaid

I think there's a typo in this line
car commandText = @"SELECT ProductId, ProductName, QuantityPerUnit, UnitPrice FROM Products .
IT should be:
var commandText = @"SELECT ProductId, ProductName, QuantityPerUnit, UnitPrice FROM Products

NOT "car"

- Mike

@Yousaid,

Well spotted!

- Quintus

Mike, several of the images in this post are not being found and returning 404

- Mike

@Quintus,

Thanks - I was in the middle of moving my hosting. Some of the images were a bit obstinate.

Recent Comments

Justin Kusuma 7/24/2015 3:38 AM
In response to Posting Data With jQuery AJAX In ASP.NET Razor Web Pages
Hi Mike, thanks much for sharing such an article :) Really help me a lot... further, I'd like to...

Michael Easterbrook 7/22/2015 5:35 PM
In response to Inline Razor Syntax Overview
I removed the @ symbols and I am still getting the same error. It only occurs when I have an "if" a...

Sujay 7/22/2015 1:36 PM
In response to ASP.NET MVC, Entity Framework, One-to-Many and Many-to-Many INSERTS
can you explain how to link two tables so that it forms many to many relationship?(Article and...

Max G 7/21/2015 9:29 PM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi, I've opted for this solution in one of my applications but i've found that the apppool is and I...

Michael Easterbrook 7/20/2015 4:31 PM
In response to Inline Razor Syntax Overview
When I have the following code: @foreach (var procRow in procRowDecade) { if (@procRow[3] +...

Shanice 7/18/2015 10:58 PM
In response to A Better Way To Export Gridviews To Excel
Hi. I'm working with mvc. I need to add the above code in the business logic layer, however the...

Matt 7/18/2015 6:29 PM
In response to Nested Layout Pages with Razor
Cheers sir, nice explanation :)...

Keshavan 7/17/2015 9:06 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, I have followed exactly as illustrated in blog, I get error "StdSchedulerFactory.cs" not...

Paul Thiel 7/16/2015 5:17 PM
In response to ASP.NET 5 By Numbers
Comments Below: "The new version of ASP.NET is called ASP.NET 5. It is a framework for developing...

saket singh 7/16/2015 8:42 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
hi Mike, great tutorial on Quartz.net , but i have One Problem , Everything is working fine as as...