Laravel / Eloquent - 如何同时运行多个原始SQL语句？

Question

我有多个SQL语句需要通过Eloquent运行。它们看起来像这样：

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 */

我需要在一个连接会话中运行所有这些语句，因为它们使用临时表。我尝试过使用 DB::statement()，但在第二个语句操作开始时出错，并且使用 DB::unprepared() 似乎不运行任何操作。

有没有一种使用 Eloquent 运行多个原始语句的方法？

- Lloyd Banks

Answer 1

我认为你在使用unprepared语句方面是正确的，但我觉得在你的情况下利用事务可能会更有优势。此外，你需要使用\DB::raw()来将你的SQL语句作为纯字符串执行。当然，不要在这里放置任何用户提供的内容，因为你正在绕过参数化查询，这将使你容易受到SQL注入攻击。

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();
}

此外，这实际上是Laravel的查询构建器而不是Eloquent。

- Ohgodwhy

Answer 2

你应该使用 Laravel 自定义查询。请参考Laravel 文档了解详情。

$query = DB::select("       
            SELECT
                            cars.id,
                            cars.ip_address,
                        FROM cars
                        JOIN colors
                        ON colors.ip_address = cars.ip_address
                        UNION
                        SELECT
                            ip_address,
                            MIN(id) AS id,
                        FROM colors
                        GROUP BY ip_address
            UNION
                        SELECT
                            rvs.id,
                            rvs.ip_address,
                        FROM rvs
            LEFT JOIN colors 
                        ON colors.id = rvs.id
                        WHERE colors.id IS NULL
               ");

- Vaishal Gandhi