Help with Controller - Violation of UNIQUE KEY constraint

Yii 2.0 General Discussions
1

I am trying to build a create action and struggling, not seeing my mistake.

I have the following:

$modelProjId = Projects::find()
    ->select('ProjId')
    ->where(['dtCreation' => $model->dtCreation])
    ->andWhere(['dtModification' => $model->dtModification])
    ->scalar();
$model->ProjId = $modelProjId;

foreach ($modelsLegs as $i => $modelLeg) {
    $modelLeg->ProjId = $model->ProjId;
    if (!($flag = $modelLeg->save(false))) {
        return json_encode(['status' => 'Error', 'message' => 'Leg not created!']);
        break;
    }
}

foreach ($modelsLegs as $i => $modelLeg) {
    if(isset($modelsLegsRates[$i]) && is_array($modelsLegsRates[$i])){
        foreach ($modelsLegsRates[$i] as $ix => $modelsLegsRate) {
            $modelsLegsRate->LegId = $modelLeg->LegId;
            if (! ($go = $modelsLegsRate->save(false))) {
                return json_encode(['status' => 'Error', 'message' => 'Leg Rate not created!']);
                $transaction->rollBack();
                break;
            }
        }
    }
}

my issue is with the last foreach to process the $modelsLegsRates. It returns an error:

PDOException: SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of UNIQUE KEY constraint ‘FK_projects_legs_rates_LegId’.

but there should be no duplication. The previous loop save each $modelLeg, so they will have a unique PK value, and then I loop over each and assign it to the $modelsLegsRate->LegId.

I have confirm that the project ($model) is created, each leg ($modelLegs/$modelLeg) is properly created, and the 1st ($modelsLegsRates/$modelsLegsRate) is created, but every subsequent one generates this error.

What am I missing, doing wrong here?

Why does

$modelsLegsRate->LegId = $modelLeg->LegId;

not work?

2

In the most inner foreach loop you are assign the same LegId to the bunch of modelsLegRate objects. And it looks like you have unique key constrain in the table mapped to the object modelsLegsRate (in your mssql db, it even has it’s name). Can you show us schema of the table (keys and constrains)?

3

I can if you truly think it will be helpful, but in this instance the schema is not the issue and the constraint is fine. I believe this to simply be a PHP coding issue.

Let me elaborate. I’m using the dynamicform to allow multiple entries within the main project. So a Project can have multiple Leg and each Leg will then have a single Leg Rate

My issue is simply assigning the LegId to the corresponding Leg Rate

The data is:

$_POST = [
‘_csrf-backend’ => ‘bpdvnV5k4TjbiDc87S8UJvZx-bgBZ2GVVM9UXgR6AJWcgKLODrg7ohYeKSloOShYNu9MEKEUcnMUi3dQ==’
‘financeUser’ => ‘1’
‘Projects’ => [
‘ClientId’ => ‘174’
‘ContactId’ => ‘1826’
‘ProjNo’ => ‘69117’
‘DispatcherId’ => ‘1’
‘SourceId’ => ‘2’
‘Invoiced’ => ‘0’
]
‘rowSeqNo’ => ‘’
‘ProjectsLegs’ => [
0 => [
‘LegNo’ => ‘1’
‘StatusId’ => ‘5’
‘ServTypeId’ => ‘4’
‘DispatcherId’ => ‘1’
]
1 => [
‘LegNo’ => ‘2’
‘StatusId’ => ‘5’
‘ServTypeId’ => ‘3’
‘DispatcherId’ => ‘1’
]
]
‘ProjectsLegsRates’ => [
0 => [
0 => [
‘VendorMileage’ => ‘1.25’
‘VendorFS’ => ‘0.00’
‘VendorMini’ => ‘0.00’
‘VendorDayRate’ => ‘0.00’
‘VendorOvernight’ => ‘0.00’
‘VendorNoGo’ => ‘0.00’
‘VendorDownTime’ => ‘0.00’
‘VendorWorkingTime’ => ‘0.00’
‘VendorFlat’ => ‘0.00’
‘ClientMileage’ => ‘1.85’
‘ClientFS’ => ‘0.00’
‘ClientMini’ => ‘300.00’
‘ClientDayRate’ => ‘550.00’
‘ClientOvernight’ => ‘120.00’
‘ClientNoGo’ => ‘0.00’
‘ClientDownTime’ => ‘30.00’
‘ClientWorkingTime’ => ‘40.00’
‘ClientFlat’ => ‘0.00’
‘ApprovedPer’ => ‘’
]
]
1 => [
0 => [
‘VendorMileage’ => ‘0.00’
‘VendorFS’ => ‘0.00’
‘VendorMini’ => ‘0.00’
‘VendorDayRate’ => ‘0.00’
‘VendorOvernight’ => ‘0.00’
‘VendorNoGo’ => ‘0.00’
‘VendorDownTime’ => ‘0.00’
‘VendorWorkingTime’ => ‘0.00’
‘VendorFlat’ => ‘0.00’
‘ClientMileage’ => ‘2.05’
‘ClientFS’ => ‘0.00’
‘ClientMini’ => ‘350.00’
‘ClientDayRate’ => ‘650.00’
‘ClientOvernight’ => ‘120.00’
‘ClientNoGo’ => ‘0.00’
‘ClientDownTime’ => ‘30.00’
‘ClientWorkingTime’ => ‘40.00’
‘ClientFlat’ => ‘0.00’
‘ApprovedPer’ => ‘’
]
]
]
‘CustomerTotal’ => ‘’
‘refBtn’ => ‘’
‘whichSubmitbtn’ => ‘1’
]

So ProjectsLegsRates[0][0] should be associated with ProjectsLegs[0] and ProjectsLegsRates[1][0] should be associated with ProjectsLegs[1].

So when I iterate through the leg to save them I then associate the LegId to the appropriate ProjectsLegsRates[?][0].

If it make any difference, here is how I was handling to populating $modelsLegsRates


$ProjectsLegsRatesData['_csrf-backend'] =  Yii::$app->request->post()['_csrf-backend'];
            for ($i=0; $i < count($modelsLegs); $i++) {
                if(isset(Yii::$app->request->post()['ProjectsLegsRates'][$i])){
                    $ProjectsLegsRatesData['ProjectsLegsRates'] = Yii::$app->request->post()['ProjectsLegsRates'][$i];
                    $modelsLegsRates[$i] = Model::createMultipleProjectsLegsRates(ProjectsLegsRates::class, [] ,$ProjectsLegsRatesData);
                    Model::loadMultiple($modelsLegsRates[$i], $ProjectsLegsRatesData);
                }
            }
4

Which table throws this error? Share your relevant db schema

5

I truly think it will be helpful :slight_smile:
The error message points quite clearly “FK_projects_legs_rates_LegId”. I bet it is Foreign Key constrain on ‘ProjectsLegsRates’ table. Can we see it?

6

I finally resolved the issue. It was a coding, not understanding the way that Yii bring s back the data from the page. Since this is a 1-1 relationship, I was able to eliminate the loop and simply process the first element in the array and everything worked perfectly.

7

When you ask for the schema, what exactly are you wanting me to post? model?something from the db directly? I’d like to know for future reference.

8

Database Table structure with relations.