Search code examples
node.jspostgresqltypescriptnestjstypeorm

Compare uuid and string on TypeORM query builder


I want to join 2 tables where user.id = photo.userId but the problem here is that the userId on photo table is varchar and that can't change. So I did a queryBuilder to join and the problem is here:

....
.where(user.id = photo.userId)
....

this query throw an error: operator does not exists: uuid = character varying

Is there any way to make this work?

Note: My project is a NestJS API, using TypeORM and Postgresql.

EDIT I already have the Photo result and use it on a subQuery:

query = query
         .where(qb => {
              const subQuery = qb.subQuery()
              .select('user.id')
              .from(User, 'user')
              .where('user.id = photo.userId)
              .getQuery();
              return 'EXISTS' + subQuery;
          });

Solution

  • Thank you for the help, finally the best solution I found was to create a postgres function as indicated here and then call it in the code like this:

    query = query
             .where(qb => {
                  const subQuery = qb.subQuery()
                  .select('user.id')
                  .from(User, 'user')
                  .where('user.id = uuid_or_null(photo.userId))  // here
                  .getQuery();
                  return 'EXISTS' + subQuery;
              });