select top products in orders table-CodePudding

I have two tables:

product(id, reference, name)  
order(id, productId, date, quantity)

Every order has one or many products. The order table can have many lines of the same product. I need to select the 5 best seller products for today in the order table. I try this join to select the products of every order on today.

from order in orders where order.date == DateTime.Today
join product in products on product.Id equals order.productId
select new {product.name, product.quantity, product.Id};

Now I have a list of products sold on today, which can have multiple lines of the same products. So I tried this to add the quantity of the repeated product:

for (int i = 1; i <= productList.ToArray().Count(); i  )
{
    foreach (var product in productList)
    {
        if (productList.Contains(product))
            quantite  = product.Quantite;
        else
            quantite = product.Quantite;

I didn't find a solution how to get the top 5 articles!

CodePudding user response：

Try following :

var results = (from order in orders on order.date equals DateTime.Today
   join product in products on product.Id equals order.productId
   select new {name = product.name, quantity = order.quantity, id = product.Id})
   .GroupBy(x => x.id)
   .Select(x => new { name = x.First().name, id = x.Key, totalQuantity = x.Sum(y => y.quantity)}
   .OrderByDescending(x => x.totalQuantity)
   .Take(5)
   .ToList();

CodePudding user response：

You may run this simple query as native and receive the result.

select * from 
(
  select p.id, p.name, sum(o.quantity) qty 
  from "order" as o 
  inner join product as p on o.productid = p.id
  where o.date = current_date
  group by p.id -- and p.name if p.id is not primary key
) as t
order by qty desc
limit 5;