I am looking to create a TypeORM query that pulls data from the database. Specifically, I want to retrieve all clients who have made a purchase but have not initiated a return. Here is the structure of the database:
Clients:
- Id (Int, primary column)
- Purchase_Id (only one purchase possible for a client; refers to Id of Purchases; nullable)
Purchases:
- Id (Int, primary column)
Returns:
- Id (Int, primary column)
- Purchase_Id (several returns per purchase are possible; refers to Id of Purchases)
To fetch all clients along with their purchases and returns, I have successfully used this code:
let query = this.clientRepo
.createQueryBuilder('client')
.select()
.leftJoinAndSelect('client.purchase', 'purchase')
.leftJoinAndSelect('purchase.returns', 'returns');
However, my challenge now is to apply a filter to only display customers who have initiated a return. This is where I encountered some errors. Here are the attempts I made and the corresponding issues:
query = query.where('purchase.returns IS NULL');
(Error: QueryFailedError: Error: Invalid column name 'returns'.)
query = query.where('client.purchase IS NOT NULL and purchase.returns IS NULL');
(Error: Cannot query across one-to-many for property returns)
query = query.where('purchase.returns IS (:...returnValues)', {returnValues: []});
(Error: QueryFailedError: Error: Invalid usage of the option NEXT in the FETCH statement.)
I am relatively new to TypeORM. Any advice on how to solve this issue would be greatly appreciated! Thank you.