Typeorm / Postgres: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Question

I have the following entities: User and Role. I want to select users with their roles with pagination and filters.

Error message:

for SELECT DISTINCT, ORDER BY expressions must appear in the select list

@ObjectType()
@Entity()
export class User {
  *******

  @ManyToMany(() => Role, (role) => role.id, {
    nullable: true,
    cascade: true,
  })
  @JoinTable()
  roles: Role[];

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

@ObjectType()
@Entity()
export class Role {
  @Field((type) => Int)
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: UserRoles;

  @Column()
  description: string;

  @CreateDateColumn()
  createdAt: Date;

  @UpdateDateColumn()
  updatedAt: Date;
}

Main query logic:

******
    const [results, total] = **await queryBuilder
      .orderBy(`"user_${sortField}"`, sortOrder)
      .take(limit)
      .skip((page - 1) * limit)
      .leftJoinAndSelect('user.roles', 'role')
      .getManyAndCount();**

    return {
      results,
      total,
    };

It leads to error because in generated SQL line SELECT DISTINCT "distinctAlias"."user_id" as "ids_user_id" doesn't contain a field user_updatedAt which im ordering by.

Note,.take(limit) method adds the above-mentioned line is generated SQL and I can't find a way to edit selected fields.

Generated SQL:

SELECT DISTINCT
   \"distinctAlias\".\"user_id\" as \"ids_user_id\" 
FROM
   (
      SELECT
         \"user\".\"id\" AS \"user_id\",
         \"user\".\"firstName\" AS \"user_firstName\",
         \"user\".\"lastName\" AS \"user_lastName\",
         \"user\".\"email\" AS \"user_email\",
         \"user\".\"phone\" AS \"user_phone\",
         \"user\".\"isActivated\" AS \"user_isActivated\",
         \"user\".\"refreshToken\" AS \"user_refreshToken\",
         \"user\".\"createdAt\" AS \"user_createdAt\",
         \"user\".\"updatedAt\" AS \"user_updatedAt\",
         \"role\".\"id\" AS \"role_id\",
         \"role\".\"title\" AS \"role_title\",
         \"role\".\"description\" AS \"role_description\",
         \"role\".\"createdAt\" AS \"role_createdAt\",
         \"role\".\"updatedAt\" AS \"role_updatedAt\" 
      FROM
         \"user\" \"user\" 
         LEFT JOIN
            \"user_roles_role\" \"user_role\" 
            ON \"user_role\".\"userId\" = \"user\".\"id\" 
         LEFT JOIN
            \"role\" \"role\" 
            ON \"role\".\"id\" = \"user_role\".\"roleId\"
   )
   \"distinctAlias\" 
ORDER BY
   \"user_updatedAt\" DESC,
   \"user_id\" ASC LIMIT 10"

What am I doing wrong? Thanks for your time.

Answer 1

This suggestion helped to solve the problem: https://github.com/typeorm/typeorm/issues/4742#issuecomment-783857414