Order Only Nested Model Data Sequelize

Question

I am trying to order only the nested include Scheme Model based on an integer column but it seems like the code is not working. Also, the data is being fetched from MYSQL bottom to up and shown as output.

sample code:

[err, products] = await to(
Product_Inventory_Mapping.findAll({
  include: [
    {
      model: Product,
      as: "product_details",
      where: filterClause,
      include: [
        {
          model: Unit,
          as: "unit_details"
        },
        {
          model: Brand,
          as: "brand_details"
        },
        {
          model: Category,
          as: "category_details"
        },
        {
          model: Sub_Brand,
          as: "sub_brand_details"
        },
        {
          model: Sub_Category,
          as: "sub_category_details"
        },
        {
          model: Brand_Company,
          as: "brand_company_details"
        },
        {
          model: Scheme,
          as: "schemes",
          attributes: [`moq`, 'discount', `description`],
          order: [[{ model: Scheme, as: "schemes" }, "moq", "ASC"]],
          where: {
            status: 1
          },
          required: false
        }
      ]
    }
  ],
  order: [
    [{ model: Product, as: "product_details" }, "mrp", "ASC"]
  ],
  where: {
    user_id: aligned_distributors,
    brand_company_id: filtered_brand_company,
    count: {
      [Op.gt]: 0
    },
    sp: {
      [Op.gt]: 0
    }
  },
  attributes: [
    `product_id`,
    "user_id",
    "count",
    "sp",
    "occ",
    "brand_company_id"
  ],
  limit: limit,
  offset: offset,
  subQuery: false
}));

Here I want to order only the data inside the Scheme Model. Sample response:

"products": [
    {
        "product_id": 115,
        "user_id": 1003,
        "count": 50,
        "sp": 9.09,
        "occ": 9.09,
        "brand_company_id": 11,
        "product_details": {
            "id": 115,
            "category_id": 2,
            "sub_category_id": 12,
            "brand_company_id": 11,
            "brand_id": 24,
            "sub_brand_id": 44,
            "sku_code": null,
            "min_order_qty": "27",
            "description": "Nandini Butter Milk Tetra Pack, 200 Ml",
            "unit_id": 4,
            "weight": "200ml",
            "mrp": "10.00",
            "barcode": "",
            "image_url": "https://xxxxxx.s3.ap-south-1.amazonaws.com/products/1565919229Unknown-min-5.jpg",
            "created_at": "2019-08-12T13:44:01.000Z",
            "updated_at": "2019-08-15T20:03:49.000Z",
            "unit_details": {
                "id": 4,
                "name": "Pieces",
                "created_at": null,
                "updated_at": null
            },
            "brand_details": {
                "id": 24,
                "name": "Nandini Butter Milk",
                "brand_company_id": 11,
                "created_at": null,
                "updated_at": null
            },
            "category_details": {
                "id": 2,
                "name": "Packaged Food",
                "image_url": "https://zzzz.s3.ap-south-1.amazonaws.com/images/category/food-min.png",
                "created_at": "2019-08-11T11:36:52.000Z",
                "updated_at": "2019-08-11T11:36:52.000Z"
            },
            "sub_brand_details": {
                "id": 44,
                "name": "Nandini Butter Milk",
                "brand_id": 24,
                "created_at": null,
                "updated_at": null
            },
            "sub_category_details": {
                "id": 12,
                "name": "Dairy products (Butter, Cheese etc)",
                "category_id": 2,
                "created_at": null,
                "updated_at": null
            },
            "brand_company_details": {
                "id": 11,
                "name": "Karnataka Co-operative Milk Producers",
                "image_url": "https://xxxxx.s3.ap-south-1.amazonaws.com/images/company/kmf_logo.jpg",
                "created_at": "2019-08-12T13:10:18.000Z",
                "updated_at": "2019-08-12T13:10:18.000Z"
            },
            "schemes": [
                {
                    "moq": 30,
                    "discount": 1.5,
                    "description": "8 % offer"
                },
                {
                    "moq": 20,
                    "discount": 1,
                    "description": "20 % offer"
                },
                {
                    "moq": 40,
                    "discount": 2,
                    "description": "40 % offer"
                }
            ]
        }
    }]

In the database, the Scheme data is saved as:

Scheme belongs to the Product via product_id. I want the Scheme array to be sorted by moq ASC so that the value comes in order of moq: 20 > 30 > 40.

Answer 1

AFAIK, an order clause in the included model doesn't work. But, you can make a reference to the included model in the main model. I think this should work if you combine your two order clauses into one:

Product_Inventory_Mapping.findAll({
include: [
  ..... lots of includes here ....
],      
order: [
        [{ model: Product, as: "product_details" }, "mrp", "ASC"],
        [{ model: Scheme, as: "schemes" }, "moq", "ASC"]]
      ],

This will order by moq within mrp... you could remove the first field if you really want to sort ONLY by moq....