New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
question about the active record joinWith #5879
Comments
I am trying to figure out this by reading source code \vendor\yiisoft\yii2\db\ActiveQuery.php public function all($db = null)
{
return parent::all($db);
} it called the all method in parent class, show below: public function all($db = null)
{
$rows = $this->createCommand($db)->queryAll();
return $this->populate($rows);
} after the command " $rows = $this->createCommand($db)->queryAll();" executed, the data I want iis right. This mean the sql is right. Ok, let us check the populate() method, the populate in AactiveQuery class: public function populate($rows)
{
if (empty($rows)) {
return [];
}
$models = $this->createModels($rows);
if (!empty($this->join) && $this->indexBy === null) {
$models = $this->removeDuplicatedModels($models);
}
if (!empty($this->with)) {
$this->findWith($this->with, $models);
}
if (!$this->asArray) {
foreach ($models as $model) {
$model->afterFind();
}
}
return $models;
} it called the createModels() medthod, code show as follow: private function createModels($rows)
{
$models = [];
if ($this->asArray) {
if ($this->indexBy === null) {
return $rows;
}
foreach ($rows as $row) {
if (is_string($this->indexBy)) {
$key = $row[$this->indexBy];
} else {
$key = call_user_func($this->indexBy, $row);
}
$models[$key] = $row;
}
} else {
/* @var $class ActiveRecord */
$class = $this->modelClass;
if ($this->indexBy === null) {
foreach ($rows as $row) {
$model = $class::instantiate($row);
$class::populateRecord($model, $row);
$models[] = $model;
}
} else {
foreach ($rows as $row) {
$model = $class::instantiate($row);
$class::populateRecord($model, $row);
if (is_string($this->indexBy)) {
$key = $model->{$this->indexBy};
} else {
$key = call_user_func($this->indexBy, $model);
}
$models[$key] = $model;
}
}
}
return $models;
} if this->asArray is true, it works well, else I cant get the related object. I still haven't find the solution |
"samdark added this to the 2.0.x milestone 6 hours ago" It this mean it is a bug or something? |
No, it means that if it's a bug it's fixable withing 2.0.x releases. It's still to be verified. |
appreciate |
@shyandsy can you show the SQL queries generated by your code? you'll find them in the debug toolbar. |
and I tried the sql in phpadmin, it works well 2.I stay in school right now, I will show you the sql sentence when I go back. appreciate for you focus |
@cebe SELECT `oc_category`.* FROM `oc_category`
LEFT JOIN `oc_category_description`
ON `oc_category`.`category_id` = `oc_category_description`.`category_id`
WHERE parent_id = 0 and status = 1
ORDER BY `oc_category`.`sort_order`, LCASE(oc_category_description.name) and the result of this query is correct which is show in follow: I performed this sql statement in phpmyadmin. It works too |
@samdark "removed their assignment 16 hours ago" |
use alias |
@lynicidn I tried this , and it does not work.
the description always null |
try ->select('alias1.*, alias2.*'); |
same . I think the problem is not on the sql sentence, because It can take correct data from database. |
|
@cebe the ActiveQuery::populate($rows) cannot construct model for rhe related data, and I don't know the reason. If there is a example, I will compare the execution process. |
|
@qiangxue I generate the model by gii, for the table oc_category and oc_category_description. the follow is all code in OcCategort.php ///////////////////////////////////////////////////////// <?php
namespace app\models;
use Yii;
/**
* This is the model class for table "oc_category".
*
* @property integer $category_id
* @property string $image
* @property integer $parent_id
* @property integer $top
* @property integer $column
* @property integer $sort_order
* @property integer $status
* @property string $date_added
* @property string $date_modified
*/
class OcCategory extends \yii\db\ActiveRecord
{
public $category_id;
public $image;
public $parent_id;
public $top;
public $column;
public $sort_order;
public $status;
public $date_added;
public $date_modified;
/**
* @inheritdoc
*/
public static function tableName()
{
return 'oc_category';
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['parent_id', 'top', 'column', 'sort_order', 'status'], 'integer'],
[['top', 'column', 'status'], 'required'],
[['date_added', 'date_modified'], 'safe'],
[['image'], 'string', 'max' => 255]
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'category_id' => 'Category ID',
'image' => 'Image',
'parent_id' => 'Parent ID',
'top' => 'Top',
'column' => 'Column',
'sort_order' => 'Sort Order',
'status' => 'Status',
'date_added' => 'Date Added',
'date_modified' => 'Date Modified',
];
}
//////////////////////////////// 关系声明 ////////////////////////////////////////////
// Order has_one Customer via Customer.id -> customer_id
public function getOcCategoryDescription()
{
return $this->hasOne(OcCategoryDescription::className(), ['category_id' => 'category_id']);
}
//////////////////////////////////////////////////////////////////////////////////////////////
/*get all catagories*/
public function getCategories($parent_id = 0)
{
$result = OcCategory::find()->joinWith('ocCategoryDescription');
//$result = $result->select('oc_category.*,oc_category_description.*');
//$result = $result->where('oc_category.parent_id = ' . $parent_id . ' and oc_category.status = 1');
$result = $result->orderBy('oc_category.sort_order, LCASE(oc_category_description.name)');
//$result = $result->asArray();
$result = $result->all();
//////////////////////up to there , the related filed of $result is null
foreach($result as $category){
$a = $category;
$description = $category->categoryDescription;
//////////////////////up to there , the $description is null
}
$categoryDescription = $result[0]->getRelation('ocCategoryDescription');
/* this way works well
$result = new \yii\db\Query();
$result = $result->select('oc_category.*,oc_category_description.*');
$result = $result->from('oc_category');
$result = $result->where('oc_category.parent_id = ' . $parent_id . ' and oc_category.status = 1');
$result = $result->leftjoin('oc_Category_Description', 'oc_category.category_id = oc_category_description.category_id');
$command = $result->createCommand();
$rows = $command->queryAll();
return $rows;
*/
}
} |
I'm confused. In your first post, you have |
in the first post, the model name is wrong. I changed that but I havent update that. sorry I will modify that. just hold on second |
@qiangxue getOcCategoryDescription() method is used to declare the relation between two model, which the part OcCategoryDescription is sames as the parameter of joinwith it is not the point. I trace the code deeper, the code can get all data after queryAll(), so I think the sql has no problem public function all($db = null) |
I'm not asking about what could be the cause of the problem. You said your first post no longer applies, while your latest code snippet (#5879 (comment)) doesn't clearly explain your problem as it contains too much irrelevant code and comments. So could you simplify your code a bit and explain how to reproduce the issue? |
I think this is all related code. I cant get the related data if I donot use asArray method. the method getCategories($parent_id = 0) is called by site controller to get all categories. $result = OcCategory::find()->joinWith('ocCategoryDescription');
$result = $result->orderBy('oc_category.sort_order, LCASE(oc_category_description.name)');
$result = $result->all(); the code show above perform left join query to get data, but I cant get the ocCategoryDescription data. because that field always be null ////////////////////////////////////////////////////////////////////////////////// <?php
namespace app\models;
use Yii;
class OcCategory extends \yii\db\ActiveRecord
{
public $category_id;
public $image;
public $parent_id;
public $top;
public $column;
public $sort_order;
public $status;
public $date_added;
public $date_modified;
/**
* @inheritdoc
*/
public static function tableName()
{
return 'oc_category';
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['parent_id', 'top', 'column', 'sort_order', 'status'], 'integer'],
[['top', 'column', 'status'], 'required'],
[['date_added', 'date_modified'], 'safe'],
[['image'], 'string', 'max' => 255]
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'category_id' => 'Category ID',
'image' => 'Image',
'parent_id' => 'Parent ID',
'top' => 'Top',
'column' => 'Column',
'sort_order' => 'Sort Order',
'status' => 'Status',
'date_added' => 'Date Added',
'date_modified' => 'Date Modified',
];
}
// declare relation between this model and OcCategoryDescription model
public function getOcCategoryDescription()
{
return $this->hasOne(OcCategoryDescription::className(), ['category_id' => 'category_id']);
}
//////////////////////////////////////////////////////////////////////////////////////////////
/*get all catagories*/
public function getCategories($parent_id = 0)
{
$result = OcCategory::find()->joinWith('ocCategoryDescription');
//$result = $result->select('oc_category.*,oc_category_description.*');
//$result = $result->where('oc_category.parent_id = ' . $parent_id . ' and oc_category.status = 1');
$result = $result->orderBy('oc_category.sort_order, LCASE(oc_category_description.name)');
//$result = $result->asArray();
$result = $result->all();
//////////////////////up to there , the related filed of $result is null
foreach($result as $category){
$a = $category;
$description = $category->ocCategoryDescription;
//////////////////////up to there , the $description is null
}
}
} |
|
@qiangxue always made mistake on edit. |
the struct of these two table are CREATE TABLE IF NOT EXISTS `oc_category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`image` varchar(255) DEFAULT NULL,
`parent_id` int(11) NOT NULL DEFAULT '0',
`top` tinyint(1) NOT NULL,
`column` int(3) NOT NULL,
`sort_order` int(3) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL,
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=76 ;
CREATE TABLE IF NOT EXISTS `oc_category_description` (
`category_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`meta_description` varchar(255) NOT NULL,
`meta_keyword` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`,`language_id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
... |
I tried your DB and code, but couldn't reproduce your issue. Below is what I tried: CREATE TABLE IF NOT EXISTS `oc_category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=76 ;
CREATE TABLE IF NOT EXISTS `oc_category_description` (
`category_id` int(11) NOT NULL,
`language_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`category_id`,`language_id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into oc_category values (1, 1);
insert into oc_category_description values (1, 2, 'test');
insert into oc_category values (2, 0);
insert into oc_category_description values (2, 3, 'test2'); $result = OcCategory::find()->joinWith('ocCategoryDescription');
$result = $result->all();
foreach($result as $category){
$description = $category->ocCategoryDescription;
echo $description->name;
} |
Perhaps it is related with your particular data? Note that since left join is used, so it is possible that |
could you make a screenshot of your variable $category in the for loop? the data shows as below INSERT INTO `oc_category` (`category_id`, `image`, `parent_id`, `top`, `column`, `sort_order`, `status`, `date_added`, `date_modified`) VALUES
(62, '', 0, 1, 1, 0, 1, '2014-06-30 11:36:06', '2014-06-30 11:42:44'),
(63, '', 0, 1, 1, 0, 1, '2014-06-30 11:39:41', '2014-06-30 11:55:47'),
(64, '', 0, 1, 1, 0, 1, '2014-06-30 11:41:00', '2014-08-02 10:56:25'),
(65, '', 0, 1, 1, 0, 1, '2014-06-30 11:42:09', '2014-08-02 10:55:42'),
(67, '', 0, 1, 1, 0, 1, '2014-06-30 11:57:26', '2014-08-02 10:57:04'),
(68, '', 0, 0, 1, 0, 1, '2014-06-30 11:58:37', '2014-08-02 10:59:18'),
(69, '', 0, 0, 1, 0, 1, '2014-06-30 11:59:05', '2014-08-02 10:59:08'),
(70, '', 0, 1, 1, 0, 1, '2014-06-30 12:44:39', '2014-08-02 10:55:55'),
(75, '', 0, 1, 1, 0, 1, '2014-08-02 15:28:46', '2014-08-02 15:28:46');
INSERT INTO `oc_category_description` (`category_id`, `language_id`, `name`, `description`, `meta_description`, `meta_keyword`) VALUES
(62, 1, 'Lanterns/Candle Holders', '', 'Lanterns/Candle Holders', 'Lantern, Candle, Candle Holder, birch, bark'),
(63, 1, 'Pots & Trays', '', 'Pots/Trays', 'Pots,Plate,Tray,Vase,Crate, flower, birch, bark'),
(64, 1, 'Ornaments', '', 'Hanging Ornaments', 'ornament, christmas, tree, wreath, birch, bark'),
(65, 1, 'Decor', '', 'Decor, Birch Bark', 'birch, bark, decor'),
(67, 1, 'Storage', '', 'crates', 'crate, box, container'),
(68, 1, 'Spring & Summer', '', '', 'spring, hen, summer, rabbit, bunny, chicken'),
(69, 1, 'Fall & Winter', '', 'fall & winter', 'wreath, ornaments'),
(70, 1, 'Letterstand', '', '', 'birch, letter'),
(75, 1, 'Garden', '', '', '');
''' |
What's the data for |
INSERT INTO `oc_category` (`category_id`, `image`, `parent_id`, `top`, `column`, `sort_order`, `status`, `date_added`, `date_modified`) VALUES
(62, '', 0, 1, 1, 0, 1, '2014-06-30 11:36:06', '2014-06-30 11:42:44'),
(63, '', 0, 1, 1, 0, 1, '2014-06-30 11:39:41', '2014-06-30 11:55:47'),
(64, '', 0, 1, 1, 0, 1, '2014-06-30 11:41:00', '2014-08-02 10:56:25'),
(65, '', 0, 1, 1, 0, 1, '2014-06-30 11:42:09', '2014-08-02 10:55:42'),
(67, '', 0, 1, 1, 0, 1, '2014-06-30 11:57:26', '2014-08-02 10:57:04'),
(68, '', 0, 0, 1, 0, 1, '2014-06-30 11:58:37', '2014-08-02 10:59:18'),
(69, '', 0, 0, 1, 0, 1, '2014-06-30 11:59:05', '2014-08-02 10:59:08'),
(70, '', 0, 1, 1, 0, 1, '2014-06-30 12:44:39', '2014-08-02 10:55:55'),
(75, '', 0, 1, 1, 0, 1, '2014-08-02 15:28:46', '2014-08-02 15:28:46');
''' |
is this possible lead by that I used the dev version the following is the composer.json file {
"name": "yiisoft/yii2-app-advanced",
"description": "Yii 2 Advanced Application Template",
"keywords": ["yii2", "framework", "advanced", "application template"],
"homepage": "http://www.yiiframework.com/",
"type": "project",
"license": "BSD-3-Clause",
"support": {
"issues": "https://github.com/yiisoft/yii2/issues?state=open",
"forum": "http://www.yiiframework.com/forum/",
"wiki": "http://www.yiiframework.com/wiki/",
"irc": "irc://irc.freenode.net/yii",
"source": "https://github.com/yiisoft/yii2"
},
"minimum-stability": "dev",
"require": {
"php": ">=5.4.0",
"yiisoft/yii2": "*",
"yiisoft/yii2-bootstrap": "*",
"yiisoft/yii2-swiftmailer": "*"
},
"require-dev": {
"yiisoft/yii2-codeception": "*",
"yiisoft/yii2-debug": "*",
"yiisoft/yii2-gii": "*",
"yiisoft/yii2-faker": "*"
},
"config": {
"process-timeout": 1800
},
"extra": {
"asset-installer-paths": {
"npm-asset-library": "vendor/npm",
"bower-asset-library": "vendor/bower"
}
}
} |
Now I see why. You should remove all those property declaration in your AR classes. |
the description still be null............................ |
Please try my AR classes: class OcCategory extends ActiveRecord
{
public static function tableName()
{
return 'oc_category';
}
public function getOcCategoryDescription()
{
return $this->hasOne(OcCategoryDescription::className(), ['category_id' => 'category_id']);
}
}
class OcCategoryDescription extends ActiveRecord
{
public static function tableName()
{
return 'oc_category_description';
}
} |
thank you very much @qiangxue it is the reason i create class variable for table fields. it works well after I removed variables in ocCategoryDescription, |
Yes, that's what I suggested you to change. |
thanks a lot. |
for the following code in my catagory model
if i do not use the asArray method, then I can't get the related object
if I add the code $result = $result->asArray();, then I can get the catagory description data.
is that mean if i want to get data with related data, I must use the asArray method?
I haven't find a good case to describe this, are there someone know the answer?
I appreciate it if it could be solved today....
:rolleyes:
(I still have little problem on English, but I am getting better)
The text was updated successfully, but these errors were encountered: