Here is the current Entity structure:
entity/Ticket.ts
import {Entity, PrimaryColumn, Column, OneToMany, ManyToOne, OneToOne, JoinColumn} from "typeorm";
import { Gym } from "./Gym";
import { TicketInteraction } from "./TicketInteraction";
import { TicketType } from "./TicketType";
import { User } from "./User";
@Entity()
export class Ticket {
@PrimaryColumn('uuid')
id: string;
@ManyToOne(type => Gym, gym => gym.tickets)
gym: Gym;
@ManyToOne(type => User, user => user.tickets)
user: User;
@ManyToOne(type => TicketType, type => type.tickets)
type: TicketType;
@OneToMany(type => TicketInteraction, interactions => interactions.ticket, {
onDelete: 'CASCADE',
onUpdate: 'CASCADE'
})
interactions: TicketInteraction[];
@Column()
title: string;
}
entity/TicketInteraction.ts
import {Entity, PrimaryColumn, Column, ManyToOne} from "typeorm";
import { Ticket } from "./Ticket";
import { User } from "./User";
@Entity()
export class TicketInteraction {
@PrimaryColumn('uuid')
id: string;
@ManyToOne(type => Ticket, ticket => ticket.interacoes, {
cascade: true
})
ticket: Ticket;
@ManyToOne(type => User, user => user.interactions)
user: User;
@Column({ type: 'text' })
description: string;
@Column({ type: 'date' })
date: string;
@Column({ type: 'time' })
time: string;
}
entity/User.ts
import {Entity, PrimaryColumn, Column, OneToMany, ManyToMany, ManyToOne} from "typeorm";
import { Gym } from "./Academia";
import { Ticket } from "./Ticket";
import { TicketInteracao } from "./TicketInteracao";
import { UserType } from "./UserType";
@Entity()
export class Usuario {
@PrimaryColumn('uuid')
id: string;
@Column({ length: 50 })
name: string;
@ManyToOne(type => Gym, gym => gym.users, {
cascade: true
})
gym: Gym;
@ManyToOne(type => UserType, type => type.users, {
cascade: true
})
type: UserType;
@OneToMany(type => Ticket, tickets => tickets.user)
tickets: Ticket[];
@OneToMany(type => TicketInteraction, interactions => interactions.user)
interactions: TicketInteraction[];
}
entity/UserType.ts
import {Entity, PrimaryColumn, Column, OneToMany} from "typeorm";
import { User } from "./User";
@Entity()
export class UserType {
@PrimaryColumn('uuid')
id: string;
@Column({ length: 50 })
description: string;
@OneToMany(type => User, user => user.type, {
onDelete: 'CASCADE',
onUpdate: 'CASCADE'
})
user: User[];
}
In summary, each user has many tickets and each ticket has many interactions with users who created them. To retrieve all the tickets of a user, along with the last interaction made on each ticket by a user with their respective type, I am struggling to construct the query. Here is my current attempt:
let query = createQueryBuilder(Ticket, 'ticket')
.leftJoinAndSelect('ticket.interacoes', 'ticket_interacao')
.leftJoinAndSelect('ticket_interacao.usuario', 'usuario')
.orderBy('ticket_interaction.date', 'DESC')
.orderBy('ticket_interaction.time', 'DESC')
Here is the JSON output:
{
[
{
"id": "0b8c5b62-1eb2-4b28-9fb9-97dc680a3c02",
"title": "Title",
"interactions": [
{
"id": "1e0bc060-ceb7-4722-898a-9bb22685afd0",
"description": "Description",
"date": "2021-03-07",
"time": "18:45:52"
"user": {
"id": "0739f286-ecf5-4579-8efe-3062941a5e7f",
"name": "usuario"
}
},
{
"id": "188bf283-8a0d-472f-83b9-2755d11016fe",
"description": "Description",
"date": "2021-03-07",
"time": "18:44:21",
"usuario": {
"id": "a391dc7f-33af-4798-a41c-374a06f2f73e",
"nome": "usuario"
}
}
]
}
]
}
I am seeking assistance in constructing the complete query. Thank you!