YII2 how to update field with query buider

Question

How can I update the field with query builder in Yii2?

requirement is every transaction from table B table A will automatically update value field: actual and available

Table A

• id
• budget
• actual // query amount from table b
• available // value = (budget - actual)

Table B

• id
• amount
• tableA_id

table relation

here sql query

SELECT
  DISTINCT(A.id) AS tableA,
  A.budget AS budget,
  SUM(B.amount) AS actual,
  budget - SUM(B.amount) AS Available
FROM
  tableA AS A
 LEFT JOIN
  TableB AS B
ON
  A.id = B.TableA_id
Group By
 tableA

results

I'm try to learn from https://github.com/yiisoft/yii2/blob/master/docs/guide/db-dao.md#basic-sql-queries

But i have no idea how to apply SQL to query builder in Yii2 and how to coding or where to put the code in model or controller.

Answer 1

an easy solution could be based on sql command (instead of an activeQuery )

\Yii::$app->db->createCommand('update TableA A
               inner join (
                select B.TableA_id id
                    , sum(B.amount) Available
                FROM TableB B
                GROUP BY id
               ) T  on A.id =T.id
                AND A.id = :actid
               set budget = budget - T.Available')->bindValue(':actid', $your_id)
->execute();

I have refatctored your code avoiding the issue in my previous comment