How do I translate a MySQL query with a subquery and a UNION ALL to TypeORM to be used in NestJS?

Viewed 149

I would like to translate the query below into TypeORM:

SELECT 
    tr.Month as reporting_month,
    tr.Year as reporting_year,
    SUM(CASE WHEN tr.Type = 'D' THEN tr.Amount ELSE 0 END) As Gross_Subscription_Amount,
    SUM(CASE WHEN tr.Type = 'W' THEN tr.Amount ELSE 0 END) As Gross_Redemption_Amount,
    COUNT(DISTINCT u.id) as Number_of_Investors
    FROM (
      SELECT user_id, deposit.amount as Amount, YEAR(date_created) as YEAR, MONTHNAME(date_created) as Month, 'D' As 'Type' FROM deposit where status in ('APPROVED' , 'BOOKED', 'PENDING_APPROVAL')
      UNION ALL
      SELECT user_id, withdrawal.amount as Amount, YEAR(date_created) as YEAR, MONTHNAME(date_created), 'W' FROM withdrawal where status in ('APPROVED' , 'BOOKED', 'PENDING_APPROVAL')
      ) tr
    INNER JOIN user u ON u.id = tr.user_id
    WHERE Month = ?
    AND Year = ?
    GROUP BY tr.Year, tr.Month;

The problem is in the UNION ALL statement, I could not find a way for this subquery with a UNION to be translated to TypeORM.

0 Answers
Related