I have two entities one is car and another one is carAvailability
import { Entity, Column, PrimaryGeneratedColumn, OneToMany } from 'typeorm';
import { CarAvailability } from 'src/car-availabilitys/car-availability.entity';
@Entity('cars')
export class Car {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@OneToMany(() => CarAvailability, (carAvailability) => carAvailability.car, {
eager: true,
cascade: true,
})
availabilities: CarAvailability[];
}
I am trying to add a service that queries and filters cars based on the availabilities. In My Service and tried two ways:
Method 1 with repo functions:
async test () {
const startDateTime = '2012-04-24 02:25:43.511';
return await this.repo.find({
relations: ['availabilities'],
where: {
availabilities: {
start_date_time: startDateTime
}
}
});
}
Method 2 with query builder:
async test () {
const startDateTime = '2012-04-24 02:25:43.511';
return this.repo.createQueryBuilder('cars')
.innerJoin('cars.availabilities', 'car_availabilities')
.where("cars.availabilities.start_date_time = :startDateTime", { startDateTime })
.getMany();
}
Method 1 error:
Error: Cannot query across one-to-many for property availabilities
Method 2 error:
QueryFailedError: missing FROM-clause entry for table "availabilities"
I feel like I am missing something but I am not sure. Have referred both NestJS and TypeORM docs but can't seem to figure out what went wrong.
Using Method 2, because you aliased car.availabilities as car_availabilities, your where clause should use the alias name:
car_availabilities.start_date_time
not:
cars.availabilities.start_date_time
Using Method 1 repo functions with nested query builder:
async test () {
const startDateTime = '2012-04-24 02:25:43.511';
return await this.repo.find({
relations: ['availabilities'],
where: (qb) => {
qb.where('availabilities.start_date_time = :startDateTime', {
startDateTime
});
}
});
}
Remove cascade from oneToMany side and put:
{
cascade: true,
onDelete: 'CASCADE',
onUpdate:'CASCADE'
}
on manyToOne side.
And if you going to use relations: ['availabilities'] you can remove eager: true.