Laravel Database Raw SQL, Query Builder and Eloquent ORM

[Contents]

1. Raw SQL via DB Facade
2. Chaining with the Query Builder
3. Eloquent ORM
4. Reference

As we all agree Laravel is created using php and support most (if not all) native php methods. This is also true when comes to database operations in Laravel.

In this post, I will demostrate the differences between using row SQL, chained Query Builder as well Eloquent ORM when play with database in Laravel.

1. Raw SQL via DB Facade

// UserController
use Illuminate\Support\Facade\DB;
...

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

// Raw select, and parameter binding
DB::select('select * from contacts where validated = ?', [true]);

// Select using the fluent builder
$users = DB::table('users')->get();

// Joins and other complex calls
DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.type', 'donor');
    })
    ->get();

It is possible to make any raw call to database using the DB facade with statement() method, DB::statement('SQL statement here')

But there are also specific methods for various common actions: select(), insert(), update(), and delete().

Method	Example
Raw `select`	`DB::select('select * from users');`
Raw `insert`	`DB::insert('insert into contacts (name, email) values (?, ?)', ['sally', '[email protected]']);`
Raw `update`	`DB::update('update contacts set status = ? where id = ?', ['donor', $id]);`
Raw `delete`	`DB::delete('delete from contacts where archived = ?', [true]);`

2. Chaining with the Query Builder

// UserController
use Illuminate\Support\Facade\DB;
...
$usersOfType = DB::table('users')
    ->where('type', $type)
    ->get();

Let’s take a look at what methods the query builder allows us to chain. The methods can be split up into four categories: constraining methods, modifying methods, conditional methods, and ending/returning methods.

constraining methods	modifying methods	conditional methods	ending/returning methods
`select()`	`orderBy(colName, direction)`	`when()`	`get()`
`where()`	`groupBy()` and `having()` or `havingRaw()`	`unless()`	`first()` and `firstOrFail()`
`orWhere()`	`skip()` and `take()`		`find(id)` and `findOrFail(id)`
`whereBetween(colName, [low, high])`	`latest(colName)` and `oldest(colName)`		`value()`
`whereIn(colName, [1, 2, 3])` `whereInRaw(colName, [1, 2, 3])` `whereIntegerInRaw(colName, [1, 2, 3])`	`inRandomOrder()`		`count()`
`whereNull(colName) and whereNotNull(colName)`			`min()` and `max()`
`whereRaw()`			`sum()` and `avg()`
`whereExists()`
`distinct()`

Further than the listed, custom methods exist for making raw statement - for example, select() has a selectRaw() counterpart that allows you to pass in a string for the query builder to place after the WHERE statement.

One can however pass in the result of a DB::raw() call to almost any method in the query builder to achieve the same result.

Other SQL native methods like join and union are also made possible in Query Builder.

Raw Methods	Join Methods	Union Methods	Insert/Update/Delete
`selectRaw()`	`join()`	`union()`	`insert()` / `insertOrIgnore()`
`whereRaw()` / `orWhereRaw()`	`leftJoin()` / `rightJoin()`	`unionAll()`	`upsert()`
`havingRaw()` / `orHavingRaw()`	`crossJoin()`		`update()` `increment()` `decrement()` json `update()`
`orderBy()`	Advanced Join Clauses		`delete()` `truncate()`
`groupBy()`	`joinSub()`		`transaction()`

2.1. Constraining Methods

Method	Funciton
`select()`	Allows you to choose which columns you’re selecting
`where()`	Allows you to limit the scope of what’s being returned using `WHERE`
`orWhere()`	Creates simple `OR WHERE` statements
`whereBetween(colName, [low, high])`	Allows you to scope a query to return only rows where a column is between two values (inclusive of the two values)
`whereIn(colName, [1, 2, 3])` `whereInRaw(colName, [1, 2, 3])` `whereIntegerInRaw(colName, [1, 2, 3])`	Allows you to scope a query to return only rows where a column value is in an explicitly provided list of options
`whereNull(colName) and whereNotNull(colName)`	Allow you to select only rows where a given column `is NULL` or `is NOT NULL`, respectively
`whereRaw()`	Allows you to pass in a raw, unescaped string to be added after the `WHERE` statement
`whereExists()`	Allows you to select only rows that, when passed into a provided subquery, return at least one row
`distinct()`	Selects only rows where the selected data is unique when compared to the other rows in the returned data

