{ "message": "select count(distinct(\"e0\".\"id\")) as \"count\" from \"subscriber\" as \"e0\" left join \"message\" as \"e2\" on \"e0\".\"id\" = \"e2\".\"recipient_id\" where \"e1\".\"date_clicked\" < '2021-03-05T20:47:01.090Z' and \"e0\".\"owner_id\" = 27 and not (\"e2\".\"date_clicked\" > '2021-03-05T20:47:01.090Z' and \"e0\".\"owner_id\" = 27) - missing FROM-clause entry for table \"e1\"" }

@Entity()
export class Account extends BaseEntity {
  @PrimaryKey()
  id!: number;

  @OneToMany({
    entity: () => Subscriber,
    mappedBy: 'owner',
    orphanRemoval: true,
  })
  subscribers = new Collection<Subscriber>(this);
...
}

@Entity()
export class Subscriber extends BaseEntity {
  @PrimaryKey()
  id!: number;

  @OneToMany({
    entity: () => Message,
    mappedBy: 'recipient',
    orphanRemoval: true,
    orderBy: { createdAt: QueryOrder.ASC },
  })
  messages = new Collection<Message>(this);

  @ManyToOne(() => Account)
  owner!: Account;
...
}

@Entity()
export class Message extends BaseEntity {
  @PrimaryKey()
  id!: number;

  @Property({ nullable: true })
  dateClicked?: Date;

  @ManyToOne(() => Subscriber)
  recipient!: Subscriber;
...
}

let owner: Account = ...
let date: Date = ...

let filterAfter = { messages: { dateClicked: { $gt: date } }};
Object.assign(filterAfter, { owner });

let filterBefore = { messages: { dateClicked: { $lt: date } } };
Object.assign(filterBefore, { owner });

filter  = {
  $and: [
    filterAfter,
    { $not: filterBefore }
  ]
}

select 
	count(distinct("e0"."id")) as "count" 
from 
	"subscriber" as "e0" left join 
	"message" as "e2" on "e0"."id" = "e2"."recipient_id" 
where 
	"e1"."date_clicked" < $date and 
	"e0"."owner_id" = $owner and 
	not ("e2"."date_clicked" > $date and "e0"."owner_id" = $owner) 

select 
	count(distinct("e0"."id")) as "count" 
from 
	"subscriber" as "e0" left join 
	"message" as "e1" on "e0"."id" = "e1"."recipient_id" 
where 
	"e1"."date_clicked" < $date and 
	"e0"."owner_id" = $owner and 
	not ("e1"."date_clicked" > $date and "e0"."owner_id" = $owner)