如何将此SQL转换为Laravel雄辩？

Question

I have the following SQL

SELECT C.CUOCODE, C.NAME, COUNT(*) TOTAL_PAYMENT, SUM(P.AMOUNT) TOTAL_AMOUNT
FROM TAX_PAYMENT P
INNER JOIN TAX_CHECKPOINT C ON C.CUOCODE = REGEXP_SUBSTR(P.INVOICEID, 'R....')
WHERE DELETED = 0 AND TO_CHAR(TXTIME,'YYYY-MM-DD') = '2018-04-24'
GROUP BY  C.CUOCODE, C.NAME
ORDER BY TOTAL_AMOUNT DESC;

How can i convert to laravel eloquent, i have "Payment" model (table TAX_PAYMENT) with "paymentid" as primary key.

Answer 1

DB::table('TAX_PAYMENT as P')
    ->select([
        'C.CUOCODE',
        'C.NAME',
        DB::raw('COUNT(*) AS TOTAL_PAYMENT'),
        DB::raw('SUM(P.AMOUNT) AS TOTAL_AMOUNT'),
    ])->Join('TAX_CHECKPOINT C', 'C.CUOCODE', '=', DB::raw('REGEXP_SUBSTR(P.INVOICEID,'R....')'))
    ->where('DELETED', 0)
    ->where(DB::raw("TO_CHAR(TXTIME,'YYYY-MM-DD')"), '2018-04-24')
    ->groupBy('C.CUOCODE')
    ->groupBy('C.NAME')
    ->orderBy('TOTAL_AMOUNT', 'desc')
    ->toSql();

output

SELECT
    `C`.`CUOCODE`,
    `C`.`NAME`,
    COUNT(*) AS TOTAL_PAYMENT,
    SUM(P.AMOUNT) AS TOTAL_AMOUNT
FROM
    `TAX_PAYMENT` AS `P`
INNER JOIN `TAX_CHECKPOINT C` ON `C`.`CUOCODE` = REGEXP_SUBSTR (P.INVOICEID,"R....")
WHERE
    `DELETED` = ?
AND TO_CHAR (TXTIME, 'YYYY-MM-DD') = ?
GROUP BY
    `C`.`CUOCODE`,
    `C`.`NAME`
ORDER BY
    `TOTAL_AMOUNT` DESC