In yii2, when querying a list, when type=5, you need to associate the image model based on value. How to avoid the redundant value in value?
1. The existing implementation is as follows
$informations = ConventionCustomerServiceInformation::find()->select('id, group_id, type, label, value')->where([
'convention_id' => $conventionId,
])->with(['image'])->orderBy('order_number ASC')->asArray()->all();
public function getImage(): ActiveQuery
{
return $this->hasOne(Image::class, ['id' => 'value']);
}
2. The generated SQL is as follows
SELECT * FROM `images` WHERE `id` IN ('7777', '55550', '333', '66', '地址', '1827082629732850', '88', '1827082629732857')
3. However, only1827082629732850,1827082629732857The corresponding type=5, and the other ID’s type != 5. as shown in Figure 1
Figure 1
4. Collect all values of type=5 (that is, image ID), query the corresponding pictures in batches, and attach image data. Re-implement as follows
$informations = ConventionCustomerServiceInformation::find()->select('id, group_id, type, label, value')->where([
'convention_id' => $conventionId,
])->orderBy('order_number ASC')->asArray()->all();
// 批量查询对应图片
$images = [];
$imageIds = $this->getImageValuesFrom($informations);
if (!empty($imageIds)) {
$images = Image::find()
->where(['id' => $imageIds])
->indexBy('id')
->asArray()
->all();
}
foreach($informations as $info){
if ($info['type'] == ConventionCustomerServiceInformation::TYPE_IMAGE) {
$info['image'] = $images[$info['value']] ?? null;
} else {
$info['image'] = null;
}
$mappings[$info['group_id']]['informations'][] = $info;
}
/**
* 获取所有 type = 5 的 value，即所有的图片
* @param array $models
* @return array
*/
private function getImageValuesFrom(array $models): array
{
return ArrayHelper::getColumn(
array_filter($models, function ($model) {
return $model['type'] == ConventionCustomerServiceInformation::TYPE_IMAGE;
}),
'value'
);
}
5. The generated sql is as follows, in line with expectations
SELECT * FROM `images` WHERE `id` IN ('1827082629732850', '1827082629732857')
