TypeORM query related oneToMany values in find()

Question

I have the following SQL Query which I want to replace with a ORM Mapping:

SELECT primeid, name 
FROM primes p INNER JOIN seconds s ON s.primeid = p.primeid 
WHERE s.insertdate >= '2020-01-01 and s.insertdate <= '2020-01-31'

I have the following entities:

@Entity("primes") 
export class PrimeEntity {
        @PrimaryColumn({name:'primeid'})
        primeId: number;

        @Column()
        name: string;

        @OneToMany('SecondEntity','prime')
        seconds: SecondEntity[];
}

@Entity("seconds") 
export class SecondEntity {
        @PrimaryColumn({name:'secondid'})
        secondId: number;

        @Column({name: 'insertdate'})
        insertDate: Date;

        @ManyToOne('PrimeEntity', 'seconds')
        @JoinColumn('primeid')
        prime: PrimeEntity;
}

The following code should return nearly the same as the SQL Query at the beginning:

primeRepository.find({
    relations: ['seconds'],
    where: {
        seconds: {
            insertDate: Between('2020-01-01', '2020-01-31')
        }
    }

});

it should return

[
PrimeEntity {primeId: 1, name: 'test', seconds: [SecondEntity {secondId: 1, insertDate: '2020-01-01'}]},
...
] 

But instead, i get the following error message:

EntityColumnNotFound: No entity column "seconds" was found.

Maybe someone could help me...

thank you and best greetings Greg

Answer 1

I was looking for something along those lines where I can use the custom repository I created and query similar to your example above. I ended up having to do something like this instead:

    primeRepository.createQueryBuilder("prime")
    .innerJoin("prime.seconds", "s")
    .where("s.insertDate >= :startDate and s.insertDate <= :endDate", {startDate: '2020-01-01', endDate: '2020-01-31'})
    .getMany()

The sample above is something that I wrote based on your query and what I did to get it working, not sure if that will drop and work "as-is", you might have to tweak it. I just wanted to help get you in the right direction if you were still in need of a solution. I hope that in a future version they will address this and make it more intuitive.