how to write this sql with active record

Hello everyone, I'm trying to calculate profit from two tables province_income and province_expenses. But I'm getting difficult to organize sql statement with ci4 query methods to make it work.
Help please, Thanks.

here is my sql

PHP Code:

                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
                          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 
                   ) as t 
                GROUP BY Date 


@venance,

Have you thought about using query binding (https://codeigniter.com/user_guide/datab...y-bindings) instead? This way you can put the query in as you have it above.

@venance,

Have you thought about using query binding (https://codeigniter.com/user_guide/datab...y-bindings) instead?  This way you can put the query in  as you have it above.

Yes I have used it, but because I want to use paginate(), the query() didn't work.
All I want to get data and use pager

PHP Code:

$this->table("mytable")->paginate(5); 


But  if I query() I will get data but I won't be able to paginate them like:

PHP Code:

$sql = "mystatement";
$this->db->query($sql); 


@venance,

Have you considered using something like Datatables (https://datatables.net/)? Then you don't have to worry about pageination.

Or you could create a variable that holds the range of records and whenever the query is run it only pulls the next set of records based on the variables value.

@venance,

Have you considered using something like Datatables (https://datatables.net/)?  Then you don't have to worry about pageination.

Or you could create a variable that holds the range of records and whenever the query is run it only pulls the next set of records based on the variables value.

Thank you, I think for this issue a datatable would be the best solution