Laravel - Converting RAW query into Eloquent query (Update Query)

i have a table called partner_transaction. On this table i have 4 columns: id, partner_id, type, amount

|id|partner_id |type     |amount|
|--|------------|---------|------|
|1 |65          |Credit   |50    |
|2 |65          |Credit   |20    |
|3 |65          |Debit    |30    |
|4 |10          |Credit   |30    |

now i am introduce a new balance column(current balance) on this table like below.

|id|partner_id  |type     |amount|balance|
|--|------------|---------|------|-------|
|1 |65          |Credit   |50    |50     |
|2 |65          |Credit   |20    |70     |
|3 |65          |Debit    |30    |40     |
|4 |10          |Debit    |30    |-30    |

We wants to re-adjust all balance column by type for a specific partner(65). So we write a raw query -

update partner_transactions pt
inner join (
select
    id, (select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
    from partner_transactions pt2
    where pt2.partner_id = pt1.partner_id and pt2.id <= pt1.id
    ) balance
    from partner_transactions pt1
    WHERE pt1.partner_id = 65
) ptx
on ptx.id = pt.id AND pt.partner_id = 65
set pt.balance = ptx.balance
WHERE pt.partner_id = 65

We are using Laravel 5.2. Now we wants to convert in eloquent. So we write 2 function.

01. For calculating balance -

private function calculateBalanceQuery()
    {
        return DB::table('partner_transactions as pt1')
            ->where('pt1.partner_id', 65)
            ->selectRaw("id, (select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
                from partner_transactions pt2
                where pt2.partner_id = pt1.partner_id and pt2.id <= pt1.id) balance"
            );
    }

02. For update partner_transaction_table -

public function updateQuery()
    {
        return PartnerTransaction::join(DB::raw('(' . $this->calculateBalanceQuery()->toSql() . ') ptx'), function ($join) {
                $join->on('ptx.id', '=', 'partner_transactions.id')->on('partner_transactions.partner_id', '=', DB::raw("'65'"));
            })
            ->where('partner_transactions.partner_id', 65)
            ->update(['partner_transactions.balance' => 'ptx.balance']);
    }

But when execute this script, their was an error occurred.

QueryException in Connection.php line 729:
SQLSTATE[HY093]: Invalid parameter number (SQL: update `partner_transactions` inner join (select id, (select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
from partner_transactions pt2
where pt2.partner_id = pt1.partner_id and pt2.id <= pt1.id) balance from `partner_transactions` as `pt1` where `pt1`.`partner_id` = ptx.balance) ptx on `ptx`.`id` = `partner_transactions`.`id` and `partner_transactions`.`partner_id` = '65' set `partner_transactions`.`balance` = 65 where `partner_transactions`.`partner_id` = ?)

So how could we convert it.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2AhczCL
via IFTTT