Raw queries

You can use:

$results = DB::select('select * from users where id = ?', [1]);

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);

DB::update('update users set votes = 100 where name = ?', ['John']);

DB::delete('delete from users');

result is an array of obj or update and delete statements return the number of rows affected by the operation

From the docs ;)

What if I wanted to use the following?

UPDATE table SET y = 2 WHERE x = 1

or an INSERT query or anything else? Because I have very complex SQL queries which include joins, ifs cases etc.

Try

DB::update('UPDATE table SET y = ? WHERE x = ?', [2,1]);

can you post an exemple?

I don't have it with me (I'm not in the office right now, writing on my mobile phone) but I am interested in SELECTs with JOINs, UPDATE queries with CASEs etc, such as

UPDATE
    table_name
SET 
  column_a = CASE WHEN @flag = '1' THEN column_a + @new_value ELSE column_a END
WHERE
    id = @ID

You can use Eloquent for query and DB:raw

User::where('id',1)
    ->update(array('status' => DB:raw('CASE WHEN @flag = '1' THEN column_a + @new_value ELSE column_a END')));

or full query raw.

DB::update('UPDATE table_name SET column_a = CASE WHEN ? = '1' THEN column_a + ? ELSE column_a END WHERE id = ?', [$flag,$new_value, $id]);

I like Eloquent with DB:raw but you can prepare the query string with php and pass all in raw query.

Thanks, gonna try it!

@toniperic There are 2 additional methods for advanced raw queries statement and unprepared:

// with bindings
DB::statement('update users join (select @id = 0) as var set id = (@id := @id + 1) where some_column < ?', [10]);
// returns boolean

// completely raw
DB::unprepared('update users join (select @id = 0) as var set id = (@id := @id + 1) where some_column < 10');
// returns boolean

And btw DB::insert is just a wrapper for DB::statement returning boolean and DB::update / delete are wrappers for DB::statement but return affected rows number.

Oh, didn't know this.

I suppose using DB::statement() protects from injections as well? And what are the returned values from those method calls?

Great advice @JarekTkaczyk, gonna try it when I get back to the office!

@toniperic Check my edited answer above. And yes, these with bindings are run through PDO, thus injection-safe.

Great answer @JarekTkaczyk, many thanks!

I am using mssql with laravel 5.1, and I know Laravel can do this:

$num_of_updated_records = DB::update(DB::raw('UPDATE [dbo].[myTable] SET selection=1 WHERE id IN (1,2,3)'));
$updated_record_ids = DB::select(DB::raw('SELECT id FROM [dbo].[myTable] WHERE selection=1'));

But it will hit the DB 2 times.

What if I want to do this to avoid hitting the DB twice:

DECLARE @MyTableVar table(selected_id bigint NOT NULL)
UPDATE [dbo].[myTable] SET selection=1
OUTPUT inserted.id INTO @MyTableVar
WHERE id IN (1,2,3)
SELECT selected_id FROM @MyTableVar

DB::select() will just return empty array.

DB::statement() will just return true.

DB::update() will only return integer 3.

Does Laravel have a function for such query to return the data from that last select of the temp table?

Thanks.