Why is TypeORM andWhere breaking my query

Question

I have a very simple query. Media has a relationship with users. The media table has a column userId. I am attempting to do a search on title and description, but only after I get the media for the specified user. For whatever reason the queryBuilder below doesn't work. If I use the first where clause isolating the userId alone, the database returns the correct media for that user. However after adding the andWhere part, the query breaks. It no longer isolates the media by user id, and returns any row in the table with the proper query search match.

public async search(userId: string, query: { search: string }) {
  const queryBuilder = this.dataSource.createQueryBuilder(
    MediaEntity,
    "media",
  );

  queryBuilder
    .where("media.userId = :userId", { userId: userId })
    .andWhere(
      "LOWER(media.title) LIKE :s OR LOWER(media.description) LIKE :s",
      {
        s: `%${query.search.toLocaleLowerCase()}%`,
      },
    );

  return await queryBuilder.getMany();
}

Answer 1

Looks like you have issue with breackets. Let's try to change your raw part of statement like this:

"(LOWER(media.title) LIKE :s OR LOWER(media.description) LIKE :s)",

Whole example of new source code can be like this:

public async search(userId: string, query: { search: string }) {
  const queryBuilder = this.dataSource.createQueryBuilder(
    MediaEntity,
    "media",
  );

  queryBuilder
    .where("media.userId = :userId", { userId: userId })
    .andWhere(
      "(LOWER(media.title) LIKE :s OR LOWER(media.description) LIKE :s)",
      {
        s: `%${query.search.toLocaleLowerCase()}%`,
      },
    );

  return await queryBuilder.getMany();
}