How to create an insert into ... select ... on duplicate key update ... query through Laravel Query Builder?

Question

Laravel

There is a query, something like this (in fact, it is more complicated, a bunch of where and join conditions in the select):

insert into offers (sku, name, availability, price)
select concat(cast(products.id as char), 'ff'),
       products.name,
       products.availability,
       products.price
from products
where products.quantity > 0 &&
      products.price > 1000
on duplicate key update name         = value(name),
                        availability = value(availability),
                        price        = value(price);

How to write it through Laravel Query Builder?

For insert into ... on duplicate key update there is an upsert method
For insert into ... select there is a method insertUsing (not in the documentation, it seems like).

But I need to put it together somehow.

Answer 1

So far, I solved it by writing my own function for the builder:

Builder::macro('upsertUsing', function (array $columns, \Closure $query, array $update = null) {
  ...
});