node.js - TypeORM/MySQL - QueryFailedError：在 'where 子句' 中未知的欄位 'NaN'

我需要幫助。我正在使用 Express 和 NodeJS 搭配 TypeScript 開發一個 API，並且我使用 TypeORM 進行資料庫操作。我正試圖建立一個函式，列出並回傳特定使用者所建立的所有預約，但這個錯誤一直發生，我不知道為什麼。我已經搜尋過答案並閱讀了一些 StackOverflow 的問題與解答，但都沒有用，這就是為什麼我決定尋求幫助。

發生例外狀況：QueryFailedError：在 'where 子句' 中未知的欄位 'NaN'，在 QueryFailedError.TypeORMError [作為建構函式] (D:\Pessoal\Vinicius\TCC\hexperience-api\src\error\TypeORMError.ts:7:9) 於 new QueryFailedError (D:\Pessoal\Vinicius\TCC\hexperience-api\src\error\QueryFailedError.ts:9:9) 於 Query.onResult (D:\Pessoal\Vinicius\TCC\hexperience-api\src\driver\mysql\MysqlQueryRunner.ts:195:37) 於 Query.execute (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\commands\command.js:31:14) 於 PoolConnection.handlePacket (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\connection) js:456:32）於 PacketParser.onPacket（D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\connection.js:85:12）在 PacketParser.executeStart（D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\packet_parser.js:75:16）在 Socket.（D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\connection.js:92:25）在 Socket.emit（events.js:315:20）在 addChunk（internal/streams/readable.js:309:12）在 readableAddChunk（internal/streams/readable.js:284:9）在 Socket.Readable.push（internal/streams/readable.js:223:10）在 TCP.onStreamRead（internal/stream_base_commons.js:188:23）在 TCP.callbackTrampoline（internal/async_hooks.js:131:14）

這是程式碼：

使用者.ts

import {Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn, OneToOne} from 'typeorm';
import { Exclude } from 'class-transformer';

import Host from './Host';

export enum typeEnum {
  user = 'user',
  host = 'host',
  admin = 'admin'
}

@Entity('User')
class User {
  @PrimaryGeneratedColumn('increment')
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;

  @Column()
  @Exclude()
  password: string;

  @Column()
  avatar: string;

  @Column()
  phone_number: string;

  @Column()
  bio: string;

  @Column({ type: 'boolean' })
  is_blocked: boolean;

  @Column({
    type: 'enum',
    enum: typeEnum,
    default: typeEnum.user
  })
  type: typeEnum;

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;

  @OneToOne(type => Host, host => host.user)
  host: Host;
}

export default User;

Appointment.ts

import {Column,Entity,PrimaryGeneratedColumn,CreateDateColumn,UpdateDateColumn,ManyToOne,JoinColumn} from "typeorm";

import Schedule from "../../../../experiences/infra/typeorm/entities/Schedule";
import User from "../../../../users/infra/typeorm/entities/User";

@Entity('Appointment')
class Appointment {
  @PrimaryGeneratedColumn('increment')
  id: number;

  @Column()
  guests: number;

  @Column({ type: 'boolean' })
  paid: boolean;

  @Column({ type: 'decimal', precision: 12, scale: 2 })
  final_price: number;

  @CreateDateColumn()
  created_at: Date;

  @UpdateDateColumn()
  updated_at: Date;

  @ManyToOne(() => User)
  @JoinColumn({ name: 'user_id' })
  user: User;

  @ManyToOne(() => Schedule)
  @JoinColumn({ name: 'schedule_id' })
  schedule: Schedule;
}

export default Appointment;

AppointmentsRepository.ts

import { getRepository, Repository } from "typeorm";

import Appointment from "../entities/Appointment";

import IAppointmentsRepository from "@modules/appointments/repositories/IAppointmentsRepository";
import ICreateAppointmentDTO from "@modules/appointments/dtos/ICreateAppointmentDTO";

class AppointmentsRepository implements IAppointmentsRepository {
  private ormRepository: Repository<Appointment>;

  constructor () {
    this.ormRepository = getRepository(Appointment);
  }

  // this function has the same idea as the one that has the problem but this one works normally
  public async findByExperienceId(exp_id: number): Promise<Appointment[]> {
    const appointments = await this.ormRepository.find({
      relations: ['user', 'schedule', 'schedule.experience'],
      where: {
        schedule: {
          experience: {
            id: exp_id,
          }
        }
      }
    });

    return appointments
  }

  // here is the function with the problem
  public async findByUserId(user_id: number): Promise<Appointment[]> {
    // this was my last attempt to fix the problem, didn't work
    const appointments = await this.ormRepository.query(
      'SELECT * FROM Appointment a ' +
      'LEFT JOIN `User` u ON u.id  = a.user_id ' +
      'LEFT JOIN Schedule s ON s.id = a.schedule_id ' +
      'LEFT JOIN Experience e ON e.id = s.exp_id ' +
      'WHERE a.user_id = ' + Number(user_id)
    );
    // This was the original code
    // const appointments = await this.ormRepository.find({
    //   relations: ['user', 'schedule', 'schedule.experience'],
    //   where: {
    //     user: {
    //       id: user_id
    //     }
    //   }
    // });

    return appointments;
  }
}

