B
B
buildok2015-10-24 00:08:43
Yii
buildok, 2015-10-24 00:08:43

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;

It works. In ActiveRecord:
// 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']);
}

Next, in the ArticleController, I return my ActiveDataProvider:
...
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'),
 ]);

As a result, JSON arrives, in which there are no fields from Status. How do you get them out anyway?
Also, if you do this:
.....
        '{{article}}.descr AS ad', // или 'ad' =>  '{{article}}.descr'
                                .....
        '{{status}}.code',
        '{{status}}.descr'])

then status.descr appears in the response, and article.descr disappears. Some street magic. Other fields from the Status do not get into the response at all.
What is wrong?
The following is in the MySQL log:
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

1 answer(s)
B
buildok, 2015-10-24
@buildok

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'
]);

But I don't like this SQL in the controller at all. Therefore, I created a view in MySQL, in which all the necessary fields are from related tables. To this view, the model and everything, you can do without joinWith, etc.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question