Yii2 QueryBuilder 更新与加入

2022-01-07 00:00:00 php mysql yii2

我有以下原始 SQL 查询:

I have the following raw SQL query:

UPDATE `pay_audit`
JOIN `invoice_items`
ON `invoice_items`.`mdn` = `pay_audit`.`account_id` 
AND `invoice_items`.`unitprice` = `pay_audit`.`payment`
AND `invoice_items`.`producttype_name` LIKE 'PAYMENT'
AND DATE_FORMAT(`invoice_items`.`created`, '%Y-%m-%d') = '2015-02-21'
SET `pay_audit`.`invoice_item_id` = `invoice_items`.`id`
WHERE `pay_audit`.`report_date` = '2015-02-21'

日期是php中的变量$date.

The date is a variable $date in php.

如何将这个原始 SQL 查询转换"为 Yii2 QueryBuilder?

How can i "convert" this raw SQL query into Yii2 QueryBuilder?

[更新]

正如 Felipe 提到的，查询构建器是不可能的，所以我最终这样做了:

As Felipe mentioned it's not possible with query builder so i ended up doing it like followed:

    $today = date('Y-m-d');
    $sql = "";
    $sql .= "UPDATE `pay_audit` ";
    $sql .= "JOIN `invoice_items` ";
    $sql .= "ON `invoice_items`.`mdn` = `pay_audit`.`account_id` ";
    $sql .= "AND `invoice_items`.`unitprice` = `qpay_audit`.`payment` ";
    $sql .= "AND `invoice_items`.`producttype_name` LIKE 'PAYMENT' ";
    $sql .= "AND DATE_FORMAT(`invoice_items`.`created`, '%Y-%m-%d') = '$today' ";
    $sql .= "SET `pay_audit`.`invoice_item_id` = `invoice_items`.`id` ";
    $sql .= "WHERE `pay_audit`.`report_date` = '$today'";

    $command = Yii::$app->db->createCommand($sql);
    $command->execute();

推荐答案

我很害怕 Yii 2 查询生成器 仅用于选择查询.

对于更新查询，您至少有三个选项:

For update queries you have at least three options:

  • 原始 SQL:

  • Raw SQL:

Yii::$app->db->createCommand('update user set status = 1 where age > 30')->execute();

  • 带有占位符的原始 SQL(防止 SQL 注入)

  • Raw SQL with placeholders (to prevent SQL injection)

    Yii::$app->db->createCommand('update user set status = :status where age > 30')->bindValue(':status','1')->execute();

  • update() 方法

  • update() method

    // update user set status = 1 where age > 30
Yii::$app->db->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

    • 更多信息在这里:

    • Yii DAO

    关于 SO 的另一个问题

    相关文章