时间戳

首页 > 解决方案 > TypeORM QueryBuilder 使用外键约束插入表

typeorm - TypeORM QueryBuilder 使用外键约束插入表

问题描述

我有两张表 - 组织和部门。部门表在 organizationId 列上有组织的外键约束。对于这两个表，我显然还有很多其他的外键关系。这只是在用例上。

在两个表上执行 select 语句都可以正常工作：

import "reflect-metadata";
import {createConnection} from "typeorm";
import {getConnection} from "typeorm";
import {Departments} from "./entity/Departments"

createConnection().then(async connection => {

    //get total estimated hours by month
    const departments = await getConnection()
        .createQueryBuilder( "Departments", "d")
        .innerJoinAndSelect("organizations", "o", "d.organizationId = o.organizationId")
        .select("organizationName, departmentName ")
        .where("d.organizationId = :organizationId", {organizationId: 2})
        .getRawMany();
    //  .getSql();

    console.log("Departments: ", departments);
}).catch(error => console.log(error));

当我尝试使用 QueryBuilder 插入 Departments 时，organizationId 未被识别为有效列：

import "reflect-metadata";
import {createConnection} from "typeorm";
import {getConnection} from "typeorm";
import {Departments} from "./entity/Departments"

createConnection().then(async connection => {

    //get total estimated hours by month
    await getConnection()
        .createQueryBuilder()
        .insert()
        .into(Departments)
        .values([
            { departmentName: "Test Product", organizationId: 2}
            ])
        .execute();
    //  .getSql();

}).catch(error => console.log(error));

这是组织和部门的实体代码：

组织.ts -

import {
  Column,
  Entity,
  Index,
  OneToMany,
  PrimaryGeneratedColumn,
} from "typeorm";
import { Departments } from "./Departments";
import { Groups } from "./Groups";
import { IntegrationDetails } from "./IntegrationDetails";
import { Jobcodes } from "./Jobcodes";
import { JobMaster } from "./JobMaster";
import { Locations } from "./Locations";
import { Phases } from "./Phases";
import { RoadmapActualCostDetails } from "./RoadmapActualCostDetails";
import { RoadmapEstimatedCostDetails } from "./RoadmapEstimatedCostDetails";
import { RoadmapEstimates } from "./RoadmapEstimates";
import { RoadmapTemplates } from "./RoadmapTemplates";
import { Strategies } from "./Strategies";
import { Teammates } from "./Teammates";

@Index("organizations_organizationId_index", ["organizationId"], {})
@Index("organizations_pk", ["organizationId"], { unique: true })
@Entity("organizations", { schema: "dbo" })
export class Organizations {
  @PrimaryGeneratedColumn({ type: "int", name: "organizationId" })
  organizationId: number;

  @Column("varchar", { name: "organizationName", length: 100 })
  organizationName: string;

  @Column("varchar", { name: "orgShortName", length: 50 })
  orgShortName: string;

  @OneToMany(() => Departments, (departments) => departments.organization)
  departments: Departments[];

  @OneToMany(() => Groups, (groups) => groups.organization)
  groups: Groups[];

  @OneToMany(
    () => IntegrationDetails,
    (integrationDetails) => integrationDetails.organization
  )
  integrationDetails: IntegrationDetails[];

  @OneToMany(() => Jobcodes, (jobcodes) => jobcodes.organization)
  jobcodes: Jobcodes[];

  @OneToMany(() => JobMaster, (jobMaster) => jobMaster.organization)
  jobMasters: JobMaster[];

  @OneToMany(() => Locations, (locations) => locations.organization)
  locations: Locations[];

  @OneToMany(() => Phases, (phases) => phases.organization)
  phases: Phases[];

  @OneToMany(
    () => RoadmapActualCostDetails,
    (roadmapActualCostDetails) => roadmapActualCostDetails.organization
  )
  roadmapActualCostDetails: RoadmapActualCostDetails[];

  @OneToMany(
    () => RoadmapEstimatedCostDetails,
    (roadmapEstimatedCostDetails) => roadmapEstimatedCostDetails.organization
  )
  roadmapEstimatedCostDetails: RoadmapEstimatedCostDetails[];

  @OneToMany(
    () => RoadmapEstimates,
    (roadmapEstimates) => roadmapEstimates.organization
  )
  roadmapEstimates: RoadmapEstimates[];

  @OneToMany(
    () => RoadmapTemplates,
    (roadmapTemplates) => roadmapTemplates.organization
  )
  roadmapTemplates: RoadmapTemplates[];

  @OneToMany(() => Strategies, (strategies) => strategies.organization)
  strategies: Strategies[];

  @OneToMany(() => Teammates, (teammates) => teammates.organization)
  teammates: Teammates[];
}

部门.ts

export class Departments {
  @PrimaryGeneratedColumn({ type: "int", name: "departmentId" })
  departmentId: number;

  @Column("nvarchar", { name: "departmentName", length: 100 })
  departmentName: string;

  @Column("int", { name: "ownerId", nullable: true })
  ownerId: number | null;

  @ManyToOne(() => Organizations, (organizations) => organizations.departments)
  @JoinColumn([
    { name: "organizationId", referencedColumnName: "organizationId" },
  ])
  organization: Organizations;

  @ManyToOne(() => Departments, (departments) => departments.departments)
  @JoinColumn([{ name: "parentId", referencedColumnName: "departmentId" }])
  parent: Departments;

  @OneToMany(() => Departments, (departments) => departments.parent)
  departments: Departments[];

  @OneToMany(() => Products, (products) => products.department)
  products: Products[];

  @ManyToMany(() => Programs, (programs) => programs.departments)
  @JoinTable({
    name: "programs_departments",
    joinColumns: [
      { name: "departmentId", referencedColumnName: "departmentId" },
    ],
    inverseJoinColumns: [
      { name: "programId", referencedColumnName: "programId" },
    ],
    schema: "dbo",
  })
  programs: Programs[];

  @OneToMany(() => Roadmaps, (roadmaps) => roadmaps.department)
  roadmaps: Roadmaps[];
}

标签： typeorm

解决方案

没关系 - 我想通了。即使在实体文件中正确定义了 ManyToOne 关系，您仍然必须添加 @Column 引用。

@Column("int", { name: "organizationId" }) organizationId: number;

与此有关 - Typeorm insert with relationId

推荐阅读