Compare uuid and string on TypeORM query builder

Question

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

score 1 · Answer 1 · answered Jan 03 '20 at 21:41

1

https://www.postgresqltutorial.com/postgresql-cast/

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

answered Jan 03 '20 at 21:41

Garine

574
5
21

1

I had already tried that, but for some reason it convert photo.userId to photo.userid, note the lowercase (I to i) on userid. – hnakao11 Jan 03 '20 at 21:50
@hnakao11 then put photo.userId as photo_user_id – Garine Jan 03 '20 at 21:58
@hnakao11 if you send me the whole query I can help you more. – Garine Jan 03 '20 at 21:59
I edited the question, I don't know if you understand better – hnakao11 Jan 03 '20 at 22:16

score 1 · Accepted Answer · answered Jan 06 '20 at 13:33

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

score 0 · Answer 3 · answered Jan 04 '20 at 03:17

First off the conversion of 'I' to "i" (upper to lower) in userId is exactly what would be expected, as identifiers are all lower cased unless double quoted. Avoid that if possible as when used you must double quote every time the identifier is used.
Secondly the type uuid has some strange and unexpected formatting rules. You can compare a string::uuid to a uuid as expected, but uuid::text may not compare to a srting. As uuid::text will format as hhhhhhhh-hhhh-hhhh-hhhh-hhhhhhhhhhhh (where h is a hexdigit). The dashes are often removed if storing as a a string. So reverse the typical order; cast the string as uuid. See the following example:

create table id_uuid (id uuid, col1 text);
create table id_str  (id text, col1 text 

insert into id_uuid(id, col1) values(gen_random_uuid(),'Id defined as uuid');
insert into id_str (id, col1)
  select replace(id::text,'-',''),'Id defined as string'
    from id_uuid;

select * from id_uuid;
select * from id_str;

select * 
  from id_uuid u
  join id_str  s
    on (u.id::text = s.id);

select * 
  from id_uuid u
  join id_str  s
    on (u.id = s.id::uuid);

So, It is a very good explanation about the conversion of uuid to string and vice versa. One requirement is that the database cannot be modified. I still need the answer to my question, if you can help me but considering my question... — hnakao11, Jan 04 '20 at 14:20

Compare uuid and string on TypeORM query builder

3 Answers3