node.js - Sequelize Association 获取像 JSON 这样的数据

Question

我用两个表作为椰子和椰子列表简化了我的问题。 Coconut table 存储了椰子的规范和cocot_lists 卖家拥有椰子的商店。每次，都会在卖家和椰子之间创建一个关系，并将其插入到 cococo_lists 表中。

我尝试使用带有属性的关键字，设置不同的关系 HasMany、HasOne、BelongsToMany。

Coconut_List

import { Column, DataType, Model, Table, ForeignKey, BelongsTo } from 'sequelize-typescript';
import Coconut from './coconut';


@Table({
  modelName: 'cononuts_list',
  timestamps: true,
  paranoid: true,
})
export default class CoconutList extends Model<CoconutList> {
  @Column({
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
    type: DataType.INTEGER,
  })
  public id: number;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  public sellerId: string;

  @ForeignKey(() => coconut)
  @Column({
    type: DataType.INTEGER,
    allowNull: false,
  })
  public coconutId: number;

  @BelongsTo(() => Coconut, 'coconutId')
  public coconut: Coconut;


}

椰子

import { Column, DataType, Model, Table, HasMany } from 'sequelize-typescript';
import CoconutList from './coconutList';


@Table({
  modelName: 'coconuts',
  timestamps: true,
  paranoid: true,
})
export default class Coconut extends Model<Coconut> {
  @Column({
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
    type: DataType.INTEGER,
  })
  public id: number;

  @HasMany(() => CoconutList, 'id')
  public coconutList: CoconutList[];

  @Column({
    type: DataType.INTEGER,
    allowNull: false,
  })
  public weight: number;

  @Column({
    type: DataType.INTEGER,
    allowNull: false,
  })
  public count: number;

  @Column({
    type: DataType.FLOAT,
    allowNull: false,
  })
  public size: number;
}

查询:

const coconutsBySeller = await CoconutList.findAll({
    include: [
      {
        model: Coconut,
        attributes: ['weight', 'size', 'count'],
      },
    ],
    attributes: ['sellerId'],
  });

代码输出如下:

[
  {
    "sellerId": "f3ff90d8-ed8e-42c9-9c7b-d607111a359e",
    "coconut": {
      "weight": 100,
      "count": 6,
      "size": 11,
    }
  },
  {
    "sellerId": "f3ff90d8-ed8e-42c9-9c7b-d607111a359e",
    "coconut": {
      "weight": 200,
      "count": 12,
      "size": 20,
    }
  },
  {
    "sellerId": "ffffaaaa-ed8e-42c9-9c7b-d607111a359e",
    "coconut": {
      "weight": 300,
      "count": 18,
      "size": 50,
    }
  },
  {
    "sellerId": "ffffaaaa-ed8e-42c9-9c7b-d607111a359e",
    "coconut": {
      "weight": 100,
      "count": 6,
      "size": 11,
    }
  }
]

相反，我想得到

[
  {
    "sellerId": "f3ff90d8-ed8e-42c9-9c7b-d607111a359e",
    [{
      "weight": 100,
      "count": 6,
      "size": 11,
    },
    {
      "weight": 200,
      "count": 12,
      "size": 20,
    }]
  },
  {
    "sellerId": "ffffaaaa-ed8e-42c9-9c7b-d607111a359e",
    [{
      "weight": 300,
      "count": 18,
      "size": 50,
},   
    {
      "weight": 100,
      "count": 6,
      "size": 11,
    }
  ]
]

除了数据格式之外，我还想知道是否可以在上述查询中使用 Sequelize 获取分配给卖家的椰子的重量、数量、大小字段的 min_max 值。

Answer 1

您可以尝试按卖家分组:

const coconutsBySeller = await CoconutList.findAll({
    include: [
      {
        model: Coconut,
        attributes: ['weight', 'size', 'count'],
      },
    ],
    attributes: ['sellerId'],
    group: ['sellerId']
  });