I need to write a TypeORM code to generate a query that selects all clients who made a purchase but did not initiate a return. This is the database structure:
Clients:
Purchases:
Returns:
This is what I use to get all the clients, their purchases and returns. This works:
let query = this.clientRepo
.createQueryBuilder('client')
.select()
.leftJoinAndSelect('client.purchase', 'purchase')
.leftJoinAndSelect('purchase.returns', 'returns');
Now, I am trying to add a filter to only see customers who initiated a return. This is what I tried and what errors I got:
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 new to TypeORM. How to resolve this problem? Thank you very much in advance!
Ok, I found an answer. If anyone needs it for the future:
query = query.where('client.purchase IS NOT NULL AND returns.Id IS NULL');
The reason you write "returns.Id", but not "purchase.Id" is that "purchase" is an alias that already refers to the Id column. And a column can be null or non-null.
Unlike purchase, "returns" alias refers to a table, not a column. A table can't be null or non-null, so you need to get a column, like "Id".
From what I understand, this difference in references comes from the client->purchase relation being one-to-one, but purchase->returns being many-to-one