Can't use attributes with sequelize.fn inside include block of findAll query

Question

I'm trying to use sum for the data of my relational data model which mean i have billingsummary as a parent model and billingdetails as child model and both are linked with association of many to one relation. I basically did the query as

const billing = await models.billingsummary.findAll({
    attributes: ["doctorId"],
    include: [
      {
        model: models.billingdetails,
        as: "bills",
        attributes: ["itemAmount"],
      },
    ],
  });

And this is working and returning the data as i wanted

{
    "status": 200,
    "result": [
        {
            "doctorId": "3",
            "bills": [
                {
                    "itemAmount": "100"
                },
                {
                    "itemAmount": "100"
                }
            ]
        }
    ],
    "message": "Successfully fetched"
}

but when i add some sequelize.fn with attributes

const billing = await models.billingsummary.findAll({
    where: {
      createdAt: {
        [Op.gte]: moment(dateFrom?.toString()).toDate(),
        [Op.lte]: moment(dateTo?.toString()).add(24, "hours").toDate(),
      },
    },
    attributes: ["doctorId"],
    include: [
      {
        model: models.billingdetails,
        as: "bills",
        attributes: [
          [
            sequelize.fn("date_trunc", `day`, sequelize.col("createdAt")),
            "date",
          ],
          [
            sequelize.fn(
              "SUM",
              sequelize.cast(sequelize.col("itemDiscount"), "integer")
            ),
            "NetDiscount",
          ],
        ],
        group: "date",
      },
    ],
  });

When i add this at attributes it throws me an error like

{
    "status": 500,
    "error": {
        "name": "SequelizeDatabaseError",
        "parent": {
            "length": 115,
            "name": "error",
            "severity": "ERROR",
            "code": "42702",
            "position": "106",
            "file": "parse_relation.c",
            "line": "801",
            "routine": "scanRTEForColumn",
            "sql": "SELECT \"billingsummary\".\"id\", \"billingsummary\".\"doctorId\", \"bills\".\"id\" AS \"bills.id\", date_trunc('day', \"createdAt\") AS \"bills.date\", SUM(CAST(\"itemDiscount\" AS INTEGER)) AS \"bills.NetDiscount\", SUM(CAST(\"itemCredit\" AS INTEGER)) AS \"bills.NetCredit\", SUM(CAST(\"tax\" AS INTEGER)) AS \"bills.NetTax\", SUM(CAST(\"itemTotal\" AS INTEGER)) AS \"bills.NetTotal\" FROM \"billingsummaries\" AS \"billingsummary\" LEFT OUTER JOIN \"billingdetails\" AS \"bills\" ON \"billingsummary\".\"id\" = \"bills\".\"billingSummaryId\" WHERE (\"billingsummary\".\"createdAt\" >= '2022-07-07 18:15:00.000 +00:00' AND \"billingsummary\".\"createdAt\" <= '2022-08-08 18:15:00.000 +00:00');"
        },
        "original": {
            "length": 115,
            "name": "error",
            "severity": "ERROR",
            "code": "42702",
            "position": "106",
            "file": "parse_relation.c",
            "line": "801",
            "routine": "scanRTEForColumn",
            "sql": "SELECT \"billingsummary\".\"id\", \"billingsummary\".\"doctorId\", \"bills\".\"id\" AS \"bills.id\", date_trunc('day', \"createdAt\") AS \"bills.date\", SUM(CAST(\"itemDiscount\" AS INTEGER)) AS \"bills.NetDiscount\", SUM(CAST(\"itemCredit\" AS INTEGER)) AS \"bills.NetCredit\", SUM(CAST(\"tax\" AS INTEGER)) AS \"bills.NetTax\", SUM(CAST(\"itemTotal\" AS INTEGER)) AS \"bills.NetTotal\" FROM \"billingsummaries\" AS \"billingsummary\" LEFT OUTER JOIN \"billingdetails\" AS \"bills\" ON \"billingsummary\".\"id\" = \"bills\".\"billingSummaryId\" WHERE (\"billingsummary\".\"createdAt\" >= '2022-07-07 18:15:00.000 +00:00' AND \"billingsummary\".\"createdAt\" <= '2022-08-08 18:15:00.000 +00:00');"
        },
        "sql": "SELECT \"billingsummary\".\"id\", \"billingsummary\".\"doctorId\", \"bills\".\"id\" AS \"bills.id\", date_trunc('day', \"createdAt\") AS \"bills.date\", SUM(CAST(\"itemDiscount\" AS INTEGER)) AS \"bills.NetDiscount\", SUM(CAST(\"itemCredit\" AS INTEGER)) AS \"bills.NetCredit\", SUM(CAST(\"tax\" AS INTEGER)) AS \"bills.NetTax\", SUM(CAST(\"itemTotal\" AS INTEGER)) AS \"bills.NetTotal\" FROM \"billingsummaries\" AS \"billingsummary\" LEFT OUTER JOIN \"billingdetails\" AS \"bills\" ON \"billingsummary\".\"id\" = \"bills\".\"billingSummaryId\" WHERE (\"billingsummary\".\"createdAt\" >= '2022-07-07 18:15:00.000 +00:00' AND \"billingsummary\".\"createdAt\" <= '2022-08-08 18:15:00.000 +00:00');",
        "parameters": {}
    },
    "message": "Internal server error"
}

When i directly try to get billingdetails rather than from include or association it's working as charm here's the code for that which is working with direct one

