Level 102
Please format your code (you can edit the post) https://help.github.com/articles/creating-and-highlighting-code-blocks/
Sep 15, 2022
8
Level 1
Laravel raw query related field calculation
I wanna filter customers based on balance (which comes from the related 'ledgers' eloquent model table which has 'customer_id', 'amount' and 'type ' fields (type=1 credit and type =2 debit)) my codes are as
// Balance Filter
if ($this->min_balance) {
$this->customers = Customer::whereHas('ledgers', function ($query) {
$query->select(DB::raw('SUM( (SUM(amount) WHERE type = 1 AS total_credits) - (SUM(amount) WHERE type = 2 AS total_debits) ) >= ' . $this->min_balance));
})->get();
} else {
$this->reloadCustomers();
}
but I am getting error
"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE type = 1 AS total_credits) - (SUM(amount) WHERE type = 2 AS total_debits)' at line 1 (SQL: select * from `customers` where exists (select SUM( (SUM(amount) WHERE type = 1 AS total_credits) - (SUM(amount) WHERE type = 2 AS total_debits) ) >= 12 from `ledgers` where `customers`.`id` = `ledgers`.`customer_id`))"
any help appreciated.
Level 1
@sinnbeck updated could you please check?
thanks
Level 102
First off never add variable directly in a query. That is how sql injections happen. Try this and post the output
Customer::whereHas('ledgers', function ($query) {
$query->select(DB::raw('SUM( (SUM(amount) WHERE type = 1 AS total_credits) - (SUM(amount) WHERE type = 2 AS total_debits) ) >= ?', [ $this->min_balance]));
})->dd();
Level 1
@Sinnbeck thanks for your reply response is as
"select * from `customers` where exists (select SUM( (SUM(amount) WHERE type = 1 AS total_credits) - (SUM(amount) WHERE type = 2 AS total_debits) ) >= ? from `ledgers` where `customers`.`id` = `ledgers`.`customer_id`) ◀"
[]
Level 102
@ilearnbydoing If that is mysql, then that is not a valid query :)
Level 102
But your query seems to imply two subqueries? You cannot use where inside a select unless its a subquery
Level 1
@Sinnbeck could you please illustrate the subquery here which can perform calculations on the same field based on two different conditions?
thanks
Level 1
Just curious if it is doable in Laravel as possibly I need to have a different column for 'credit 'and 'debit' instead of just one 'amount' with 'type'
Please or to participate in this conversation.