How do I query with the orWhere query properly using TypeORM

Question

I am trying to use the orWhere clause in TypeORM as follows. The query is supposed to find notes by character search in the title and note fields. I need all the notes within a channel with the given media id and the channel user id. I also need the notes of the other channel users with the collaboration field that is set to true. So basically doing the search on the notes for the channel user and any other users that shared their notes.

Query Function:

public async searchByMediaIdCollaboration({
  mediaId,
  query,
  channelId,
  channelUserId,
  params,
}: {
  mediaId: string;
  query: SearchDto;
  channelId: string;
  channelUserId: string;
  params: any;
}) {
  const queryBuilder = this.dataSource.createQueryBuilder(NoteEntity, "note");

  // Join the topics table
  if (!!Object.keys(params).find((key) => key === "topics")) {
    queryBuilder.leftJoinAndSelect("note.topics", "topic");
  }

  queryBuilder
    .where("note.channelId = :channelId", { channelId: channelId })
    .andWhere("note.mediaId = :mediaId", { mediaId: mediaId })
    .andWhere("note.channelUserId = :channelUserId", {
      channelUserId: channelUserId,
    })
    .andWhere("(LOWER(note.title) LIKE :s OR LOWER(note.note) LIKE :s)", {
      s: `%${query.search.toLocaleLowerCase()}%`,
    })
    .orWhere("note.channelId = :channelId", { channelId: channelId })
    .where("note.mediaId = :mediaId", { mediaId: mediaId })
    .andWhere("note.collaborate = :collaborate", {
      collaborate: true,
    })
    .andWhere("(LOWER(note.title) LIKE :s OR LOWER(note.note) LIKE :s)", {
      s: `%${query.search.toLocaleLowerCase()}%`,
    });

  return await queryBuilder.getMany();
}

Note Entity:

@Entity("note")
export class NoteEntity {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column({ nullable: false })
  note: string;

  @Column({ nullable: false })
  author: string;

  @Column({ nullable: true, default: "" })
  title: string;

  @Column({ nullable: false })
  mediaPlayPosition: number;

  @Column({ nullable: false })
  channelId: string;

  @Column({ nullable: false })
  channelUserId: string;

  @Column({ nullable: false })
  mediaId: string;

  @Column({ nullable: false })
  sourceId: string;

  @Column({ default: false })
  collaborate: boolean;

  @CreateDateColumn()
  createdDate: Date;

  @UpdateDateColumn()
  updatedDate: Date;

  /**
   * Associations
   */
  @ManyToOne(() => ChannelEntity, (channel) => channel.notes, {
    nullable: false,
  })
  channel: ChannelEntity;

  @ManyToMany(() => TopicEntity, (topic) => topic.notes)
  topics: TopicEntity[];

  @ManyToOne(() => MediaEntity, (media) => media.notes, { nullable: true })
  media: MediaEntity;

  @OneToMany(() => StickyEntity, (sticky) => sticky.note)
  stickys: StickyEntity[];
}

This doesn't work. It only returns the results of this part of the query and omits the first part.

 .orWhere("note.channelId = :channelId", { channelId: channelId })
    .where("note.mediaId = :mediaId", { mediaId: mediaId })
    .andWhere("note.collaborate = :collaborate", {
      collaborate: true,
    })
    .andWhere("(LOWER(note.title) LIKE :s OR LOWER(note.note) LIKE :s)", {
      s: `%${query.search.toLocaleLowerCase()}%`,
    });

Any assistance is appreciated. Thank you.

Answer 1

If you are using .where() multiple times, only the conditions starting from the last .where() will be applied, the others are overwritten.

To get nested WHERE clauses, make use of Brackets and NotBrackets like in the following example (taken from the official documentation at https://typeorm.io/select-query-builder#adding-where-expression):

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(
        new Brackets((qb) => {
            qb.where("user.firstName = :firstName", {
                firstName: "Timber",
            }).orWhere("user.lastName = :lastName", { lastName: "Saw" })
        }),
    )

resulting in an SQL query like this:

SELECT ... FROM users user
WHERE user.registered = true AND (user.firstName = 'Timber' OR user.lastName = 'Saw')

You can also find a similar question with multiple answers here: How to make complex nested where conditions with typeORM? (I took quite some inspiration from this answer https://stackoverflow.com/a/69165948/12022565 and added info about multiple .where() calls. Other answers can show you other approaches that might also achieve your goal.)