How can i write these query in typeorm with queryBuilder?

Question

this is mysql query that i want to use in typeorm

   select name,items.invoiceId,CONCAT(DAY(invoices.created_At),' ',
        SUBSTR(MONTHNAME(invoices.created_At),1,3),' ',
        Year(invoices.created_At)) as 'Date' 
         from client
          inner join invoices on invoices.clientId = client.id
          inner join items on items.invoiceId = invoices.id
          group by items.invoiceId;

But i can use with this.repo.query('raw_query'); But i want to know how to use it with query builder;

three tables client,invoices,items relations;

Answer 1

I HAVE GOT THE ANSWER

for using function of mysql like concat,sum,avg we should use getRawMany() or getRawOne()

const dateqry =
      'CONCAT(SUBSTR(MONTHNAME(invoices.created_at),1,3)," ",DAY(invoices.created_at)," ",YEAR(invoices.created_at))';
    this.invoicesRepo
          .createQueryBuilder('invoices')
          .innerJoinAndSelect('invoices.client', 'client')
          .innerJoin('invoices.items', 'items')
          .select(['invoices.id', 'client.name'])
          .addSelect(dateqry, 'created_at')
          .groupBy('items.invoiceId')
          .orderBy('invoices.created_at', 'ASC')
          .getRawMany();

Output that i wanted

   [
      {
        "invoices_id": 1,
        "client_name": "Goku",
        "created_at": "Oct 13 2022"
      },
      {
        "invoices_id": 2,
        "client_name": "Khabib",
        "created_at": "Oct 14 2022"
      },
      {
        "invoices_id": 3,
        "client_name": "Goku",
        "created_at": "Oct 14 2022"
      }

]