Yii2:如何在同一记录的两列中获取具有相等不为空数据的表列

Question

我想使用 ActiveQuery 从 SQLite 数据库中选择字段，其中两列的值相等不为空。

我需要这样的结果 SQL 作为示例：

SELECT * FROM messages WHERE msg_sent = 0 AND file_size = downloaded_size AND file_sha1 = downloaded_sha1

Answer 1

我已经要求这样的解决方案：

use yiidbExpression;
    $messages = Messages::find()
        ->where([
            'file_downloaded' => 1,
        ])
        ->andWhere(['=', 'msg_sent', 0])
        ->andWhere(['=', 'file_size', new Expression('`downloaded_size`')])
        ->andWhere(['=', 'file_sha1', new Expression('`downloaded_sha1`')])
        ->asArray()
        ->all();
// to debug raw SQL I have used the following:
$query = = Messages::find()
            ->where([
                'file_downloaded' => 1,
            ])
            ->andWhere(['=', 'msg_sent', 0])
            ->andWhere(['=', 'file_size', new Expression('`downloaded_size`')])
            ->andWhere(['=', 'file_sha1', new Expression('`downloaded_sha1`')]);
echo var_dump($query->prepare(Yii::$app->db->queryBuilder)->createCommand()->rawSql) . PHP_EOL;

Answer 2

假设您的 activeRecord 类名为 MyModel

  $models = MyModel::find()
          ->where(['msg_sent'=> 0, 
                   'file_size'=>'downloaded_size',
                   'file_sha1' =>'downloaded_sha1'])
          ->all();

如果不工作散列格式，您可以将文字格式用作

   $models = MyModel::find()
    ->where('msg_sent = 0 and file_size= downloaded_size and file_sha1 = downloaded_sha1')
    ->all();