Problems with validation when update data

Yii 2.0 General Discussions
1

Hello guys, I have a problem and I couldn’t find a solution yet.

I created a validation that checks the distance between points through latitude and longitude and allows the user to enter or not the data if the validation is met.

However, I have a problem now when I update the data, since the validation considers the data that is being updated and performs the validation indicating an error - and in fact it is not …

My validating function is this, sorry for any mistake, open to suggestions for code improvement

public function validateLatLong($attribute) {

$connection = Yii::$app->getDb();
$command = $connection->createCommand("        
    SELECT COUNT(*) FROM ( 
    SELECT l.id_occurrence AS id
    , l.specie_id AS especie
    , l.latitude
    , l.longitude
    , ( 3959 * acos( cos( radians(c.lat) ) * cos( radians( l.latitude ) ) * cos( radians( l.longitude ) - radians(c.lng) ) + sin( radians(c.lat) ) * sin( radians( l.latitude ) ) )* 1.609344 ) AS distancia 
    FROM occurrence AS l JOIN ( SELECT :p_lat AS lat, :p_long AS lng ) 
    AS c HAVING distancia<0.5 AND especie = :p_specie) AS TOTAL", [':p_specie' => $this->specie_id,':p_lat' => $this->latitude, ':p_long' =>$this->longitude]);
$result = $command->queryScalar();


if ($result > 0) {
    $this->addError($attribute, 'Já existe uma ocorrência desta espécie em um raio de     500m, adicione uma observação.');
    }
}
2

Hi @leofilipesilva,

I think you could add a condition of

id != :id
...
:id => $this->id

somewhere in your sql.

This condition has no effect when you are creating a new record, because id should be NULL.
But it will exclude the current record when you are updating it.

1 Like
3

Thank you very much for your feedback ,
the function was useful in the case of updates, but it ends up excluding validation in the case of insertion.
I’ve seen talk about scenarios, but I don’t know much about …
Would it be an option to create a role for each “create” and “update” scenario and each role for each distinct scenario?
The functions I already have :slight_smile:

4

my code looks like this

public function validateLatLong($attribute) {


        $connection = Yii::$app->getDb();
        $command = $connection->createCommand("        
            SELECT COUNT(*) FROM ( 
            SELECT l.id_occurrence AS id
            , l.specie_id AS especie
            , l.latitude
            , l.longitude
            , ( 3959 * acos( cos( radians(c.lat) ) * cos( radians( l.latitude ) ) * cos( radians( l.longitude ) - radians(c.lng) ) + sin( radians(c.lat) ) * sin( radians( l.latitude ) ) )* 1.609344 ) AS distancia 
            FROM occurrence AS l JOIN ( SELECT :p_lat AS lat, :p_long AS lng ) 
            AS c HAVING distancia<0.5 AND especie = :p_specie AND id != :p_id) AS TOTAL", [':p_id' => $this->id_occurrence, ':p_id' => $this->id_occurrence, ':p_specie' => $this->specie_id,':p_lat' => $this->latitude, ':p_long' =>$this->longitude]);
        $result = $command->queryScalar();
        
        

        if ($result > 0) {
            $this->addError($attribute, 'Já existe uma ocorrência desta espécie em um raio de 500m, adicione uma observação.');
        }

    }
5

My solution included

Use Scenario in Controller

public function actionCreate()
{
  $model = new User();
  $model->scenario="create";  // use create scenario, create scenario validaion applied in this model
            
  
}
public function actionUpdate()
{
    $model = new User();
    $model->scenario="update";  // use update scenario, update scenario validaion applied in this model
}

and Define scenario in model class

[['latitude', 'longitude'], 'validateLatLongC', 'on'=>['create']],
[['latitude', 'longitude'], 'validateLatLongU', 'on'=>['update']],