Is it possible to do a self join?
Trying to return rows based on a parent-child relationship.
Table
id INT, PRIMARY KEY
parent INT
type INT
...
Model
...
public function getParent()
{
return $this->hasOne(self::className(), ['id' => 'parent']);
}
...
Query
Model::find()
->joinWith('parent')
->asArray()
->all();
Yes, it’s possible, but you’ll probably need to set an alias for related.
Check this post for details.
Also a guide is ready: https://github.com/yiisoft/yii2/blob/master/docs/guide/active-record.md#joining-with-relations
Hmm, not sure if there’s an exampl of aliasing. Anyway, here it is:
$this->hasOne(self::className(), ['client_id' => 'id'])
->from(self::tableName() . ' my_alias');
Thanks, I’ve now got some aliases.
But when trying to select columns I get an undefined index error in "ActiveRelationTrait" at line 202.
Works fine if I don’t select columns but as the data is a response in AJAX I don’t want to send it all.
Model
public function getParent()
{
return $this->hasOne(self::className(), ['id' => 'parent'])
->from(self::tableName() . ' parent');
}
Model::find()
->from(Model::tableName() . ' t')
->joinWith('parent')
->select(['t.id', 't.title', 'parent.title'])
->asArray()
->all();
Cannot check it right now, but:
what’s the sql query generated?
shouldn’t [‘id’ => ‘parent’] be [‘parent’ => ‘id’]?
seems like a bad idea to name alias as an existing field name.
Renamed the alias to p
SELECT `t`.`id`, `t`.`title`, `p`.`title`
FROM `my_table` `t`
LEFT JOIN `my_table` `p` ON `t`.`parent` = `p`.`id`
Query runs fine, It’s just the populating.
I cannot confirm that, my code works fine.
Try to do composer update first.
PS. Btw here is select() for relation:
->joinWith(['parent' => function($q) {$q->select(['id', 'name']);}])
You have two title columns in select: [color=#008800][size=2]t[/size][/color][color=#666600][size=2].[/size][/color][color=#008800][size=2]title[/size][/color][color=#666600][size=2],[/size][/color][size=2] [/size][color=#008800][size=2]p[/size][/color][color=#666600][size=2].[/size][/color][color=#008800][size=2]title[/size][/color]
Funny thing is that’s not a problem, current AQ deals with it (or I’m doing something wrong).
I can show some screenshots where I join table with itself on id = id, and the result is correct.
Here’s my code:
public function getSj()
{
return $this->hasOne(static::className(), ['id' => 'id'])
->from(static::tableName() . ' parent');
}
$test = \app\models\Sauna::find()
->from(\app\models\Sauna::tableName() . ' t')
->joinWith(['sj' => function($q) {$q->select(['id', 'created_at']);}])
->asArray()
->select(['t.id', 't.created_at', 'parent.created_at']) // parent.created_at left intentionally
->all();
and the result is
Array
(
[0] => Array
(
[id] => 52925d7f04a67
[created_at] => 1385323951
[sj] => Array
(
[id] => 52925d7f04a67
[created_at] => 1385323951
)
)
[1] => Array
(
[id] => 52cfe92288004
[created_at] => 1389357452
[sj] => Array
(
[id] => 52cfe92288004
[created_at] => 1389357452
)
)
)
Found my issue, I needed to have ‘parent’ in the select.
Model::find()
->from(Model::tableName() . ' t')
->joinWith(['parent' => function($q) {
$q->select(['id', 'title']);
}])
->select(['t.id', 't.title', 't.parent'])
->asArray()
->all();
Thanks for the help.
Hmm, that’s strange.
Ah, yes. Correct.
Should be documented I suppose.