Sequelize get most sold products / "most apperance of relation"

Question

I got similar problem to what described here: https://dba.stackexchange.com/questions/52476/how-to-get-a-list-result-of-best-selling-items

My order_items table looks like:

orderID	productID	quantity
1	5	7
3	4	8
1	2	3
and	so	on

I am trying to get the most sold product using Sequelize, I define the relations between 'products' table and 'order_items' table with sequelize.

And also get the most refered to product (the one who appear the most times, regardless of quantity).

score 1 · Answer 1 · answered Aug 31 '21 at 15:09

1

To get the max of a column you can use:

const mostSold = await order_items.findOne({
  attributes: [[sequelize.fn('max', sequelize.col('quantity')), 'quantity']]
}).getProduct();

To get the most occurring you can use:

const mostOccurring = await order_items.findOne({
  attributes: [[sequelize.fn('count', sequelize.col('productID')), 'productId']]
}).getProduct();

answered Aug 31 '21 at 15:09

r9119

586
2
8

Thanks, the count is great for the second option. But as for the first, I am trying to get the item that sold total the most. So if item 1 was ordered 4 times and total quanity of 18. I can use the raw query with the SQL of the linked question but isn't there a way to do it with sequlize? – eliezra236 Aug 31 '21 at 17:00
You're welcome about the count part, been a little busy irl with stuff so I didn't reply earlier and anyway it looks like you've figured it out in the answer you posted :) – r9119 Sep 03 '21 at 07:57

score 1 · Accepted Answer · answered Sep 01 '21 at 08:38

Thanks to Finding sum and grouping in sequelize for calling functions as attirubte I made some modification it include product ref, order and limit

For most refered products (unique sells):

async function getUniqueBestSelling(limit: number) {
  const res = await database.model("order_items").findAll({
    attributes: [
      "productId",
      [sequelize.fn("count", sequelize.col("productId")), "totalOrders"],
    ],
    group: ["productId"],
    include: [{ model: database.model("products") }],
    order: [[sequelize.col("totalOrders"), "DESC"]],
    limit: limit,
  });
  const plainRes = getPlainRes(res);

  return plainRes;
}

For total sales (quantity)

async function getBestSelling(limit: number) {
  const res = await database.model("order_items").findAll({
    attributes: [
      "productId",
      [sequelize.fn("sum", sequelize.col("quantity")), "totalQuantity"],
    ],
    group: ["productId"],
    include: [{ model: database.model("products") }],
    order: [[sequelize.col("totalQuantity"), "DESC"]],
    limit: limit,
  });
  const plainRes = getPlainRes(res);

  return plainRes;
}

Sequelize get most sold products / "most apperance of relation"

2 Answers2