Cannot query across one-to-many for property NestJS and TypeORM

Question

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.

DemiPixel · Accepted Answer · 2022-06-20 21:29:32Z

7

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

edited Jun 20, 2022 at 21:29

answered Oct 7, 2021 at 23:18

DemiPixel

1,9111 gold badge12 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Audwin Oyong · Accepted Answer · 2021-11-22 21:22:06Z

3

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

edited Nov 22, 2021 at 21:22

Audwin Oyong

2,5273 gold badges21 silver badges37 bronze badges

answered Oct 27, 2021 at 17:13

rhutchison

412 bronze badges

Comments

Audwin Oyong · Accepted Answer · 2021-11-20 15:35:46Z

0

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.

edited Nov 20, 2021 at 15:35

Audwin Oyong

2,5273 gold badges21 silver badges37 bronze badges

answered Nov 19, 2021 at 23:32

JsHitachi

793 silver badges9 bronze badges

Comments

Edmond Alimehmeti · Accepted Answer · 2023-04-04 14:08:58Z

-3

bro add a .then() after the async function like in my code

return await this.repository.find({relations: ['availabilities'])
.then((cars) => cars.filter((car) => car.start_date_time === startDateTime));

edited Apr 4, 2023 at 14:08

answered Mar 29, 2023 at 14:36

Edmond Alimehmeti

112 bronze badges

3 Comments

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

solidau Over a year ago

This is not a preferred method because you are doing the filtering in code instead of in SQL (not very efficient). This method always returns all results and then loops thru them to filter the ones you want.

Edmond Alimehmeti Over a year ago

You're right but this is the only solution i could find

Collectives™ on Stack Overflow

Cannot query across one-to-many for property NestJS and TypeORM

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related