Execute raw SQL in Laravel 5.1 with the DB facade:
Read:
$users = DB::select('SELECT * FROM users WHERE active = ?', [1]); //Fetching "active" users, where active = 1
Write:
DB::insert('INSERT INTO users (id, name) VALUES (?, ?)', [1, 'John']); //Inserting our man John into users
Update:
DB::update('UPDATE users SET votes = 100 WHERE name = ?', ['John']); //Giving John a boost of confidence with 100 votes
Delete:
DB::delete('DELETE FROM users WHERE name = ?', ['John']); //Bye John, it was fun while it lasted
Bind parameters to prevent SQL injection. Use the corresponding method for your SQL operation.
More than Just CRUD
Sometimes, you'll need to execute more complex operations like joins or unions:
NON-SELECT SQL operation:
DB::statement('CREATE TABLE IF NOT EXISTS test_table (id int, name varchar(255))'); //When you need a "test_table" to test things
Multiple statements or special SQL:
DB::unprepared('DROP PROCEDURE IF EXISTS add_item //Dropping SQL "bomb" to eliminate a procedure
CREATE PROCEDURE add_item(IN name VARCHAR(255), OUT id INT)
BEGIN
INSERT INTO items (name) VALUES (name);
SET id = LAST_INSERT_ID();
END'); // Creating procedure to add item and return id
Joins and Unions
For when you need to get spicy with your SQL:
LEFT JOIN
$users = DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->select('users.*', 'posts.title as post_title')
->get(); //Love triangle between users, posts, and leftJoin
UNION
$first = DB::table('users')->whereNull('updated_at');
$second = DB::table('posts')->whereNull('deleted_at');
$union = $first->union($second)->get(); //Creating harmony with the union of "users" and "posts"
Grouping, Ordering and Keeping SQL Real
Using raw SQL in Laravel can be liberating. Especially when using GROUP BY and ORDER BY to keep results organized:
$grouped = DB::select('SELECT type, COUNT(*) as count FROM products GROUP BY type ORDER BY count DESC'); //Organized chaos
The Good, The Bad, & The Errors
Aliases and table naming are critical to avoid "Undefined property" errors or ambiguous column issues. Always double-check your SQL syntax and column references:
$result = DB::select('SELECT u.id as user_id, u.name FROM users as u'); //u.id or not u.id, that is the question
Handling Efficiency Like a Pro
Streamline your data retrieval when you're only interested in a single row. Opt for ->first():
$user = DB::table('users')->where('name', 'John')->first(); //When first love is the deepest
SQL in the Raw
Going raw will surprise you with its power. Here's how to do more with raw SQL:
Complex Joins:
$complexJoin = DB::select('SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 500'); //The SQL version of "it's complicated"
Subqueries:
$subquery = DB::select('SELECT * FROM (SELECT * FROM orders WHERE total > 500) as sub_orders'); //Inception, SQL-style
