TypeORM Inner Join Same Table

Question

I have a question I could not find an answer to online.

I have a single table and I am looking to remove duplicate entries:

My raw SQL query is:

#Perform delete duplicate logs with same order_id and message
DELETE t1 FROM order_logs t1
INNER  JOIN order_logs t2
WHERE t1.log_id < t2.log_id
AND t1.order_id = t2.order_id
AND t1.message = t2.message
AND t1.order_id = @ORDER_ID
AND t2.order_id = @ORDER_ID;

Here is my @Entity:

@Entity('customer_logs')
export class CustomerLog {
    @PrimaryGeneratedColumn()
    readonly log_id: number

    @Column()
    @ManyToOne(() => Customer, (customer) => customer.id, {
        onDelete: 'CASCADE',
    })
    @JoinColumn({ name: 'customer_id' })
    readonly customer_id: number

    @Column({ default: null, nullable: true })
    @ManyToOne(() => User, (user) => user.user_id)
    @JoinColumn({ name: 'user_id' })
    readonly user_id?: number

    @Column()
    @IsString()
    @MinLength(1)
    @MaxLength(255)
    readonly message: string

    @Column()
    @IsEnum(CustomerLogType)
    readonly type: CustomerLogType

    @Column({ type: 'json', default: null, nullable: true })
    @IsObject()
    readonly json?: object

    @CreateDateColumn()
    @Expose({ groups: ['ADMIN', 'OWNER'] })
    readonly created_at: Date

    @UpdateDateColumn()
    @Expose({ groups: ['ADMIN', 'OWNER'] })
    readonly updated_at: Date

    constructor(partial: Partial<CustomerLog>) {
        Object.assign(this, partial)
    }
}

Do you know how I would perform this query using TypeORM via the QueryBuilder?

Answer 1

If you already have the raw SQL query right, you can just use that in code without the need of constructing it with QueryBuilder.

import { getConnection } from "typeorm";
.......{

await getConnection().query("DELETE t1 FROM order_logs t1
INNER  JOIN order_logs t2
WHERE t1.log_id < t2.log_id
AND t1.order_id = t2.order_id
AND t1.message = t2.message
AND t1.order_id = @ORDER_ID
AND t2.order_id = @ORDER_ID;"
    );

}