How to Execute Raw Queries in Laravel?

Laravel Query Builder allows us to experiment with database queries even if we have basic knowledge of SQL. Lets see how. We have a product table below. You can see simple select query below.

$products = DB::select ('select * from products');

This will return all products in Array form. If we want to check that our query is a right SQL query in syntax then we can use this function.

$products = DB::statement('select * from products');

This will return true if statement is a right one. We can also write few queries with where statements.

$products = DB::select('select * from products where name = "purel"');

To run these queries we do not have to create a Model. We only need to write this line before our class in controller and that’s it.

use Illuminate\Support\Facades\DB;

Now in order to insert we can write this query.

DB::insert('INSERT INTO products (name, price, description) VALUES ("purel", 100, "it is a hands sanitizer.")');

We can also write update query.

DB::insert('Update products SET price = 110 where name="purel"');

Even we can create a table in database using Schema Builder. We need to add this line before our class in controller.

use Illuminate\Support\Facades\Schema;

and then we can write this query.

Schema::create( 'products', function ( $table ) {
	$table->increments('id')->unsigned();;
	$table->string( 'name' );
	$table->integer('price');
} );

and if we have already created products table and we want to add columns then.

Schema::table( 'products', function ( $table ) {
	$table->string( 'name' );
	$table->integer('price');
} );

Imagine we are using different databases. Mongo, SQL and sqlLite. We can specify connection and then run our query.

Schema::connection('sql')->create('products', function($table) {
	$table->increments('id');
});

We can also drop tables using this query.

Schema::drop('products');
Schema::dropIfExists('products');

We can also update existing table.

Schema::table('products', function($table) {
	$table->text('description');
});

Now we can write more queries using Query builder. This time we do not even need to use SQL syntax.

$products = DB::table('products')->select('*')->where('name', 'purel')->get();

We can use joins like this. This is inner join.

$products = DB::table('products')->select('*')->join('categories', 'category_id', '=', 'categories.id')->get();

we can use these queries to get left and right joins.

$products = DB::table('products')->select('*')->rightJoin('categories', 'category_id', '=', 'categories.id')->get();
$products = DB::table('products')->select('*')->leftJoin('categories', 'category_id', '=', 'categories.id')->get();

I hope now you can run handful of SQL queries in Laravel. If you have any problems please leave a comment.