Answer the question
In order to leave comments, you need to log in
Yii2 ActiveQuery::joinWith() unable to select fields from linked table?
There is a table Article(id, id_status, descr, ...) and a table Status(id, code, descr)
In MySQL I do:
SELECT article.id, article.id_status, article.descr, status.code, status.descr AS sd
FROM article
LEFT JOIN status ON article.id_status = status.id
WHERE status.code = 4;
// Article.php
public function getArtStatus()
{
return $this->hasOne(Status::className(), ['id' => 'id_status']) ->inverseOf('articles');
}
// Status.php
public function getArticles()
{
return $this->hasMany(Article::className(), ['id_status' => 'id']);
}
...
return new ActiveDataProvider([
'query' => $model->find()
->select([
'{{article}}.name',
'{{article}}.descr',
'{{article}}.id_status',
'{{article}}.createDt',
'{{status}}.code',
'{{status}}.descr AS sd'])
->joinWith(['artStatus'])
->andWhere('{{status}}.scode = :code', [':code' => $code])
->orderBy('{{article}}.createDt'),
]);
.....
'{{article}}.descr AS ad', // или 'ad' => '{{article}}.descr'
.....
'{{status}}.code',
'{{status}}.descr'])
SELECT COUNT(*)
FROM `article`
LEFT JOIN `status` ON `article`.`id_status` =
`status`.`id`
WHERE `status`.code = 4
SHOW FULL COLUMNS FROM `article`
SELECT
SELECT `article`.name, `article`.createDt, `article`.descr, `article`.id_status,
`status`.code, `status`.descr AS `sd`
FROM `article`
LEFT JOIN `status` ON `article`.`id_status` = `status`.`id`
WHERE `status`.code = 4
ORDER BY `article`.createDt LIMIT 20
SELECT * FROM `status` WHERE `id`=4
SHOW FULL COLUMNS FROM `status`
SELECT
Answer the question
In order to leave comments, you need to log in
Through var_dump, I found that the fields from the associated table are still returned in the nested ['artStatus'] array. For some reason, ActiveDataProvider does not add nested arrays to JSON.
Found a solution using SqlDataProvider:
$count = $model->find()
->joinWith('artStatus')
->andWhere('{{status}}.code = :code', [':code' => $code])
->count();
return new SqlDataProvider([
'totalCount' => $count,
'params' => [':code' => $code],
'sql' => '
SELECT article.*, status.code, status.descr AS sdescr
FROM article
LEFT JOIN status ON article.id_status = status.id
WHERE status.code = :code'
]);
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question