PostgreSQL self join not working on TypeORM

Question

My goal is to select users from USER table where userId = user_id_creator(who created the user, some users can create other users according to the user role) I need to convert this Postgres SQL query to TypeOrm query builder

    select  DISTINCT ON (u1.id) *
  from public.user as u1
  inner join  public.user as u2
    on u1.id = u2.created_by

I executed it in Postgres it's working fine, I get a list of users who created other users. but when I converted it in TypeOrm like this :

const rawData = AppDataSource.manager.query(`
  select  DISTINCT ON (u1.id) *
  from public.user as u1
  right join  public.user as u2
    on u1.id = u2.created_by
  `)

it's giving me an other result, it's selecting the list of created users instead of the creators. Thank you

Answer 1

I think I found the solution.Indeed, the query is not working on TypeOrm because I didn't specify which columns to select, selecting all columns when self joining the table, always gives me the columns from the right, so my solution is to specify which columns to select in this way :

const rawData = AppDataSource.manager.query(`
  SELECT DISTINCT ON (u1.id) u1.id, u1.email
  FROM public.user as u1
  INNER JOIN  public.user as u2
    ON u1.id = u2.created_b
  `)