export default AppointmentsRepository;

列出使用者預約服務.ts

import { inject, injectable } from "tsyringe";

import AppError from "@shared/errors/AppError";

import Appointment from "../infra/typeorm/entities/Appointment";

import IUsersRepository from "@modules/users/repositories/IUsersRepository";
import IAppointmentsRepository from "../repositories/IAppointmentsRepository";

interface IRequest {
  id: number
}

@injectable()
class ListUserAppointmentsService {
  constructor (
    @inject('UsersRepository')
    private usersRepository: IUsersRepository,

    @inject('AppointmentsRepository')
    private appointmentsRepository: IAppointmentsRepository
  ) {}

  public async execute({ id }: IRequest): Promise<Appointment[]> {
    const user = await this.usersRepository.findById(id);

    if (!user) {
      throw new AppError('User does not exists');
    }

    // originally, I was passing the parameter id
    const appointments = await this.appointmentsRepository.findByUserId(user.id);

    return appointments;
  }
}

export default ListUserAppointmentsService;

答案＃1

在 NestJS 中我也有這個問題，
這是我的程式碼

@Injectable()
export class NavSiteCategoryService {
 
this.navSiteCategoryRepository.createQueryBuilder('category')
'sites.name','sites.desc','sites.url'])

  constructor(
    @InjectRepository(NavSiteCategory)
    private navSiteCategoryRepository: Repository<NavSiteCategory>,
  ) {
  }

  async create(createNavSiteCategoryDto: CreateNavSiteCategoryDto) {
    const newCategory =  this.navSiteCategoryRepository.create(createNavSiteCategoryDto);
    return  await this.navSiteCategoryRepository.save(newCategory)
  }

  async findAll() {
    return this.navSiteCategoryRepository.createQueryBuilder('category')
      .select(["category.id","category.name","category.desc"])
      .leftJoinAndSelect("category.sites", "sites").getMany()
  }

  async categoryOptions() {
    return this.navSiteCategoryRepository.createQueryBuilder('option')
      .select(["option.id","option.name","option.desc"])
      .getMany()
  }

  async findOne(id: number) {
    return this.navSiteCategoryRepository.createQueryBuilder('category')
      .select(["category.id","category.name","category.desc"])
      .leftJoinAndSelect("category.sites", "sites")
      .where({id})
      .getOne()
  }

  async update(id: number, updateNavSiteCategoryDto: UpdateNavSiteCategoryDto) {
    await  this.navSiteCategoryRepository.update(id, updateNavSiteCategoryDto );
  }

  async remove(id: number) {
    await this.navSiteCategoryRepository.delete(id);
  }
}

當 service 中的 findAll 方法顯示成功時，categoryOptions 方法會像你上述描述的那樣出現錯誤，

我不知道原因，控制器是唯一的不同，
錯誤代碼是（注意：@Get('/options')，@Get()）

    @ApiTags('站点分类')
@Controller('nav-site-category')
export class NavSiteCategoryController {
  constructor(
    @Inject(forwardRef(() => NavSiteCategoryService))
    private readonly navSiteCategoryService: NavSiteCategoryService
  ) { }
  @Get()
  findAll() {
    return this.navSiteCategoryService.findAll();
  }


  @Get(':id')
  findOne(@Param('id') id: string) {
    return this.navSiteCategoryService.findOne(+id);
  }

  @Post()
  create(@Body() createNavSiteCategoryDto: CreateNavSiteCategoryDto) {
    return this.navSiteCategoryService.create(createNavSiteCategoryDto);
  }


  @Patch(':id')
  update(@Param('id') id: string, @Body() updateNavSiteCategoryDto: UpdateNavSiteCategoryDto) {
    return this.navSiteCategoryService.update(+id, updateNavSiteCategoryDto);
  }

  @Delete(':id')
  remove(@Param('id') id: string) {
    return this.navSiteCategoryService.remove(+id);
  }

  @Get('/options')
  options() {
    return this.navSiteCategoryService.categoryOptions()
  }

}

解決方案是將 Options 方法放在最上面，錯誤就修正了，我不知道為什麼，希望這能幫助其他人

成功代碼：

@ApiTags('站点分类')
@Controller('nav-site-category')
export class NavSiteCategoryController {
  constructor(
    @Inject(forwardRef(() => NavSiteCategoryService))
    private readonly navSiteCategoryService: NavSiteCategoryService
  ) { }
  @Get()
  findAll() {
    return this.navSiteCategoryService.findAll();
  }

  @Get('/options')
  options() {
    return this.navSiteCategoryService.categoryOptions()
  }

  @Get(':id')
  findOne(@Param('id') id: string) {
    return this.navSiteCategoryService.findOne(+id);
  }

  @Post()
  create(@Body() createNavSiteCategoryDto: CreateNavSiteCategoryDto) {
    return this.navSiteCategoryService.create(createNavSiteCategoryDto);
  }


  @Patch(':id')
  update(@Param('id') id: string, @Body() updateNavSiteCategoryDto: UpdateNavSiteCategoryDto) {
    return this.navSiteCategoryService.update(+id, updateNavSiteCategoryDto);
  }

  @Delete(':id')
  remove(@Param('id') id: string) {
    return this.navSiteCategoryService.remove(+id);
  }

}