可以使用sequelize.fn内部使用findall查询块

Question

我正在尝试使用总和作为我的关系数据模型的数据，这意味着我将Billingsummary作为父型模型，而BillingDetails作为儿童模型，并且两者都与许多与一个关系的关联有关。我基本上进行了查询，

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

并且它正在工作并返回数据，

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

但是当我在属性上添加此属性时添加一些属性

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",
      },
    ],
  });

时，它会给我带来错误，例如

{
    "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"
}

当我直接尝试获得 billingdetails < /strong>而不是来自包括或关联，它是魅力的，这是与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",
  });

使我输出了

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

我模型 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;
};

我对这里感到困惑的是，为什么我不能在嵌套的内部使用这些功能，其中包括我目前所要求的模型，

我对quelize不太擅长，并且对内部的经验不佳工作原理，因此，如果有人提出一些建议，请在整个Stackoverflow社区中找到适当的解决方案，因此我倾向于在这里创建自己的查询。

我这里的主要问题是我们可以或不能使用sequelize.fn在某些地方包括某些查询的块，例如model.findall（）

Answer 1

嘿，我在这个问题上都挣扎了很多人，最后带来了一个解决方案，该解决方案只是包括您的模型并将属性设置为空

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: [],
    },
  ],
});

数组您可以看到我已将其中的模型用作detailbills，并使用格式检索了它的数据，例如detail bills.createateDat。

这是原始查询输出

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";