TypeORM "ERROR [ExceptionsHandler] for SELECT DISTINCT, ORDER BY expressions must appear in select list" when ordering by alias and using take & skip

Question

I'm using NestJS with TypeORM and a Postgres Database.
I am calculating after how many km the car has to go to inspection the next time and using that value to order the list of cars, that I'm selecting from.

This works totally fine so far, but as soon as I try to paginate the outcome, using skip and take, I get the error: ERROR [ExceptionsHandler] for SELECT DISTINCT, ORDER BY expressions must appear in select list

This is the code:

const dbQuery = this.carsRepository
  .createQueryBuilder('car')
  .addSelect(
    `(floor((car.milage + 5000) / vehicleType.inspectionIntervalInKm) * vehicleType.inspectionIntervalInKm - car.milage) as nextInspectionDueInKm`,
  )
  .leftJoinAndSelect('car.vehicleType', 'vehicleType')
  .orderBy('nextInspectionDueInKm', Order.ASC)
  .skip(page * limit)
  .take(limit);
const result = await dbQuery.getMany()

I googled and tried a lot of things, but couldn't find a working solution. So maybe someone here can help me with this little problem :)

Answer 1

you have to add double quote in your variable like this

.addSelect(
    `(floor((car.milage + 5000) / vehicleType.inspectionIntervalInKm) * vehicleType.inspectionIntervalInKm - car.milage) as "nextInspectionDueInKm"`,
  )