node.js - TypeORM/MySQL - QueryFailedError: Okänd kolumn 'NaN' i 'where-satsen'

Jag behöver hjälp. Jag utvecklar ett API med Express och NodeJS med TypeScript, och jag använder TypeORM för databasoperationer. Jag försöker skapa en funktion där jag listar och returnerar alla bokningar som gjordes av en specifik användare, men det här felet fortsätter att inträffa och jag vet inte varför. Jag har sökt efter ett svar och läst några frågor och svar på StackOverflow, men inget fungerade, och därför bestämde jag mig för att be om hjälp.

Undantag har inträffat: QueryFailedError: Okänd kolumn 'NaN' i 'where-klausul' vid QueryFailedError.TypeORMError [som konstruktor] (D:\Pessoal\Vinicius\TCC\hexperience-api\src\error\TypeORMError.ts:7:9) vid ny QueryFailedError (D:\Pessoal\Vinicius\TCC\hexperience-api\src\error\QueryFailedError.ts:9:9) vid Query.onResult (D:\Pessoal\Vinicius\TCC\hexperience-api\src\driver\mysql\MysqlQueryRunner.ts:195:37) vid Query.execute (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\commands\command.js:31:14) vid PoolConnection.handlePacket (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\connection. js:456:32) vid PacketParser.onPacket (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\connection.js:85:12) vid PacketParser.executeStart (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\packet_parser.js:75:16) vid Socket. (D:\Pessoal\Vinicius\TCC\hexperience-api\node_modules\mysql2\lib\connection.js:92:25) vid Socket.emit (events.js:315:20) vid addChunk (internal/streams/readable.js:309:12) vid readableAddChunk (internal/streams/readable.js:284:9) vid Socket.Readable.push (internal/streams/readable.js:223:10) vid TCP.onStreamRead (internal/stream_base_commons.js:188:23) vid TCP.callbackTrampoline (internal/async_hooks.js:131:14)

Här är koden:

User.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;

Avtal.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;

AvtalRepository.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;

ListUserAppointmentsService.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;

Svar #1

i nestjs har jag detta problem också,
det är min kod

@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);
  }
}

när findAll-metoden i tjänsten visar framgång har categoryOptions-metoden fel som du beskrev ovan,

jag vet inte varför, enda skillnaden är i kontrollern,
felkod är (Obs: @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()
  }

}

Lösningen är att lägga Options-metoden högst upp, felet är fixat, jag vet inte varför, hoppas det hjälper andra

framgångskod:

@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);
  }

}