How to execute complex sql query in Laravel

Question

I have complex SQL that consists of a series of non-query statements that use temporary tables in MySql, and then a SELECT statement at the end to return the result.

eg

DROP TABLE IF EXISTS temp_foo;

CREATE TEMPORARY TABLE IF NOT EXISTS temp_foo AS (
    SELECT *
    FROM foo
);

SELECT * from temp_foo;

How can I run all this in a single DB call from Laravel and get the results of this last SELECT state?

I tried doing something like this in laravel, but it gives me MySql syntax error, which is strange since this exact sql works fine when I run it directly in MySQl.

DB::select("

    DROP TABLE IF EXISTS temp_foo;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_foo AS (
        SELECT *
        FROM foo
    );

    SELECT * from temp_foo;

");

Any ideas on how to do this?

Answer 1

You need to use DB :: raw () for your query to work, for example:

DB::select(DB::raw("
    DROP TABLE IF EXISTS temp_foo;
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_foo AS (
        SELECT *
        FROM foo
    );
    SELECT * from temp_foo;
"));

, selectRaw(), . , temp_foo :

DB::table('temp_foo')->get();

Answer 2

DB:: raw() select(), :

DB::statement('drop table users');

Answer 3

I did something similar in migration where I had to create and run a stored procedure. in my up () I used something like this.

public function up() {
  $sql = <<<SQL
    DROP TABLE IF EXISTS temp_foo;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_foo AS (
      SELECT *
      FROM foo
    );

    SELECT * from temp_foo;
  SQL;

  DB::connection()->getPdo()->exec($sql);
}

you can use

DB::connection()->getPdo()->exec($sql);