Display items based on ratings MVC

Question

I have a Product table with columns "ProductId(pk), P_Name, Price, Quantity"

and another table VoteLogs with columns "V_id(pk), ProductId, UserId, Vote"

I've implemented the rating feature by following this tutorial

The Vote Log table contains the following data

As you can see customer1@gmail.com and customer2@gmail.com both voted product 28 but customer2@gmail.com has also voted product 20, 72, 1187, and 1188

Now when customer1@gmail.com log in, I want to display him product 20, 72, 1187, 1188 because customer1 and customer2 both voted the same product so they might have similar taste.

I have tried and came this far,

public ActionResult BasedOnRating()
    {
        string UserId = User.Identity.GetUserName(); //logged in with customer1@gmail.com
        var query = from vv in db.VoteLogs
                    join pp in db.Products
                    on vv.ProductId equals pp.ProductId
                    where !(db.VoteLogs.Where(c => c.UserName == UserId)
                           .Select(c => c.Product.ProductId).ToList())
                           .Contains(vv.Product.ProductId) && (vv.UserName == "customer2@gmail.com")
                    select pp;

        return View(query);
    }

and I get the desired result:

But I don't want to write customer2@gmail.com in the query, What If i have 1000+ users. I guess here I should pass the list of users who has voted the same products. I'm confused here. I would appreciate any help. Thanks

Answer 1

To break this down into its individual components

First you need to get the ID's of the products the current user has voted on

string UserId = User.Identity.GetUserName();
var userProducts = db.VoteLogs.Where(v => v.UserName == userId).Select(v => v.ProductId);

Then get all the other users who have also voted for one of those products

var likeUsers = db.VoteLogs.Where(v => userProducts.Contains(v.ProductId) && v.UserName != userId).Select(v => v.UserName);

Then get all other products that those users have voted on

var likeProducts = db.VoteLogs.Where(v => likeUsers.Contains(v.UserName)).Select(v => v.ProductId).Except(userProducts);

And finally return those products

return View(db.Products.Where(p => likeProducts.Contains(p.ID)));

Based on the table you have shown, this will return products 20, 72, 1187 and 1188. If you were to add another row to your votes table with ProductId = 109 and UserName = "customer1@gmail.com" then the results will be 19, 20, 30, 72, 1111, 1187 and 1188.

Note that since you have potentially thousands of users and products, this could return thousands of products to the view, so you may want to consider limiting the results, for example you might order the likeProducts query by Vote (highest) and take only the top #.