postgresql - 在typeorm中过滤多对多关系

Question

所以我有两个实体 Target 和 Contact 具有多对多关系。如何根据 typeorm 中的特定目标过滤联系人。

export class Contact {
@PrimaryGeneratedColumn()
id: number;

@Column()
name: string;

@Column()
contactno: string;

@Column()
email: string;

@ManyToMany(
    () => Target,
    target => target.contacts
)
@JoinTable()
targets: Target[];
}

export class Target {

@PrimaryGeneratedColumn()
id: number;

@Column()
name: string;

@ManyToMany(
    () => Contact,
    contact => contact.targets,
    { cascade: true }
)

现在我正在使用以下 queryBuilder 获取数据

this.contactRepository
        .createQueryBuilder("contact")
        .select(["contact"])
        .leftJoinAndSelect("contact.targets", "target")
        .where("contact.targets= :targets", {targets: "4"})
        .getMany();

但这产生了一个

QueryFailedError: column contact.contactId does not exist
at new QueryFailedError (/home/niel_99/Internship/wf/src/api/node_modules/typeorm/error/QueryFailedError.js:11:28)
at Query.callback (/home/niel_99/Internship/wf/src/api/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:176:38)
at Query.handleError (/home/niel_99/Internship/wf/src/api/node_modules/pg/lib/query.js:146:19)
at Connection.connectedErrorMessageHandler (/home/niel_99/Internship/wf/src/api/node_modules/pg/lib/client.js:233:17)
at Connection.emit (events.js:310:20)
at /home/niel_99/Internship/wf/src/api/node_modules/pg/lib/connection.js:109:10
at Parser.parse (/home/niel_99/Internship/wf/src/api/node_modules/pg-protocol/dist/parser.js:42:17)
at Socket.<anonymous> (/home/niel_99/Internship/wf/src/api/node_modules/pg-protocol/dist/index.js:8:42)
at Socket.emit (events.js:310:20)
at addChunk (_stream_readable.js:286:12)
at readableAddChunk (_stream_readable.js:268:9)
at Socket.Readable.push (_stream_readable.js:209:10)
at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

我的数据库中有一个联系人表、目标表和 contact_targets_target 表，其中 contact_targets_target 根据 targetId 存储 contactId

Answer 1

在我的例子中，我没有使用 querybuilder，但你可以尝试 .find 或 .findandcount例子:

let res = await this.userRepository.findAndCount({
      relations: ["roles", "company"],//this for the relation many-to-many
      where: `(username like '%${seachValue}%' or firstname like '%${seachValue}%' 
      or lastname like '%${seachValue}%' or email like '%${seachValue}%')`,
      order: {
        username: sortDirection,
      },
      skip: pageNumber * listeNumber,
      take: listeNumber,

    });