Welcome Guest, Not a member yet? Register   Sign In
how to write this sql with active record
#1

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 DateSUM(Income) as IncomeSUM(Expense) as ExpenseSUM(Income) - SUM(Expense) as Profit 
                FROM 

                          SELECT province_income.date as Dateprovince_income.amount as Incomeas Expense 
                          FROM province_income
                          LEFT JOIN province ON province
.provId province_income.provId WHERE province_income.provId
                          UNION ALL 
                          SELECT province_expenses
.date as Dateas Incomeprovince_expenses.amount as Expense 
                          FROM province_expenses
                          LEFT JOIN province ON province
.provId province_expenses.provId WHERE province_expenses.provId 
                   
) as 
                GROUP BY Date 
Reply
#2

@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.
Reply
#3

(03-18-2021, 08:09 AM)php_rocs Wrote: @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); 
Reply
#4

(This post was last modified: 03-18-2021, 11:38 AM by php_rocs.)

@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.
Reply
#5

(03-18-2021, 11:35 AM)php_rocs Wrote: @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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB