how to convert plain mysql sql query to typeorm

Question

I am migrating the existing express.js project into nest.js. And the problem I am facing right now is that the current project used MySQL and it is a plain SQL query, but I'm using TypeOrm for SQL queries. but I don't know how to implement relations between tables in TypeOrm. here is how i am doing

inference_result.entity.ts

import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from "typeorm";
import { Inference } from "./inference.entity";

@Entity('inference_result')
export class Inference_result {
@PrimaryGeneratedColumn()
result_id: number

@OneToOne(() => Inference, (inference) => inference.inference_id)
// @JoinColumn()
inference_id: Inference;

@Column()
result_type: string

@Column({type: 'varchar', length: 512, nullable: false})
s3_key: string
}

inference.entity.ts

import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';

@Entity('inference')
export class Inference {
@PrimaryGeneratedColumn({type: 'int',})
inference_id: number;

@Column()
inference_type: string;

@Column()
created_by: string;

@Column()
s3_key: string;

@Column({type: 'int' , nullable: true, default: 0})
progress: number

@Column({type: 'int', nullable: true, default: 0})
error_code: any;
 }

here is how im trying to catch the data

async getLink(id: number) {
  const response = await 
  this.inferenceResultRepo.createQueryBuilder('inference_result')
  .innerJoin('inference_result.inference_id', "inference", 
  "inference_result.inference_id = 
  inference.inference_id")
  .select('inference_result.s3_key', 
  'inference_result.result_type').where('inference.inference_id =:id' ,{id}).getOne()
} 

And the result im trying to get is

 CREATE TABLE `inference` (
 `inference_id` int NOT NULL AUTO_INCREMENT,
 `inference_type` varchar(20) NOT NULL,
 `created_by` varchar(21) NOT NULL,
 `s3_key` varchar(512) NOT NULL,
 `progress` float DEFAULT NULL,
 `error_code` int DEFAULT NULL,
  PRIMARY KEY (`inference_id`)
  );

  CREATE TABLE `inference_result` (
  `result_id` int NOT NULL AUTO_INCREMENT,
  `inference_id` int NOT NULL,
  `result_type` varchar(20) NOT NULL,
  `s3_key` varchar(512) NOT NULL,
  PRIMARY KEY (`result_id`),
  KEY `inference_id` (`inference_id`),
  CONSTRAINT `inference_result_ibfk_1` FOREIGN KEY (`inference_id`) REFERENCES 
  `inference` (`inference_id`)
  );

    const sql = 'SELECT inference_result.s3_key AS s3_key, 
    inference_result.result_type AS result_type FROM inference_result INNER JOIN 
    inference ON inference.inference_id = inference_result.inference_id WHERE 
    inference.inference_id = ?';
    const params = [inference_id, session_id];

I would appreciate any suggestion from u guys. btw I'm super new to nest.js so I might be doing any silly mistakes:)

Not actually. the problem is occurring when creating the inference_result table.

and it looks like this

result_id result_type 
s3_key 
inferenceIdInferenceId 

. here

inferenceIdinferenceId column should be like just inference_id but somehow it's not being created in this way

Answer 1

You should always take a look at the documentation for typenorm and start form the example before experimenting

import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from "typeorm";
import { Inference } from "./inference.entity";

@Entity('inference_result')
export class Inference_result {
@PrimaryGenerate

@OneToOne(type => Inference) @JoinColumn() 
inference_id: Inference;

@Column()
result_type: string

@Column({type: 'varchar', length: 512, nullable: false})
s3_key: string
}