const billing = await models.billingdetails.findAll({
    attributes: [
      [
        sequelize.fn("date_trunc", `${freq}`, sequelize.col("createdAt")),
        "date",
      ],
      [
        sequelize.fn(
          "SUM",
          sequelize.cast(sequelize.col("itemDiscount"), "integer")
        ),
        "NetDiscount",
      ],
      [
        sequelize.fn(
          "SUM",
          sequelize.cast(sequelize.col("itemCredit"), "integer")
        ),
        "NetCredit",
      ],
      [
        sequelize.fn("SUM", sequelize.cast(sequelize.col("tax"), "integer")),
        "NetTax",
      ],
      [
        sequelize.fn(
          "SUM",
          sequelize.cast(sequelize.col("itemTotal"), "integer")
        ),
        "NetTotal",
      ],
    ],
    group: "date",
  });

Gives me output of

{
    "status": 200,
    "result": [
        {
            "date": "2022-07-08T00:00:00.000Z",
            "NetDiscount": "800",
            "NetCredit": "1600",
            "NetTax": "200",
            "NetTotal": "1000"
        }
    ],
    "message": "Successfully fetched"
}

My Model billingsummary.js

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class billingsummary extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      billingsummary.belongsTo(models.patient, { foreignKey: "patientId" });
      billingsummary.belongsTo(models.visit, { foreignKey: "visitId" });
      billingsummary.belongsTo(models.users, {
        foreignKey: "doctorId",
        as: "doctor",
      });
      billingsummary.belongsTo(models.users, {
        foreignKey: "createdBy",
        as: "creator",
      });
      billingsummary.hasMany(models.billingdetails, {
        foreignKey: "billingSummaryId",
        as: "bills",
      });
    }
  }
  billingsummary.init(
    {
      visitId: DataTypes.BIGINT,
      doctorId: DataTypes.BIGINT,
      patientId: DataTypes.BIGINT,
      createdBy: DataTypes.BIGINT,
      totalDiscount: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "billingsummary",
    }
  );
  return billingsummary;
};

billingdetails.js

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class billingdetails extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
      billingdetails.belongsTo(models.service, { foreignKey: "serviceId" });
      billingdetails.belongsTo(models.doctorcharge, { foreignKey: "doctorId" });
      billingdetails.belongsTo(models.billingsummary, {
        foreignKey: "billingSummaryId",
      });
    }
  }
  billingdetails.init(
    {
      billingSummaryId: DataTypes.BIGINT,
      doctorId: DataTypes.BIGINT,
      serviceId: DataTypes.BIGINT,
      facilityChargeId: DataTypes.BIGINT,
      promotionId: DataTypes.BIGINT,
      quantity: DataTypes.BIGINT,
      testCostId: DataTypes.BIGINT,
      prescriptionItemId: DataTypes.BIGINT,
      itemDiscount: DataTypes.STRING,
      itemCredit: DataTypes.STRING,
      tax: DataTypes.STRING,
      promotionDiscountPercent: DataTypes.STRING,
      itemTotal: DataTypes.STRING,
      itemAmount: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "billingdetails",
    }
  );
  return billingdetails;
};

What i'm confused of here actually is why can't i use those functions inside of my nested included models which i'm currently required of

I'm not that good with sequelize and not well experienced with internal workings so if anyone have some suggestions please put forward, i can't find an appropriate solution in the whole stackoverflow community so i lean towards creating my own query here.

My main question here is can we or can't we use `sequelize.fn` inside some include block of some query like `model.findAll()`

Thank you for the respose @Anatoly i have found the working solution after some deep dive. — Nabin Thapa, Jul 19 '22 at 12:14

score 0 · Answer 1 · edited Jul 20 '22 at 11:46

Hey everyone I have struggle quite a bit in this problem and finally came with a solution which is just to include your models and set attributes to an empty array then we can simply refer that included models property as regular object here's my code which worked later

bills = await models.billingsummary.findAll({
  attributes: [
    [Sequelize.col("billingsummary.doctorId"), "DoctorId"],
    [Sequelize.col("detailBills.itemTotal"), "ItemTotal"],
    [Sequelize.col("detailBills.itemDiscount"), "ItemDiscount"],
    [Sequelize.col("detailBills.itemCredit"), "ItemCredit"],
    [Sequelize.col("detailBills.tax"), "ItemTax"],
    [Sequelize.col("detailBills.createdAt"), "CreatedAt"],
    [Sequelize.col("detailBills.id"), "InvoiceNumber"],
  ],
  include: [
    {
      model: models.billingdetails,
      as: "detailBills",
      attributes: [],
    },
  ],
});

As you can see I have used my included model as detailBills and had retrieved it's data using format like detailBills.createdAt.

This is the raw query output

SELECT "billingsummary"."id", "billingsummary"."doctorId" AS "DoctorId", "detailBills"."itemTotal" AS "ItemTotal", "detailBills"."itemDiscount" AS "ItemDiscount", "detailBills"."itemCredit" AS "ItemCredit", "detailBills"."createdAt" AS "CreatedAt", "detailBills"."id" AS "InvoiceNumber" FROM "billingsummaries" AS "billingsummary" LEFT OUTER JOIN "billingdetails" AS "detailBills" ON "billingsummary"."id" = "detailBills"."billingSummaryId";

Can't use attributes with sequelize.fn inside include block of findAll query

I'm trying to use sum for the data of my relational data model which mean i have billingsummary as a parent model and billingdetails as child model and both are linked with association of many to one relation. I basically did the query as

What i'm confused of here actually is why can't i use those functions inside of my nested included models which i'm currently required of

My main question here is can we or can't we use sequelize.fn inside some include block of some query like model.findAll()

1 Answers1

My main question here is can we or can't we use `sequelize.fn` inside some include block of some query like `model.findAll()`