1. I inspected the response data from the API and identified the cause: the checkin_location_coordinates field in the response is a MySQL point type. See Figure 1.
2. I decided to adjust the SQL query to use MySQL spatial functions (such as ST_X and ST_Y) to extract the longitude and latitude, thereby avoiding direct manipulation of the POINT field. Using the addSelect() method, I can add the longitude and latitude fields on top of querying all existing fields. Then, I would unset the checkin_location_coordinates field.
SELECT * FROM `survey_forms` WHERE (`id`='1825203750690273') AND (`user_id`='1825203750689422') LIMIT 1
SELECT *, ST_X(checkin_location_coordinates) AS `checkin_location_longitude`, ST_Y(checkin_location_coordinates) AS `checkin_location_latitude` FROM `survey_forms` WHERE `id`='1825203750690273' LIMIT 1
$form = SurveyForm::find()
->select('*') // 查询所有字段
->addSelect([
'ST_X(checkin_location_coordinates) AS checkin_location_longitude',
'ST_Y(checkin_location_coordinates) AS checkin_location_latitude',
])
->where([
'id' => $id,
])->limit(1)->asArray()->one();
unset($form['checkin_location_coordinates']);
3. However, this approach requires adjustments to every query involving this model. The workload exceeded expectations. I tried to implement automatic querying of checkin_location_longitude and checkin_location_latitude in the model file, followed by unsetting checkin_location_coordinates. I found that the fields() method was never executed. Ultimately, the only working solution was to call unset($form[‘checkin_location_coordinates’]); directly in the controller. I decided to stick with the approach from step 2.
public $checkin_location_longitude;
public $checkin_location_latitude;
public function afterFind()
{
parent::afterFind();
// 设置经度和纬度
$this->checkin_location_longitude = Yii::$app->db->createCommand("SELECT ST_X(checkin_location_coordinates) FROM {$this->tableName()} WHERE id = :id", [':id' => $this->id])->queryScalar();
$this->checkin_location_latitude = Yii::$app->db->createCommand("SELECT ST_Y(checkin_location_coordinates) FROM {$this->tableName()} WHERE id = :id", [':id' => $this->id])->queryScalar();
// 排除原始字段
unset($this->checkin_location_coordinates);
}
public function fields()
{
$fields = parent::fields();
Yii::debug('Fields before modification: ' . print_r($fields, true));
// 添加自定义字段
$fields['checkin_location_longitude'] = 'checkin_location_longitude';
$fields['checkin_location_latitude'] = 'checkin_location_latitude';
// 排除原始字段
unset($fields['checkin_location_coordinates']);
Yii::debug('Fields after modification: ' . print_r($fields, true));
return $fields;
}
$form = SurveyForm::find()->where([
'id' => $id,
])->with(['fields', 'fields.diagram_image', 'fields.options', 'cover_image', 'submit_success_image'])->limit(1)->asArray()->one();
unset($form['checkin_location_coordinates']);
4. I ultimately decided to abandon the point field type. I changed the schema to use checkin_location_longitude decimal(9,6) and checkin_location_latitude decimal(9,6).