CODEWITHSUNDEEP
node.jstypescriptnestjstypeorm
user3482304
user3482304

Reputation: 301

Typeorm querybuilder join latest row

I'm using NestJS with TypeORM

I got two entities with relation:

export class Device {
    @PrimaryGeneratedColumn()
    id: number;

    @Column("macaddr")
    mac: string;

    @OneToMany(type => DeviceStatus, deviceStatus => deviceStatus.mac)
    @JoinColumn()
    status: DeviceStatus[]

    @Column()
    created: Date;
}

export class DeviceStatus {
    @PrimaryGeneratedColumn()
    id: number;

    @ManyToOne(type => Device, device => device.mac)
    @JoinColumn({ name: "mac", referencedColumnName: "mac" })
    mac: Device;

    @Column()
    deviceState: number;

    @Column()
    created: Date;
}

I want to get Device but only it's latest DeviceStatus on its status property.

Now I am doing it like this:

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];

How can I do this using just one typeorm queryBuilder query?

I already tried this, but it doesn't map status property to Device, .execute() gets all DeviceStatus properties but it wont map the entities.

const deviceQuery: Device = await this.deviceRepository
                    .createQueryBuilder("device")
                    .leftJoinAndSelect(subquery => {
                        return subquery
                            .from(DeviceStatus, "status")
                            .innerJoin(subquery => {
                                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();

Upvotes: 1

Views: 11016

Answers (1)

Jordane
Jordane

Reputation: 643

You can achieve this by joining status 2 times. The second status join (next_status) condition should compare created date and be after the first joined status. Then just check if next_status is null, that means that there is no status younger than the first join

Code is probably a better explanation:

const device: Device = await this.deviceRepository
                    .createQueryBuilder("device")
                    .leftJoinAndSelect("device.status", "status")
                    .leftJoin("device.status", "next_status", "status.created < next_status.created")
                    .where("next_status.id IS NULL")

// device.status is the latest status

Upvotes: 14

Related Questions