WesselV WesselV - 4 months ago 19
SQL Question

Yii2 query returns 2 rows, model only one

Within Yii2 I use the following code in my 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
]);


Which outputs the following query (from the Yii2 debug toolbar):

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`


Running this query on the database returns 2 rows, which is correct. However, when dumping the data from $licenses, it only returns one row. When I change the customer_id in the licences table, it however does return both rows. So somewhere Yii2 drops one row, even though the query returns the correct values.

I've searched a lot, however I can't find any solution for the problem. Thank you in advance!

EDIT 1

Within my License model, I have the following function. Removing this function still causes the same problem, however it gets the other row (it's reversed). Still one row though.

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


EDIT 2

Here are the dumped tables. When I change the customer_id on rows with id 3 and 4 to something else (in the dump they are both 2, once I change either one to another value) it does return both rows.

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

This is not an answer but a suggestion for check the problem origin

Could be that the query generatedv by activeQuery in not the same you sue in db console

try obtain the command use by yii2 and check if is the same as you expected

$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();

try

 echo $dbCommand->sql;

Try

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

how many result you obtain ? if you obatin a single row this is related to the fact you are using License::find() and the License part of the query return a model only because this part is the same for both the records

Then try using a command

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();
Comments