Order by joined column broken in Postgres

[frankdugan3]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[x] @next
[ ] 0.x.x (or put your version here)

When ordering by a nested column in Postgres, it would appear that the generated SQL is missing a select clause. I've included an example of the code and the generated SQL with error message and missing select. I am also using lazy loading (combined with TypeGraphQL) if that is a factor.

const [nodes, totalCount] = await this.gageRepository.findAndCount({
      where: search
        ? [
            { name: ILike(`%${search}%`) },
            { type: { name: ILike(`%${search}%`) } },
            { make: { name: ILike(`%${search}%`) } },
            { model: { name: ILike(`%${search}%`) } },
            { description: ILike(`%${search}%`) },
          ]
        : undefined,
      order, // example: { type: { name: 'ASC' } }
      skip,
      take,
    })

SELECT DISTINCT
  "distinctAlias"."Gage_id" AS "ids_Gage_id",
  "distinctAlias"."Gage_type_name"
FROM (
  SELECT
--	"Gage_type"."name" AS "Gage_type_name", <-- This line is missing
    "Gage"."id" AS "Gage_id",
    "Gage"."createdAt" AS "Gage_createdAt",
    "Gage"."updatedAt" AS "Gage_updatedAt",
    "Gage"."version" AS "Gage_version",
    "Gage"."name" AS "Gage_name",
    "Gage"."standard" AS "Gage_standard",
    "Gage"."serialNo" AS "Gage_serialNo",
    "Gage"."minMaxUnit" AS "Gage_minMaxUnit",
    "Gage"."min" AS "Gage_min",
    "Gage"."max" AS "Gage_max",
    "Gage"."description" AS "Gage_description",
    "Gage"."frequency" AS "Gage_frequency",
    "Gage"."ownerId" AS "Gage_ownerId",
    "Gage"."locationId" AS "Gage_locationId",
    "Gage"."typeId" AS "Gage_typeId",
    "Gage"."makeId" AS "Gage_makeId",
    "Gage"."modelId" AS "Gage_modelId"
  FROM
    "public"."gage" "Gage"
  LEFT JOIN "public"."gage_type" "Gage_type" ON "Gage_type"."id" = "Gage"."typeId") "distinctAlias"
ORDER BY
  "distinctAlias"."Gage_type_name" DESC,
  "Gage_id" ASC

-- ERROR:  column distinctAlias.Gage_type_name does not exist
-- LINE 3:   "distinctAlias"."Gage_type_name"
--           ^
-- SQL state: 42703
-- Character: 65

[vlapo]

Not really sure but I think you have to add relations to the find options;

relations: ["profile", "photos", "videos"]

[Kononnable]

@frankdugan3
Did you resolve your issue?
If not we probably need to see also a schema because right now we don't even know if column Gage_type_name exists at all.

[frankdugan3]

Did not solve the problem. The column does exist, and if I run the query with that line added, it works fine. I added that as a comment to help with fixing the issue. I did not try adding the relations because I would think that the code for orderBy should handle generating the correct select statement on its own. If this is expected behavior, feel free to close the issue.

[Kononnable]

@frankdugan3
Let's take a step back for a moment. Forget that you wrote that code and read that issue. And ask yourself how much you know about the issue.

Ok, back to the issue. I believe this is a bug but I cannot confirm or deny it unless I know exactly what is going on there. I really don't like that generated query.

Could you please post your entity definition/make a reproduction repo/provide failing test?

[frankdugan3]

This is a summary of what the entities look like:

@Entity()
export class Gage extends Node {
  @Column({ unique: true, nullable: false, type: 'text' })
  name!: string

  // ...

  // Relationships
  @ManyToOne((type) => GageType, (type) => type.gages, {
    cascade: true,
    nullable: false,
    lazy: true,
    onDelete: 'RESTRICT',
    onUpdate: 'CASCADE',
  })
  type!: Lazy<GageType>

  // ...
}

@Entity()
export class GageType extends Node {
  @Column({ type: 'text', nullable: false, unique: true })
  name!: string

  // Relationships
  @OneToMany((type) => Gage, (gage) => gage.type, { lazy: true })
  gages!: Lazy<Gage[]>
}

export abstract class Node extends BaseEntity {
  @PrimaryGeneratedColumn('uuid')
  readonly id!: string

  @CreateDateColumn()
  createdAt!: Date

  @UpdateDateColumn()
  updatedAt!: Date

  @VersionColumn({ comment: 'Count of updates to this row.' })
  version!: number
}

[delucca]

No updates on this? I'm with the same issue...