0

I am using Yii2 and ActiveRecord. I have a field called "code" and for each record, it is meant to have a unique value like this: "REC0001", "REC0002", "REC0003" in a sequencial manner.

All works and I can generate a record code as described. However if I refresh my page request fast in a multiple manner (trying to test multiple requests at the same time in a very raw manner hehe), then some of the records end up with the same record code. In other words I found "REC007" a few times.

I generate the code looking at the last code and increase it by one, then I do a while foundFlag == true by checking to see if it already exists in the database.

I am suspecting there is a delay in writing to the database and hence it assumes that it is not there.

Here is a portion of the code:

static function createCode($rec){
    if ($rec->code){
        return $rec->code;
    }
    if ($rec->id){ // find it by id if one passed and record exists
        $tmpRec = $rec->find()
            ->where([
                'id'  => $rec->id,
            ])
            ->one();
        if ($tmpRec && $tmpRec->code){
            return $tmpRec->code;
        }
    }

    $prefix = 'REC';
    if (!$prefix){
        $prefix = 'REC';
    }
    $maxDecimals = 12;
    $codeLength = $maxDecimals+strlen($prefix);
    $query = $rec->find();
    $query = $query->where([
        'archived'  => '0'
    ]);

    // look under an organization if it exists in the model and there is one
    if ($rec->hasField('organization_id') && $organization_id){
        $query = addQueryWhere($query, [
            'organization_id'   => $organization_id,
        ]);
    }
    $query = addQueryWhere($query, [
        'LENGTH(code)'   => $codeLength*1,
    ]);
    $query = $query->orderBy('code desc');

    $lastRec = $query->one();

    $tmpNumber = 0;
    if ($lastRec && $lastRec->id){
        // check what it returns
        $tmpNumber = str_replace($prefix, '', $lastRec->code);
    }

    $tmpNumber++;
    $leftDecimals = $maxDecimals - strlen($tmpNumber.'');
    for ($k=0; $k <= $leftDecimals-1 ; $k++){
        $tmpNumber = '0'. $tmpNumber;
    }
    $ret = $prefix . $tmpNumber;
    return $ret;
}
public function generateCode($rec){
    $foundFlag = true;
    $break = 1000; // safe break point - no continuous loop
    $cnt = 0;
    $code = static::createCode($rec);
    while ($foundFlag === true || $cnt < $break){
        $tmpRec = $rec->find()
            ->where([
                'code'  => $code,
            ])
            ->one();
        if (!$tmpRec->id){
            $foundFlag = false;
            break;
        }
        $time = getCurrentTimestamp();
        $code = static::createCode($rec);
        $cnt++;
    }
    $ret = $code;
    return $ret;
}

So I simply call: $this->code = $this->generateCode(); Like I said it does work in generating the code, but it creates duplicates when it shouldn't!

Thank you for your assistance.

Mihai Pop
  • 65
  • 6
  • 1
    Generate the codes directly with a MySQL sentence or use [transactions](http://www.yiiframework.com/doc-2.0/yii-db-transaction.html) – gmc Apr 04 '17 at 13:11
  • You're doing a lot of work that duplicates the purpose of an autoincrement primary key. Let the database do the work for you. If you want to display the resulting integer as RECxxxx, do it in your view file. – sonofagun Apr 04 '17 at 23:12
  • gmc - will try that (transactions)! Thank you! – Mihai Pop Apr 05 '17 at 06:05
  • sonofagun: The reason for not relying on autoincrements is because I am doing an application that requires to work offline and when it goes online, then unique id's between other server applications must not conflict on unique id's / code's. So I am building the unique id's manually with a unique server prefix, while the record code's are more of a human readable, but sequenced value. And to avoid total conflict, I make use of batches of predefined codes for each server to ensure that when the records would synchronise (when it goes online), then they would merge and become sequenced. – Mihai Pop Apr 05 '17 at 06:08
  • So I tried direct SQL - same result. Will try the transactions – Mihai Pop Apr 05 '17 at 12:16

0 Answers0