CODEWITHSUNDEEP
phplaravel-5.7
Bob Tymczasowy
Bob Tymczasowy

Reputation: 73

Is there a insertUsing with ignore option?

In Laravel there exist two useful methods in DB facade:

I need to combine these two approaches i.e. insert data based on existing data and ignore duplicates. Do you know any way to do it in Laravel 5.7 or newer?

Upvotes: 4

Views: 1643

Answers (1)

Bob Tymczasowy
Bob Tymczasowy

Reputation: 73

There is no one simple method to do this. But it can be achieved with facade DB.

Let assume we have 2 models:

class Car extends Model
{
    protected $table = "cars";

    protected $fillable = [
        "name",
        "production_year",
    ];
}

class Antique extends Model
{
    protected $table = "antiques";

    protected $fillable = [
        "name",
        "production_year",
        "category",
    ];
}

Assume that our task is to find all cars that were created before 1990 and basing on them create antiques with category 'car', ignoring all cars that already exist in antiques table.

This is the solution.

$selectQuery = Car::where('production_year', '<', 1990)->select([
    'name',
    'production_year',
    DB::raw('"car" as category'),
]);
$antiquesTable = (new Antique())->getTable();
$insertQuery = "INSERT IGNORE INTO $antiquesTable (
        `name`,
        `production_year`,
        `category`
    ) "
    . $selectQuery->toSql();
DB::insert($insertQuery, $selectQuery->getBindings());

It will generate the following SQL query

INSERT IGNORE INTO antiques (`name`, `production_year`, `category`)
SELECT `name`, `production_year`, "car" AS category FROM cars WHERE `production_year` < 1990

Upvotes: 1

Related Questions