within yii2 use following code in 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 following query (from yii2 debug toolbar):
select `license`.*, `customer`.* `license` inner join `customer` on `customer`.`id` = `license`.`customer_id` ((`active`='1') , (not (`customer_id` null))) , (last_changed > last_confirmed) order `added_on` running query on database returns 2 rows, correct. however, when dumping data $licenses, returns 1 row. when change customer_id in licences table, return both rows. somewhere yii2 drops 1 row, though query returns correct values.
i've searched lot, can't find solution problem. thank in advance!
edit 1
within license model, have following function. removing function still causes same problem, gets other row (it's reversed). still 1 row though.
public function getcustomer() { return $this->hasone(customer::classname(), ['id' => 'customer_id']); } edit 2
here dumped tables. when change customer_id on rows id 3 , 4 else (in dump both 2, once change either 1 value) 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 `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 `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');
this not answer suggestion check problem origin
could query generatedv activequery in not same sue in db console
try obtain command use yii2 , check if same 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 $key=> $value) { var_dump($value->license ); } how many result obtain ? if obatin single row related fact using license::find() , license part of query return model because part same both records
then try using command
use yii; $query= \yii::$app->db->createcommand( "select `license`.*, `customer`.* `license` inner join `customer` on `customer`.`id` = `license`.`customer_id` ((`active`='1') , (not (`customer_id` null))) , (last_changed > last_confirmed) order `added_on`;"); $licenses = $query->queryall(); 
Comments
Post a Comment