+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: how to write this sql with active record (/showthread.php?tid=78847)
how to write this sql with active record - venance - 03-18-2021
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
RE: how to write this sql with active record - php_rocs - 03-18-2021
@venance,
Have you thought about using query binding (https://codeigniter.com/user_guide/database/queries.html?highlight=sql#query-bindings) instead? This way you can put the query in as you have it above.
RE: how to write this sql with active record - venance - 03-18-2021
(03-18-2021, 08:09 AM)php_rocs Wrote: @venance,Yes I have used it, but because I want to use paginate(), the query() didn't work.
Have you thought about using query binding (https://codeigniter.com/user_guide/database/queries.html?highlight=sql#query-bindings) instead? This way you can put the query in as you have it above.
All I want to get data and use pager
PHP Code:
$this->table("mytable")->paginate(5);
PHP Code:
$sql = "mystatement";
$this->db->query($sql);
RE: how to write this sql with active record - php_rocs - 03-18-2021
@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.
RE: how to write this sql with active record - venance - 03-18-2021
(03-18-2021, 11:35 AM)php_rocs Wrote: @venance,Thank you, I think for this issue a datatable would be the best solution
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.