mysql - Yii2 查询返回 2 行，模型只有一个

Question

在 Yii2 中，我在 Controller 中使用以下代码:

$licenses = License::find()
        ->select('`license`.*, `customer`.*')
        ->orderBy('added_on')
        ->innerJoin('customer', '`customer`.`id` = `license`.`customer_id`')
        ->where([
            'active' => '1'
        ])
        ->andWhere(['not', ['customer_id' => null]])
        ->andWhere("last_changed > last_confirmed")
        ->all();

    return $this->render('not-handled', [
        'licenses' => $licenses
    ]);

输出以下查询(来自 Yii2 调试工具栏):

SELECT `license`.*, `customer`.* FROM `license` INNER JOIN `customer` ON `customer`.`id` = `license`.`customer_id` WHERE ((`active`='1') AND (NOT (`customer_id` IS NULL))) AND (last_changed > last_confirmed) ORDER BY `added_on`

在数据库上运行此查询返回 2 行，这是正确的。但是，当从 $licenses 转储数据时，它只返回一行。当我更改许可证表中的 customer_id 时，它确实返回了两行。所以 Yii2 在某个地方丢了一行，即使查询返回了正确的值。

我已经搜索了很多，但是找不到解决问题的方法。提前致谢!

编辑 1

在我的许可证模型中，我有以下功能。删除此功能仍然会导致同样的问题，但它会得到另一行(相反)。不过还是一排。

public function getCustomer()
{
    return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
}

编辑 2

这是转储表。当我将 ID 为 3 和 4 的行上的 customer_id 更改为其他内容时(在转储中，它们都是 2，一旦我将其中一个更改为另一个值)，它确实会返回两行。

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(70) NOT NULL,
  `streetname` varchar(100) NOT NULL,
  `house_number` int(20) NOT NULL,
  `city` varchar(100) NOT NULL,
  `postal_code` varchar(7) NOT NULL,
  `email` varchar(100) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `customer` VALUES (1,'Dummyuser1','Dummystreet1',1,'Dummtcity1','1111 DE','dummy1@hotmail.com','dummy1','dummy1'),(2,'Dummyuser2','Dummestreet2',2,'Dummycity2','2222 RR','dummy2@hotmail.com','dummy2','dummy2');

DROP TABLE IF EXISTS `license`;
CREATE TABLE `license` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `license` varchar(29) NOT NULL,
  `added_on` datetime NOT NULL,
  `license_activated_on` datetime NOT NULL,
  `last_changed` datetime NOT NULL,
  `last_confirmed` datetime NOT NULL,
  `requested_by` varchar(70) NOT NULL,
  `changed_by` varchar(70) NOT NULL,
  `active` enum('0','1') NOT NULL DEFAULT '0',
  `customer_id` int(10) NOT NULL,
  `costs` int(10) NOT NULL,
  `article_type` enum('1','3') NOT NULL DEFAULT '1',
  `invoice_number` int(10) NOT NULL,
  `invoice_date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `license_UNIQUE` (`license`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


INSERT INTO `license` VALUES (1,'DFDFD-DFDFD-DFDFD-QASDF','2016-04-22 17:06:57','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','Wessel','','1',0,30,'1',0,'0000-00-00 00:00:00'),(2,'ABCDE-FGHIJ-KLMNO-PQRST','2016-04-22 17:36:25','0000-00-00 00:00:00','2016-04-22 17:36:25','2016-04-22 18:00:00','Wesel','Wessel2','1',1,400,'3',34342,'2016-04-22 17:36:25'),(3,'QWERT-YUIOP-ASDFG-HJKLZ','2016-04-23 10:51:19','2016-04-23 10:51:19','2016-04-23 10:51:19','0000-00-00 00:00:00','Wessel Dummy','','1',2,40,'1',0,'0000-00-00 00:00:00'),(4,'QWERT-YUIOP-ASDFG-AAAAA','2016-04-23 10:51:19','2016-04-23 10:51:19','2016-04-23 10:51:19','0000-00-00 00:00:00','Wessel Dummy1','d','1',2,40,'1',0,'0000-00-00 00:00:00'),(5,'ABCDE-FGHIJ-DD4NO-PQRST','2016-04-22 17:36:25','0000-00-00 00:00:00','2016-04-22 17:36:25','2016-04-22 18:00:00','Wesel','Wessel2','1',2,400,'3',34342,'2016-04-22 17:36:25'),(7,'DFDFD-DFDFD-DFDFD-QQQQQ','2016-04-22 17:06:57','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','Wessel','','1',0,30,'1',0,'0000-00-00 00:00:00');

Answer 1

这不是答案，而是检查问题根源的建议

可能是 activeQuery 生成的查询与您在数据库控制台中起诉的查询不同

尝试获取 yii2 使用的命令并检查是否与您预期的相同

$dbcommand = License::find()
    ->select('`license`.*, `customer`.*')
    ->orderBy('added_on')
    ->innerJoin('customer', '`customer`.`id` = `license`.`customer_id`')
    ->where([
        'active' => '1'
    ])
    ->andWhere(['not', ['customer_id' => null]])
    ->andWhere("last_changed > last_confirmed")->createCommand();

尝试

 echo $dbCommand->sql;

尝试

foreach($licenses as $key=> $value) {
  var_dump($value->license );
}

您获得了多少结果？如果您获得一行，这与您使用 License::find() 的事实有关，并且查询的 License 部分仅返回一个模型，因为这部分对于两个记录都是相同的

然后尝试使用命令

use Yii;

$query= \Yii::$app->db->createCommand(
  "SELECT `license`.*, `customer`.* 
   FROM `license` 
   INNER JOIN `customer` ON `customer`.`id` = `license`.`customer_id` 
   WHERE ((`active`='1') 
   AND (NOT (`customer_id` IS NULL))) 
   AND (last_changed > last_confirmed) 
   ORDER BY `added_on`;");

$licenses = $query->queryAll();