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

Ok - but you haven’t actually asked a question and this isn’t a free coding site. What have you tried and what issue are you facing? — NickW
– NickW, Commented Dec 26, 2023 at 20:38

Abderazak Amiar · Accepted Answer · 2023-12-26 23:46:35Z

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
  `)

answered Dec 26, 2023 at 23:46

Abderazak Amiar

8169 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL self join not working on TypeORM

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related