2.1.1. Examples

2.1.1.1. `select()`

// select() method
$emails = DB::table('contacts')
    ->select('email', 'email2 as second_email')
    ->get();
// Or
$emails = DB::table('contacts')
    ->select('email')
    ->addSelect('email2 as second_email')
    ->get();

2.1.1.2. `where()`

// where() method
$newVips = DB::table('contacts')
    ->where('vip', true)
    ->where('created_at', '>', now()->subDay());
// Or
$newVips = DB::table('contacts')->where([
    ['vip', true],
    ['created_at', '>', now()->subDay()],
]);

2.1.1.3. `orWhere()`

// orWhere() method with a closure
$contacts = DB::table('contacts')
    ->where('vip', true)
    ->orWhere(function ($query) {
        $query->where('created_at', '>', now()->subDay())
            ->where('trial', false);
    })
    ->get();

2.1.1.3.1. Caution of using multiple `orWhere()`

$canEdit = DB::table('users')
    ->where('admin', true)
    ->orWhere('plan', 'premium')
    ->where('is_plan_owner', true)
    ->get();

// is equivalent to 

SELECT * FROM users
    WHERE admin = 1
    OR plan = 'premium'
    AND is_plan_owner = 1;

and,

$canEdit = DB::table('users')
    ->where('admin', true)
    ->orWhere(function ($query) {
        $query->where('plan', 'premium')
            ->where('is_plan_owner', true);
    })
    ->get();

// is equivalent to

SELECT * FROM users
    WHERE admin = 1
    OR (plan = 'premium' AND is_plan_owner = 1);

2.1.1.4. `whereBetween()`

// whereBetween() method
$mediumDrinks = DB::table('drinks')
    ->whereBetween('size', [6, 12])
    ->get();

2.1.1.5. `whereIn()`

// whereIn() method
$closeBy = DB::table('contacts')
    ->whereIn('state', ['FL', 'GA', 'AL'])
    ->get();

2.1.1.6. `whereRaw()`

// whereRaw() method
// BEWARE OF SQL INJECTION! ALWAYS AVOID!
$goofs = DB::table('contacts')->whereRaw('id = 12345')->get()

2.1.1.7. `whereExist()`

// whereExist() method
$commenters = DB::table('users')
    ->whereExists(function ($query) {
        $query->select('id')
            ->from('comments')
            ->whereRaw('comments.user_id = users.id');
    })
    ->get();
// get those users who have left at least one comment

2.1.1.8. `distinct()`

// distinct() method
$lastNames = DB::table('contacts')->select('city')->distinct()->get();

2.2. Modifying Methods

Method	Function
`orderBy(colName, direction)`	Orders the results. The second parameter may be either `asc` (the default, ascending order) or `desc` (descending order)
`groupBy()` and `having()` or `havingRaw()`	Groups your results by a column. Optionally, `having()` and `havingRaw()` allow you to filter your results based on properties of the groups
`skip()` and `take()`	Most often used for pagination, these allow you to define how many rows to return and how many to skip before starting the return
`latest(colName)` and `oldest(colName)`	Sort by the passed column (or created_at if no column name is passed) in descending (`latest()`) or ascending (`oldest()`) order
`inRandomOrder()`	Sorts the result randomly

2.2.1. Examples

2.2.1.1. `orderBy(colName, direction)`

// orderBy() method
$contacts = DB::table('contacts')
    ->orderBy('last_name', 'asc')
    ->get();

2.2.1.2. `groupBy()`

// groupBy() method
$populousCities = DB::table('contacts')
    ->groupBy('city')
    ->havingRaw('count(contact_id) > 30')
    ->get();

2.2.1.3. `skip()` and `take()`

// skip() and take()
// returns rows 31-40
$page4 = DB::table('contacts')->skip(30)->take(10)->get();

2.3. Conditional Methods

Method	Function
`when()`	Given a truthy first parameter, applies the query modification contained in the closure; given a falsy first parameter, it does nothing
`unless()`	The exact inverse of when(). If the first parameter is falsy, it will run the second closure

