Laravel 4: how to run a raw SQL?

Question

I want to rename a table in Laravel 4, but don't know how to do that.

The SQL is alter table photos rename to images. If there is an Eloquent solution, I'd also like to know how to run a raw SQL, cause sometimes there's just no alternative.

Answer 1

In the Laravel 4 manual - it talks about doing raw commands like this:

DB::select(DB::raw('RENAME TABLE photos TO images'));

edit: I just found this in the Laravel 4 documentation which is probably better:

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

Update: In Laravel 4.1 (maybe 4.0 - I'm not sure) - you can also do this for a raw Where query:

$users = User::whereRaw('age > ? and votes = 100', array(25))->get();

Further Update If you are specifically looking to do a table rename - there is a schema command for that - see Mike's answer below for that.

Answer 2

Actually, Laravel 4 does have a table rename function in Illuminate/Database/Schema/Builder.php, it's just undocumented at the moment: Schema::rename($from, $to);.

Answer 3

You can also use DB::unprepared for ALTER TABLE queries.

DB::unprepared is meant to be used for queries like CREATE TRIGGER. But essentially it executes raw sql queries directly. (without using PDO prepared statements)

https://github.com/laravel/framework/pull/54

Answer 4

The best way to do this I have found so far it to side step Laravel and execute the query directly using the Pdo object.

Example

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

I usually find it faster and more efficient for a one time query to simply open my database query tool and type the query with full syntax checking then execute it directly.

This becomes essential if you have to work with stored procedures or need to use any database functions

Example 2 setting created_at to a the value you need it to be and side steeping any carbon funkiness

$sql = 'UPDATE my_table SET updated_at = FROM_UNIXTIME(nonce) WHERE id = ' . strval($this->id);
DB::statement($sql);

I found this worked in a controller but not in a migration

Answer 5

The accepted way to rename a table in Laravel 4 is to use the Schema builder. So you would want to do:

Schema::rename('photos', 'images');

From http://laravel.com/docs/4.2/schema#creating-and-dropping-tables

If you really want to write out a raw SQL query yourself, you can always do:

DB::statement('alter table photos rename to images');

Note: Laravel's DB class also supports running raw SQL select, insert, update, and delete queries, like:

$users = DB::select('select id, name from users');

For more info, see http://laravel.com/docs/4.2/database#running-queries.

Answer 6

This is my simplified example of how to run RAW SELECT, get result and access the values.

$res = DB::select('
        select count(id) as c
        from prices p 
        where p.type in (2,3)
    ');
    if ($res[0]->c > 10)
    {
        throw new Exception('WOW');
    }

If you want only run sql script with no return resutl use this

DB::statement('ALTER TABLE products MODIFY COLUMN physical tinyint(1) AFTER points;');

Tested in laravel 5.1

Answer 7

Laravel raw sql – Insert query:

lets create a get link to insert data which is accessible through url . so our link name is ‘insertintodb’ and inside that function we use db class . db class helps us to interact with database . we us db class static function insert . Inside insert function we will write our PDO query to insert data in database . in below query we will insert ‘ my title ‘ and ‘my content’ as data in posts table .

put below code in your web.php file inside routes directory :

Route::get('/insertintodb',function(){
DB::insert('insert into posts(title,content) values (?,?)',['my title','my content']);
});

Now fire above insert query from browser link below :

localhost/yourprojectname/insertintodb

You can see output of above insert query by going into your database table .you will find a record with id 1 .

Laravel raw sql – Read query :

Now , lets create a get link to read data , which is accessible through url . so our link name is ‘readfromdb’. we us db class static function read . Inside read function we will write our PDO query to read data from database . in below query we will read data of id ‘1’ from posts table .

put below code in your web.php file inside routes directory :

Route::get('/readfromdb',function() {
    $result =  DB::select('select * from posts where id = ?', [1]);
    var_dump($result);
});

now fire above read query from browser link below :

localhost/yourprojectname/readfromdb