SQL Join with typeorm

Question

Look at this, I have the following structure of entities:

Entity/Ticket.ts

import {Entity, PrimaryColumn, Column, OneToMany, ManyToOne, OneToOne, JoinColumn} from "typeorm";
import { Academia } from "./Academia";
import { TicketInteracao } from "./TicketInteracao";
import { TicketTipo } from "./TicketTipo";
import { Usuario } from "./Usuario";

@Entity()
export class Ticket {

    @PrimaryColumn('uuid')
    id: string;

    @ManyToOne(type => Academia, academia => academia.tickets)
    academia: Academia;

    @ManyToOne(type => Usuario, usuario => usuario.tickets)
    usuario: Usuario;

    @ManyToOne(type => TicketTipo, tipo => tipo.tickets)
    tipo: TicketTipo;

    @OneToMany(type => TicketInteracao, interacoes => interacoes.ticket, {
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
    })
    interacoes: TicketInteracao[];

    @Column()
    titulo: string;

    @Column({ type: 'date' })
    data_abertura: string;

    @Column({ type: 'time' })
    hora_abertura: string;

    @Column({ type: 'date', nullable: true, default: null })
    data_fechamento: string;

    @Column({ type: 'time', nullable: true, default: null })
    hora_fechamento: string;
}

Entity/Ticketinteracao.ts

import {Entity, PrimaryColumn, Column, ManyToOne} from "typeorm";
import { Ticket } from "./Ticket";
import { Usuario } from "./Usuario";

@Entity()
export class TicketInteracao {

    @PrimaryColumn('uuid')
    id: string;

    @ManyToOne(type => Ticket, ticket => ticket.interacoes, {
        cascade: true
    })
    ticket: Ticket;

    @ManyToOne(type => Usuario, usuario => usuario.interacoes)
    usuario: Usuario;

    @Column({ type: 'text' })
    descricao: string;

    @Column({ type: 'date' })
    data: string;

    @Column({ type: 'time' })
    hora: string;

    @Column({ type: 'date', nullable: true, default: null })
    arquivo: string;
}

Entity/Usuario.ts

import {Entity, PrimaryColumn, Column, OneToMany, ManyToMany, ManyToOne} from "typeorm";
import { Academia } from "./Academia";
import { Ticket } from "./Ticket";
import { TicketInteracao } from "./TicketInteracao";
import { UsuarioContato } from "./UsuarioContato";
import { UsuarioTipo } from "./UsuarioTipo";

@Entity()
export class Usuario {

    @PrimaryColumn('uuid')
    id: string;

    @Column({ length: 50 })
    nome: string;

    @Column({ length: 50 })
    login: string;

    @Column({ length: 255 })
    senha: string;

    @Column()
    super: number;

    @Column()
    ativo: number;

    @Column()
    acesso: number;

    @ManyToOne(type => Academia, academia => academia.usuarios, {
        cascade: true
    })
    academia: Academia;

    @ManyToOne(type => UsuarioTipo, tipo => tipo.usuarios, {
        cascade: true
    })
    tipo: UsuarioTipo;

    @OneToMany(type => UsuarioContato, contatos => contatos.usuario, {
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
    })
    contatos: UsuarioContato[];

    @OneToMany(type => Ticket, tickets => tickets.usuario)
    tickets: Ticket[];

    @OneToMany(type => TicketInteracao, interacoes => interacoes.usuario)
    interacoes: TicketInteracao[];
}

So a user has multiple tickets and a ticket has multiple interactions and those interactions also have the user who made them, I just need to put together a query where I take all the tickets of a particular user and also get the LAST interaction of that ticket and the user who did this interaction. If possible also, take this user’s type, follow the Entity: Entity/User type.ts

import {Entity, PrimaryColumn, Column, OneToMany} from "typeorm";
import { Usuario } from "./Usuario";

@Entity()
export class UsuarioTipo {

    @PrimaryColumn('uuid')
    id: string;

    @Column({ length: 50 })
    descricao: string;

    @OneToMany(type => Usuario, usuario => usuario.tipo, {
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
    })
    usuarios: Usuario[];
}

Only I can’t at all, I can get the ticket, the interactions of that ticket and even get the user who did the interaction, But there’s no way that I can take just the last interaction and there’s no way that I can take the kind of user that did this interaction that’s another Entity. This is my query currently:

   let query = createQueryBuilder(Ticket, 'ticket')
      .leftJoinAndSelect('ticket.tipo', 'ticket_tipo')
      .leftJoinAndSelect('ticket.interacoes', 'ticket_interacao')
      .leftJoinAndSelect('ticket_interacao.usuario', 'usuario')
      .orderBy('ticket_interacao.data', 'DESC')
      .orderBy('ticket_interacao.hora', 'DESC')

and this is the returned JSON:

[
  {
    "id": "0b8c5b62-1eb2-4b28-9fb9-97dc680a3c02",
    "titulo": "Titulo",
    "data_abertura": "2021-03-07",
    "hora_abertura": "18:44:21",
    "data_fechamento": null,
    "hora_fechamento": null,
    "tipo": {
      "id": "17556594-c736-4b7c-9b4f-9a25e0e55ed7",
      "descricao": "tipo"
    },
    "interacoes": [
      {
        "id": "1e0bc060-ceb7-4722-898a-9bb22685afd0",
        "descricao": "Descricao",
        "data": "2021-03-07",
        "hora": "18:45:52",
        "arquivo": null,
        "usuario": {
          "id": "0739f286-ecf5-4579-8efe-3062941a5e7f",
          "nome": "usuario",
          "login": "login",
          "senha": "senha",
          "super": 1,
          "ativo": 1,
          "acesso": 1
        }
      },
      {
        "id": "188bf283-8a0d-472f-83b9-2755d11016fe",
        "descricao": "Descricao",
        "data": "2021-03-07",
        "hora": "18:44:21",
        "arquivo": null,
        "usuario": {
          "id": "a391dc7f-33af-4798-a41c-374a06f2f73e",
          "nome": "usuario",
          "login": "login",
          "senha": "senha",
          "super": 1,
          "ativo": 1,
          "acesso": 1
        }
      }
    ]
  }
]