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;
});
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;
});