在Yii2中,我在控制器中使用以下代码:
$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转储数据时,它只返回一行。当我更改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');
这不是一个答案,而是一个检查问题来源的建议
可能是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();
对于调试ActiveQuery,我建议这样做:
$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()
;
var_dump($licenses->prepare( Yii::$app->db->queryBuilder )->createCommand()->rawSql);
顺便说一句,我猜你已经为两个客户分配了一个许可证,所以你得到了一个型号的许可证,两个客户被分配到了同一许可证。