QueryBuilder with TypeORM to return an entire object
I'm creating an API with NodeJS, TypeORM and MySQL.
I'm trying to return data from my establishments, along with calculating the average rating for each establishment. To be able to do this using TypeORM, the best way I found was using Query Builder.
The problem is that when I search for data with QueryBuilder, my return is something like:
{
"uid": "4a98db92-ab5f-4ef4-bad5-8daeb33d3886"
"categoryUid": "3ca5c694-12c6-4e94-9537-93d41cd32c3d"
"cd_establishment": 1
"nm_establishment": "Market"
}
However, I would need it to return the entire object with the associated children. And this doesn't happen, for example:
[
{
"uid": "4a98db92-ab5f-4ef4-bad5-8daeb33d3886",
"cd_establishment": 8,
"nm_establishment": "Fresh Market",
"category": {
"categoryUid": "3ca5c694-12c6-4e94-9537-93d41cd32c3d"
"cd_category": 1
"nm_category": "Market"
}
}
]
From what I understand, using query builder, the fields that have relationships in my table only return their value for each row returned. And I would need it to return something like an array. For each establishment row, the associated category column also returns its object.
What is the best way for this to work?
** Note: I believe this is important when returning an entire object to the front end in Angular**
When I make the call to the controller directly to "all"
await this.estabService.getAll();
The return structure of the all method is as follows:
(method) BaseService<EstablishmentModel>.getAll(): Promise<IResultHttp>
And when I make the request by calling my method that contains the Query Builder, it looks like this:
await this.estabService.getAllOrderedByRating();
the return structure is as follows:
(method) EstablishmentService.getAllOrderedByRating(): Promise<IResultHttp>
Note that this way we do not have the same return 'type' as in getAll "BaseService"
Below is my structure for better understanding.
/********************* ENTITIES *************************/
@Entity({name: 'establishment'})
export class Establishment extends BaseEntity {
@Unique('cd_establishment_uk', ['cd_establishment'])
@Column()
@Generated('increment')
cd_establishment: number
@ManyToOne(() => Category, { eager: true })
category: Category
@Column({type: 'varchar', length: 200})
nm_establishment: string
//... other attributes
}
@Entity({name: 'rating'})
export class Rating extends BaseEntity {
@Unique('cd_rating_uk', ['cd_rating'])
@Column()
@Generated('increment')
cd_rating: number
@ManyToOne(() => Order, { eager: true })
order: Order
@Column({type: 'int'})
rating: number
}
@Entity({name: 'order'})
export class Order extends BaseEntity {
@Unique('cd_order_uk', ['cd_order'])
@Column()
@Generated('increment')
cd_order: number
@ManyToOne(() => Client, { eager: true })
client: Client
@ManyToOne(() => Establishment, { eager: true })
establishment: Establishment
@CreateDateColumn({type: "timestamp"})
dt_order: Date
}
export abstract class BaseEntity {
@PrimaryGeneratedColumn("uuid")
uid: string
}
/********************* CONTROLLER *************************/
private _establishmentRepository = getRepository(Establishment);
async getAllOrderedByRating() {
const queryBuilder = this._establishmentRepository
.createQueryBuilder('establishment')
.leftJoin('order', 'o', 'establishment.uid = o.establishmentUid')
.leftJoin('rating', 'r', 'o.uid = r.orderUid')
.select([
'establishment.*',
'AVG(r.rating) as average',
])
.groupBy('establishment.cd_establishment')
.orderBy('average', 'DESC');
//const sql = queryBuilder.getSql();
return await queryBuilder.getRawMany();
}
Looking inside TypeORM, I checked this possibility of using JoinOptions. But I don't know if I managed to get the result I wanted. Several errors occurred. However, it may be an approach to consider if someone knows better how this works.