Laravel / Eloquent - How Do You Run Multiple Raw SQL Statements At the Same Time?

Question

I have multiple SQL statements I'd like to run through Eloquent. They look something like this:

CREATE TEMPORARY TABLE cars
    AS (SELECT cars.id, cars.ip_address
        FROM cars
            JOIN colors ON colors.ip_address = cars.ip_address);

CREATE TEMPORARY TABLE boats
    AS (SELECT ip_address, MIN(id) AS id
        FROM colors
        GROUP BY ip_address);

CREATE TEMPORARY TABLE rvs
    AS (SELECT rvs.id, rvs.ip_address
        FROM rvs
            LEFT JOIN colors ON colors.id = rvs.id
        WHERE colors.id IS NULL);

/* Do things with temp tables here */

I need all of these statements to run in one connection session since they are using temporary tables. I've tried using DB::statement() which errors out at the start of the second statement operation and also DB::unprepared() which doesn't seem to run any of the operations.

Is there a way to run multiple raw statements using Eloquent?

Answer 1

I think you were on the right track with the unprepared statement, but I feel like it's possible that utilizing a transaction may be advantageous in your scenario. Furthermore, you'll want to use \DB::raw() which will execute your SQL statement as a flat string. Of course, do not put anything in here that could be user provided as you'd open yourself up to SQL injection given that you're bypassing parameterized queries.

try {
    \DB::transaction(function(){
        \DB::unprepared(\DB::raw('CREATE TEMPORARY TABLE cars AS (SELECT cars.id, cars.ip_address FROM cars JOIN colors ON colors.ip_address = cars.ip_address)'));

        \DB::unprepared(\DB::raw('CREATE TEMPORARY TABLE boats AS (SELECT ip_address, MIN(id) AS id FROM colors GROUP BY ip_address)'));

        \DB::unprepared(\DB::raw('CREATE TEMPORARY TABLE rvs AS (SELECT rvs.id, rvs.ip_address FROM rvs LEFT JOIN colors ON colors.id = rvs.id WHERE colors.id IS NULL)'));
    });

    \DB::commit();
} catch (\Exception $e){ 
    \DB::rollback();
    //do something with $e->getMessage();
}

Furthermore, this is actually Laravel's Query Builder and not Eloquent.