How can I perform a join with a subquery using Mikro-ORM?

Viewed 460

So this is the SQL I'm trying to emulate currently.

SELECT * FROM direct_messages AS T
INNER JOIN (SELECT sender_id, receiver_id, MAX(sent_at) AS sent_at FROM direct_messages WHERE (sender_id = '2' OR sender_id = '3') AND (receiver_id = '3' OR receiver_id = '2') GROUP BY sender_id, receiver_id) A
ON A.sender_id = T.sender_id AND A.sent_at = T.sent_at;

This is the entity for the table

@ObjectType()
@Entity()
export class DirectMessages {
  @Field(() => ID)
  @PrimaryKey()
  id!: number;

  @Field(() => String)
  @Property()
  senderID!: string;

  @Field(() => String)
  @Property()
  receiverID!: string;

  @Field(() => String)
  @Property()
  message!: string;

  @Field(() => Date, { nullable: true })
  @Property({ nullable: true })
  readAt?: Date;

  @Field(() => Date)
  @Property()
  sentAt: Date = new Date();

  @Field(() => Date)
  @Property({ onUpdate: () => new Date() })
  updatedAt: Date = new Date();
}

This is what I've written in the queryBuilder so far

const subQuery = await er
      .createQueryBuilder('DirectMessages')
      .select(['sender_id', 'receiver_id', 'MAX(sent_at)'])
      .where({ $or: [{ senderID: '2' }, { senderID: '3' }] })
      .andWhere({ $or: [{ receiverID: '3' }, { receiverID: '2' }] })
      .groupBy(['sender_id', 'receiver_id'])
      .getKnexQuery();

    const queryResults = await er
      .createQueryBuilder('DirectMessages')
      .select('*')
      .withSubQuery(subQuery, 'A')
      .join('A.sender_id', 'T', undefined, 'innerJoin')
      .execute('all', true);

The subQuery is right but I have no idea how to then join that temporary table to the DirectMessages table. I appreciate the help!

0 Answers
Related