title: Typeorm querybuilder join latest row
body: <p>I'm using NestJS with TypeORM</p>
  
  <p>I got two entities with relation:</p>
  
  <pre><code>export class Device {
      @PrimaryGeneratedColumn()
      id: number;
  
      @Column("macaddr")
      mac: string;
  
      @OneToMany(type =&gt; DeviceStatus, deviceStatus =&gt; deviceStatus.mac)
      @JoinColumn()
      status: DeviceStatus[]
  
      @Column()
      created: Date;
  }
  
  export class DeviceStatus {
      @PrimaryGeneratedColumn()
      id: number;
  
      @ManyToOne(type =&gt; Device, device =&gt; device.mac)
      @JoinColumn({ name: "mac", referencedColumnName: "mac" })
      mac: Device;
  
      @Column()
      deviceState: number;
  
      @Column()
      created: Date;
  }
  </code></pre>
  
  <p>I want to get Device but only it's latest DeviceStatus on its <code>status</code> property.</p>
  
  <p>Now I am doing it like this:</p>
  
  <pre><code>const deviceQuery: Device = await this.deviceRepository
                          .createQueryBuilder("device")
                          .where('device.id = :id AND "device"."userId" = :user', {id: id, user: user.id})
                          .getOne();
  
  
  const statusQuery: DeviceStatus = await this.deviceStatusRepository.createQueryBuilder("status")
                          .where('status.mac = :mac', { mac: deviceQuery.mac })
                          .orderBy('created', "DESC")
                          .limit(1)
                          .getOne();
  
  deviceQuery.status = [statusQuery];
  </code></pre>
  
  <p>How can I do this using just one typeorm queryBuilder query?</p>
  
  <p>I already tried this, but it doesn't map <code>status</code> property to Device, <code>.execute()</code> gets all DeviceStatus properties but it wont map the entities.</p>
  
  <pre><code>const deviceQuery: Device = await this.deviceRepository
                      .createQueryBuilder("device")
                      .leftJoinAndSelect(subquery =&gt; {
                          return subquery
                              .from(DeviceStatus, "status")
                              .innerJoin(subquery =&gt; {
                                  return subquery
                                      .select(["status2.mac"])
                                      .addSelect("MAX(created)", "lastdate")
                                      .from(DeviceStatus, "status2")
                                      .groupBy("status2.mac")
                              }, "status3", 'status.created = "status3"."lastdate"')
                      }, "status", 'device.mac = "status"."mac"')
                      .where('device.id = :id AND "device"."userId" = :user', {id: id, user: user.id})
                      .getOne();
  </code></pre>