codeigniter - 使用活动记录计算利润、收入和费用 codeigniter 4

Question

我有两个表province_incomes 和province_expenses，我想计算总收入和axpense 和pprofit，但是当我尝试使用codeigniter 活动记录时我变得很困难。

    here is my tables

    province_income
    +-------------------------------+
    | id | category | amount | date |
    +-------------------------------+

    province_expenses
    +-------------------------------+
    | id | category | amount | date |
    +-------------------------------+

    expected result table
    +----------------------------------+
    | date | income | expense | profit |
    +----------------------------------+

//SQL

          SELECT Date, 
          SUM(Income) as Income, 
          SUM(Expense) as Expense, 
          SUM(Income) - SUM(Expense) as Profit 
          FROM ( 
                    SELECT 
                        province_income.date as Date, 
                        province_income.amount as Income, 
                        0 as Expense 
                    FROM province_income
                    LEFT JOIN province ON province.provId = province_income.provId
                    WHERE province_income.provId = 5
                    UNION ALL 
                    SELECT 
                        province_expenses.date as Date, 
                        0 as Income, 
                       province_expenses.amount as Expense 
                    FROM province_expenses
                    LEFT JOIN province ON province.provId = province_expenses.provId
                    WHERE province_expenses.provId = 5 
             ) as t 
          GROUP BY Date

标签： codeignitercodeigniter-3codeigniter-4