node.js - Sequelize 。 GetOne 查询文本中具有 hasMany-association 的未知列

Question

我阅读了类似的主题，但没有找到我的案例

我有一个简单的模型 User 和模型 UserAddress 与 userId 属性

然后我创建简单的关联

User.hasMany(UserAddress, { as: 'addresses' })
UserAddress.belongsTo(User)

那么如果我打电话

User.getOne({ where: { email }, include: UserAddress })

我收到错误“'字段列表'中的未知列'addresses.userAddressId'”

当然我在任何模型中都没有这样的字段'userAddressId'，它显然是没有意义的，好像它指的是自己。

如果我删除 UserAddress.belongsTo(User) 一切正常，但我无法获得这种行为的逻辑。
谢谢

UPD 模型架构

User.init({
    email: { type: DataTypes.STRING },
    name: { type: DataTypes.STRING },
    surname: { type: DataTypes.STRING }
  }, {
    sequelize,
    modelName: 'User'
  })

UserAddress.init({
    userId: { type: DataTypes.INTEGER },
    city: { type: DataTypes.STRING },
    street: { type: DataTypes.STRING },
    house: { type: DataTypes.STRING },
  }, {
    sequelize,
    modelName: 'UserAddress',
  })

User.hasMany(UserAddress, {as: 'addresses'})
UserAddresses.belongsTo(User) // without this line everything works fine

Answer 1

预先加载的 include 属性应包含 as: 'addresses' 。它应该按预期工作。

例如。

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';

class User extends Model {}
User.init(
  {
    email: { type: DataTypes.STRING },
    name: { type: DataTypes.STRING },
    surname: { type: DataTypes.STRING },
  },
  { sequelize, modelName: 'User' },
);

class UserAddress extends Model {}
UserAddress.init(
  {
    userId: { type: DataTypes.INTEGER },
    city: { type: DataTypes.STRING },
    street: { type: DataTypes.STRING },
    house: { type: DataTypes.STRING },
  },
  { sequelize, modelName: 'UserAddress' },
);

User.hasMany(UserAddress, { as: 'addresses' });
UserAddress.belongsTo(User);

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await User.create(
      {
        email: 'example@gmail.com',
        name: 'Tom',
        surname: 'jerry',
        addresses: [{ city: 'London', street: 'a', house: 'b' }],
      },
      { include: [{ model: UserAddress, as: 'addresses' }] },
    );
    // test
    const user = await User.findOne({
      where: { email: 'example@gmail.com' },
      include: [{ model: UserAddress, as: 'addresses' }],
      raw: true,
    });
    console.log(user);
  } catch (error) {
    console.log(error);
  } finally {
    sequelize.close();
  }
})();

执行结果:

Executing (default): DROP TABLE IF EXISTS "UserAddress" CASCADE;
Executing (default): DROP TABLE IF EXISTS "User" CASCADE;
Executing (default): DROP TABLE IF EXISTS "User" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "User" ("id"   SERIAL , "email" VARCHAR(255), "name" VARCHAR(255), "surname" VARCHAR(255), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'User' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "UserAddress" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "UserAddress" ("id"   SERIAL , "userId" INTEGER, "city" VARCHAR(255), "street" VARCHAR(255), "house" VARCHAR(255), "UserId" INTEGER REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'UserAddress' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "User" ("id","email","name","surname") VALUES (DEFAULT,$1,$2,$3) RETURNING *;
Executing (default): INSERT INTO "UserAddress" ("id","city","street","house","UserId") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING *;
Executing (default): SELECT "User".*, "addresses"."id" AS "addresses.id", "addresses"."userId" AS "addresses.userId", "addresses"."city" AS "addresses.city", "addresses"."street" AS "addresses.street", "addresses"."house" AS "addresses.house", "addresses"."UserId" AS "addresses.UserId" FROM (SELECT "User"."id", "User"."email", "User"."name", "User"."surname" FROM "User" AS "User" WHERE "User"."email" = 'example@gmail.com' LIMIT 1) AS "User" LEFT OUTER JOIN "UserAddress" AS "addresses" ON "User"."id" = "addresses"."UserId";
{ id: 1,
  email: 'example@gmail.com',
  name: 'Tom',
  surname: 'jerry',
  'addresses.id': 1,
  'addresses.userId': null,
  'addresses.city': 'London',
  'addresses.street': 'a',
  'addresses.house': 'b',
  'addresses.UserId': 1 }

检查数据库:

node-sequelize-examples=# select * from "User";
 id |       email       | name | surname
----+-------------------+------+---------
  1 | example@gmail.com | Tom  | jerry
(1 row)
node-sequelize-examples=# select * from "UserAddress";
 id | userId |  city  | street | house | UserId
----+--------+--------+--------+-------+--------
  1 |        | London | a      | b     |      1
(1 row)