Is there a insertUsing with ignore option?

Question

In Laravel there exist two useful methods in DB facade:

• insertOrIgnore() - allows ignore action when duplicate is being inserted
• insertUsing() - allows insert data based on data from another tables

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?

Answer 1

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