How to run complex sql query in Laravel

Question

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

e.g

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 this all in the one DB call from Laravel and get the results of that last SELECT statment?

I've tried doing something like this in laravel, but it gives me a MySql syntax error, which is strange as that exact sql works ok 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 can make this work?

JasonK · Accepted Answer · 2014-11-06 18:35:28Z

1

You need to use DB::raw() to make your query 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;
"));

I think it's even possible to simply use selectRaw() but I'm not sure. Also, you can select all rows from temp_foo like this:

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

answered Nov 6, 2014 at 18:35

JasonK

5,2949 gold badges36 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Twigg · Accepted Answer · 2014-11-06 18:25:38Z

0

You need to look at DB::raw() inside the select(), but also, try using this:

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

answered Nov 6, 2014 at 18:25

Daniel Twigg

7594 silver badges23 bronze badges

Comments

w1n78 · Accepted Answer · 2014-11-06 18:47:15Z

0

i've done something similar in a 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 may be able to use

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

answered Nov 6, 2014 at 18:47

w1n78

7891 gold badge7 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to run complex sql query in Laravel

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related