Checking record for existence before inserting to db in one transaction

Yii 2.0 General Discussions
1

Hi guys, I need your help.

My script made syncronization job. Before adding new record to db, I need to check db for existence such record (depends on 2 columns). If it have not found, then record is inserted, else do nothing. How to do it in one transaction, for safety insert and avoiding duplications?

I used this code but it does not help.




$databases = \common\models\Database::find()->active()->all();

        foreach ($databases as $database) {

            $dbConnection = Yii::$app->get($database->name);

            $result = (new \yii\db\Query)

                ->select('tst.kod agent_id, tst.fio fullname, tst.depo_kod depo_id')

                ->from('sales tst')

                ->all($dbConnection);

            foreach ($result as $row) {

                $transaction = Agent::getDb()->beginTransaction();

                try {

                    $found = Agent::findOne([

                            'agent_id' => $row['agent_id'],

                            'database_id' => $database->id,

                    ]);

                    if ($found == null) {

                        $agent = new Agent();

                        $agent->agent_id = $row['agent_id'];

                        $agent->depo_id = $row['depo_id'];

                        $agent->database_id = $database->id;;


                        if ($agent->save()) {

                            $agentProfile = new AgentProfile();

                            $agentProfile->fullname = $row['fullname'];

                            $agentProfile->is_supervisor = false;

                            $agent->link('profile', $agentProfile);

                        } else {

                            Yii::error($agent->errors);

                            throw new \Exception('Ошибка при сохранении агента.');

                        }

                    }

                    $transaction->commit();

                } catch (\Exception $e) {

                    Yii::error($e->getMessage());

                    $transaction->rollBack();

                    throw $e;

                }

            }

        }

Calling this code immideately more than one time, there will be duplications on tables.

2

To avoid multiple executions together, you should track job execution in a table record and delete the record (or set a flag).

Then, to have better performance, you can check if record count is > 0:




                    $nr = Agent::find()->where([

                            'agent_id' => $row['agent_id'],

                            'database_id' => $database->id,

                    ])->count();

$found = ($nr>0);

if($found) {

...

}
3

Do you mean setting a flag of lock to access to table before processing request and unlock it on the end?

HOw to do it on postgres?

4

Exactly

Create a table, for example "Jobs" with date_start, date_end, executing. But it could be built with fields you need.

5

I got it, thank you.