CODEWITHSUNDEEP
c#asp.netasp.net-mvcasp.net-mvc-4rating
user3223395667
user3223395667

Reputation: 239

Display items based on ratings MVC

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

VoteLog Table Data

As you can see [email protected] and [email protected] both voted product 28 but [email protected] has also voted product 20, 72, 1187, and 1188

Now when [email protected] 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 [email protected]
        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 == "[email protected]")
                    select pp;

        return View(query);
    }

and I get the desired result:

Result of query

But I don't want to write [email protected] 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

Upvotes: 2

Views: 881

Answers (3)

Sahi
Sahi

Reputation: 1484

you can use same result set. no need of converting to list again.

Linq query is given below:

    var query = (from vv in vt
                         join pp in pd
                             on vv.pdid equals pp.pdid
                         join vvv in vt
                             on vv.pdid equals vvv.pdid
                         where !(vt.Where(c => c.cust == UserId)
                             .Select(c => c.pdid).ToList())
                             .Contains(vv.pdid) && (vv.cust == vvv.cust)
                         select new
                         {
                             pdid = vv.pdid,
                             pdname = pp.pdname
                         }).Distinct().ToList();

Added ToList().

you can pass query in your view.

Upvotes: 1

user3559349
user3559349

Reputation:

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 = "[email protected]" 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 #.

Upvotes: 1

Sahi
Sahi

Reputation: 1484

Please refer below code.

you can change the userId and verify the output.

    public class Products
    {
        public int pdid { get; set; }
        public string pdname { get; set; }
    }
   public class Votes
    {
        public int pdid { get; set; }
        public string cust { get; set; }
        public int vt { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            List<Products> pd = new List<Products>()
            {
                new Products{pdid=1, pdname="abc"},
                new Products{pdid=2, pdname="ghi"},
                new Products{pdid=3, pdname="mnp"},
                new Products{pdid=4, pdname="pqr"},
                new Products{pdid=5, pdname="xyz"}

            };

            List<Votes> vt = new List<Votes>()
            {
                new Votes{pdid=1,cust="c1",vt=2},
                new Votes{pdid=2,cust="c1",vt=2},

                new Votes{pdid=1,cust="c2",vt=2},
                new Votes{pdid=3,cust="c2",vt=2},
                new Votes{pdid=4,cust="c2",vt=2},

                new Votes{pdid=2,cust="c3",vt=2},
                new Votes{pdid=5,cust="c3",vt=2}
            };

            var UserId = "c3";

            var query = (from vv in vt
                         join pp in pd
                             on vv.pdid equals pp.pdid
                         join vvv in vt
                             on vv.pdid equals vvv.pdid
                         where !(vt.Where(c => c.cust == UserId)
                             .Select(c => c.pdid).ToList())
                             .Contains(vv.pdid) && (vv.cust == vvv.cust)
                         select new
                         {
                             pdid = vv.pdid,
                             pdname = pp.pdname
                         }).Distinct();

            foreach (var p in query)
            {
                Console.WriteLine(p.pdid + "-" + p.pdname);
            }

            Console.ReadLine();
        }
    }

Upvotes: 1

Related Questions