2.3.1. Examples

2.3.1.1. `when()`

$status = request('status'); // Defaults to null if not set

$posts = DB::table('posts')
    ->when($status, function ($query) use ($status) {
        return $query->where('status', $status);
    })
    ->get();

// Or
$posts = DB::table('posts')
    ->when($ignoreDrafts, function ($query) {
        return $query->where('draft', false);
    })
    ->get();

You can also pass a third parameter, another closure, which will only be applied if the first parameter is falsy.

2.4. Ending/Returning Methods

These methods stop the query chain and trigger the execution of the SQL query. Without one of these at the end of the query chain, your return will always just be an instance of the query builder

Method	Function
`get()`	Gets all results for the built query
`first()` and `firstOrFail()`	Get only the first result—like `get()`, but with a `LIMIT 1` added
`find(id)` and `findOrFail(id)`	Like `first()`, but you pass in an ID value that corresponds to the primary key to look up
`value()`	Plucks just the value from a single field from the first row
`count()`	Returns an integer count of all of the matching results
`min()` and `max()`	Return the minimum or maximum value of a particular column
`sum()` and `avg()`	Return the sum or average of all of the values in a particular column

2.4.1. Examples

2.4.1.1. `get()`

// get() method
$contacts = DB::table('contacts')->get();
$vipContacts = DB::table('contacts')->where('vip', true)->get();

2.4.1.2. `first()` and `firstOrFail()`

// first() method
$newestContact = DB::table('contacts')
    ->orderBy('created_at', 'desc')
    ->first();

first() fails silently if there are no results, whereas firstOrFail() will throw an exception.

If you pass an array of column names to either method, it will return the data for just those columns instead of all columns.

2.4.1.3. `find(id)` and `findOrFail(id)`

// find() method
$contactFive = DB::table('contacts')->find(5);

find() fails silently if a row with that ID doesn’t exist, while findOrFail() will throw an exception.

2.4.1.4. `value()`

// value() method
$newestContactEmail = DB::table('contacts')
    ->orderBy('created_at', 'desc')
    ->value('email');

2.4.1.5. `count()`

// count() method
$countVips = DB::table('contacts')
    ->where('vip', true)
    ->count();

2.4.1.6. `min()` and `max()`

// max() method
$highestCost = DB::table('orders')->max('amount');

2.4.1.7. `sum()` and `avg()`

// avg() method
$averageCost = DB::table('orders')
    ->where('status', 'completed')
    ->avg('amount');

2.5. Raw Queries and Raw Query inside Query Builder

2.5.1. Raw Query

2.5.2. Raw Query inside Query Builder

2.6. Join Clauses

2.7. Unions

2.8. Insert/Update/Delete/Transaction

3. Eloquent ORM

// ContactController
namespace App;
...
use Illuminate\Database\Eloquent\Model;
...
class Contact extends Model
...
public function show($contactId)
{
    return view('contacts.show')
        ->with('contact', Contact::findOrFail($contactId));
}

3.1. Eloquent Methods

3.1.1. Retrieving Data

3.1.2. Inserts and Updates

3.1.3. Delection

3.2. Query Scopes

3.2.1. Global Scope

3.2.2. Local Scope

3.3. Mutators & Casting

3.3.1. Accessors, Mutators

3.3.2. Attribute Casting

3.3.3. Custom Casts

3.4. Eloquent Collections

3.4.1. Collection Methos

3.5. Eloquent Serialization

3.5.1. To Arrays

3.5.2. To JSON

3.6. Eloquent Relationships

3.7. Manipulation of Relationships

3.8. Eager Loading

3.8.1. Constraining Eager Loads

3.8.2. Lazy Eager Loading

3.8.3. Preventing Lazy Loading

3.9. Eloquent Events

3.10. Test

4. Reference

Stauffer, M. (2019). Laravel: Up & Running: A Framework for Building Modern PHP Apps. United States: O’Reilly Media.
https://laravel.com/docs/8.x/queries
https://laravel.com/docs/8.x/eloquent

laravel db sql query builder eloquent

Ben's IT Diary