Laravel mass insert for raw query

Question

I have raw query:

INSERT INTO employee (fk_country_id, employee_id, fk_city_id, password, role, email, joined_at, resigned_at, created_at, updated_at) VALUES (?, ?, (SELECT id FROM city WHERE city.id = ?), ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE email = VALUES(email), joined_at = VALUES(joined_at), resigned_at = VALUES(resigned_at)

I am executing the query like this:

DB::insert($query, $parameters);

where the values of the parameters are in the form of array.

Since there are huge amount of rows for the INSERT and UPDATE opertion, I need to optimize the query to insert values of multiple rows in a single query.

How can I optimize my query with the help of Laravel?

There are some options which allows to run queries with nested arrays but I wasn't able to get that working with:

DB::insert();

I want to avoid the use of "(?, ?, ?), (?, ?, ?)...." during INSERT operation. How can Laravel facilitate this sort of thing to make the code look cleaner and optimized?

Ayurveda

I do not get your question properly, but make yourself an array $data = ['column' => 'value, ...], ..] and use Model::insert($data) – Timework

I cannot use Model::insert($data), I need to use raw query for inserts (with DB::insert()), because there is nested select in the query and ON DUPLICATE KEY UPDATE part. So I am basically asking if there is support for mass assignment for raw queries and by raw I really mean raw not some kind of partially raw query using eloquent. – Ayurveda

Take a look at this: github.com/yadakhov/insert-on-duplicate-key – Mystic

@JonasStaudenmeir that looks really good, only thing is I have the inner select query as one of the values, I don't think they support it but I will try – Ayurveda

Did you get a solution? – Goulet

I don't have any news on that. I think I just went with the solution I mentioned. It is 2 years ago so maybe Laravel matured and there is better solution now. – Ayurveda

Laravel just released the function upsert. I don't know about mass insertion, but it will manage ON DUPLICATE KEY UPDATE – Varus

Answer 1

For raw batch insert you could use:

DB::unprepared("
    insert into users (email, votes) values ('[email protected]', 10);
    insert into users (email, votes) values ('[email protected]', 12);
");

Just be careful of SQL injection!

This could be very useful when quickly populating database in tests.

Oak

Exactly what I was looking for to populate my database during tests. – Typesetter