TypeORM QueryBuilder Limit Join to One Record

Question

The following query gets all clients and their associated products. It also joins each product's ProductSnapshot which contains historical closing inventories for each product. I want to limit the ProductSnapshot join to one record which could be accomplished by ordering by id 'DESC' and limiting to one record per product. What's the best way to achieve this?

async findClientsProducts() {
  return await this.clientsRepository
    .createQueryBuilder('client')
    .leftJoinAndSelect('client.products', 'product')
    .leftJoinAndSelect('product.productSnapshots', 'productSnapshot')
    .getMany();
}

EDIT:

The selected answer got me most of the way there by modifying the condition to include an order clause and fixing the ON clause to reference the appropriate fields. ordering by productSnapshot.id ensures you get the latest snapshot row for that product.

async findClientsProducts() {
  return await this.clientsRepository
    .createQueryBuilder('client')
    .leftJoinAndSelect('client.products', 'product')
    .leftJoinAndMapOne(
      'product.productSnapshot',
      ProductSnapshot,
      'productSnapshot',
      'productSnapshot.product_id = product.id ORDER BY productSnapshot.id DESC',
    )
    .getMany();
}

Answer 1

you should use leftJoinAndMapOne

.leftJoinAndMapOne(
          'product.productSnapshot',
          ProductSnapshot,
          'productSnapshot',
          'product.productSnapshotId = productSnapshot.id',
        )