How to rewrite complex raw query with query builder?

[valllentinnaa]

Hi,

I'm trying to rewrite a complex query with a lot of aggregations with the query builder, but without any luck till now 😔

Here it is:

let subquery = `SELECT c.*, SUM(a.ftd) as total_ftd, SUM(a.qualified_ftds) as total_qualified_ftds, SUM(a.nrc) as total_nrc, SUM(a.total_deposits_amount) as total_deposits, SUM(a.total_withdrawals_amount) as total_withdrawals, SUM(a.cpa_amount) as total_cpa_amount, SUM(a.revenue_share_amount) as total_revenue_share_amount, SUM(a.ttl_amount) as total_ttl_amount, SUM(a.ngr) as total_ngr FROM activities AS a LEFT OUTER JOIN customers as c ON c.id = a.customer_id WHERE a.date >= :startDate AND a.date <= :endDate ${
        auth?.user?.isManager
          ? `AND c.brand_id IN (${
              auth?.user?.visibleBrands.length ? auth?.user?.visibleBrands.join() : 0
            })`
          : ``
      } GROUP BY c.id`

      const query = `SELECT u.id, u.username,
      SUM(temp.total_ftd) as customers_total_ftd,
      SUM(temp.total_qualified_ftds) as customer_total_qualified_ftds,
      SUM(temp.total_nrc) as customers_total_nrc,
      SUM(temp.total_deposits) as customers_total_deposits,
      SUM (temp.total_withdrawals) AS customers_total_withdrawals,
      SUM(temp.total_cpa_amount) as cpa_amount,
      SUM(temp.total_revenue_share_amount) as rs_cost,
      SUM(temp.total_ttl_amount) as ttl_cost, SUM(temp.total_ngr) as ngr
      FROM (${subquery}) as temp LEFT OUTER JOIN users as u on u.id = affiliate_id
      ${username ? `WHERE u.username = :username` : ``}
      GROUP BY u.id
      LIMIT :perPage
      OFFSET :offset`

      const results = await Database.rawQuery(query, {
        startDate,
        endDate,
        username,
        perPage,
        offset,
      })

Version: adonis 5.4.0
Do you have any idea if this can be rewritten at all?

[RomainLanz]

Hey @valllentinnaa! 👋🏻

You are better off continuing to use raw queries than migrating to the Query Builder for that kind of query.
Any specific reason to switch to the query builder?

You may want to use database views to simplify your query.