TypeORM Conditional Selection with OneToMany

Question

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:

• 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)

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!

Answer 1

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

Answer 2

You can't change the Window transparency using CSS, but there are packages that add that functionality e.g. atom-transparency