在使用laravel插入数据之前检查数据库并避免重复

Question

我试图在插入数据之前检查数据库以避免重复。 我将URL存储在数组中，然后将这些URL插入表的“链接”列中。 所以首先我要检查该列，是否设置相同的URL？ 如果不是，则使用此代码插入数据。 这是我完整的数据库插入代码：

foreach ($outlineUrls  as $outlineUrl) {
    $html = file_get_contents($outlineUrl);
    $DOMParser = new \DOMDocument();
    $DOMParser->loadHTML($html);


    $changeForMyDB = [
                            'remote_id' => '',
                            'region' => '関西',
                            'link' => json_encode($outlineUrl),
                            'name' => '',
                            'price' => '',
                            'extend' => '',
                            'address' => '',
                            'hows_old' => '',
                            'rooms' => '',
                            'old' => '',
                            'entery' => '',
                            'balcon_m2' => '',
                            'company_name' => '',
                            'script_from' => ''
                        ];

            $allPTags = $DOMParser->getElementsByTagName('p');
            foreach($allPTags as $ptag){
                if($ptag->getAttribute('class') == 'c-name'){
                        $changeForMyDB['name'] = trim($ptag->nodeValue);
                }
            }

            $changeForMyDB['address'] = trim($DOMParser->getElementsByTagName('dd')[0]->nodeValue);

            $allTables = $DOMParser->getElementsByTagName('table');
            foreach($allTables as $table){
                foreach($table->getElementsByTagName('tr') as $tr){
                    $property = trim($tr->getElementsByTagName('th')[0]->nodeValue);
                    $value = trim($tr->getElementsByTagName('td')[0]->nodeValue);

                    switch ($property) {
                        case '物件名':
                            $changeForMyDB['name'] = $value;
                            break;
                        case '販売価格':
                            $changeForMyDB['price'] = $value;
                            break;
                        case '専有面積':
                            $changeForMyDB['extend'] = $value;
                            break;
                        case '所在地':
                            $changeForMyDB['address'] = $value;
                            break;
                        case '総戸数':
                            $changeForMyDB['hows_old'] = $value;
                            break;
                        case '間取り':
                            $changeForMyDB['rooms'] = $value;
                            break;
                        case '竣工時期':
                            $changeForMyDB['old'] = $value;
                            break;
                        case '管理会社':
                            $changeForMyDB['company_name'] = $value;
                            break;
                        case '入居時期':
                            $changeForMyDB['entery'] = $value;
                            break;
                        case '入居時期':
                            $changeForMyDB['entery'] = $value;
                            break;
                        case 'バルコニー面積':
                            $changeForMyDB['balcon_m2'] = $value;
                            break;
                        default:
                            # code...
                            break;
                    }
                }
            }

if(Estates::where('link','=',$outlineUrl)->count() > 0)
{

   $this->error('There is link.');

} else {

        Estates::insertGetId($changeForMyDB);

        $this->line('Data saved.');

       }

但问题就在这里。它没有检查就插入了数据! 你知道是什么问题吗?谢谢你的帮助。

Answer 1

You can use the updateOrCreate() method to achieve the goal of only inserting if the record does not exist:

$estate = Estates::updateOrCreate(['link' =>  $outlineUrl], $changeForMyDB);

This will check if there is an Estate::where('link', '=', $outlineUrl). If none is found, it will create a new Estate´ and add all data from$changeForMyDBto it. If one exists, it will fetch it from the database, add all data from$changeForMyDB`. Then it will persist the changes. Read more about that here: https://laravel.com/docs/5.7/eloquent#other-creation-methods

---

To avoid issues with concurrent requests, you should also wrap your update within a database transaction. This ensures that the database has the same state when you checked for uniqueness as when you store your data.

DB::transaction(function () {
    $estate = Estates::updateOrCreate(['link' =>  $outlineUrl], $changeForMyDB);
});

If you don't want to update existing entries, use something like this:

DB::transaction(function () {
    if (Estates::where('link','=',$outlineUrl)->count() === 0) {
        $estate = Estates::create($changeForMyDB);
    }
});

Beware that Estates::create($data) and Estates::insert($data) are not the same. The create($data) function will create a model, fill it with the $data and return the persisted model. It will also fire all Eloquent events that are relevant for the create method. The insert($data) method on the other hand will not create an Eloquent model and will therefore also not fire any Eloquent events. It will directly insert the data into the database. This is more lightweight and faster, but you should keep that in mind.

---

If you want to check if the record already existed and report some kind of error, you can check with this:

if (!$estate->wasRecentlyCreated()) {
    // report error here
}