MySQL Database: Query Builder

- Chunking Results
- Aggregates
- Where Clauses
- Advanced where clauses
- Ordering, Grouping, Limit, Offset

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries.
- It uses PDO parameter binding to protect your application against SQL injection. There is no need to clean strings passed as bindings.

First, add the DB facade in your controller:

use Illuminate\Support\Facades\DB;

Then, you may use the DB::table() method to begin a query.

Retrieving Results

You can apply and chain more methods to DB::table('table_name') to obtain the results you want.

Get All Rows from a Table

To get all the rows from a table, just apply the get() method
- Example: Simple UserController with an index() method that gets all the users, and returns a view.

<?php
namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller {
  //Show a list of all of the users.
  //@return view
  public function index(){
    $users = DB::table('users')->get();
    return view('user.index', ['users' => $users]);
  }
}

The get() method returns an array with objects for each row. The columns name are properties in the object.
You can use a foreach() instruction to parse the results.

foreach($users as $user){
  echo $user->name;
}

- Or, in the view blade file:

@foreach($users as $user)
  {{$user->name}}
@endforeach

- To get distinct results, apply the distinct() method:

$users = DB::table('users')->distinct()->get();

Retrieving a Single Row / Column from a Table

To get a single row from the database table, you may use the first() method. It returns a single object with columns name as properties:

$user = DB::table('users')->where('name', 'MarPlo')->first();

echo $user->name;

- If you need a single value from a record, apply the value('column_name') method. It returns directly the value of the column:

$email = DB::table('users')->where('id', '8')->value('email');

Retrieving a list of Column Values

To get a result with the values from all the rows of a single column, you may use the pluck('column_name') method:

$titles = DB::table('articles')->pluck('title');

foreach($titles as $title){
  echo '<br>'.$title;
}

- You may also specify a custom key column for the returned Collection:

$titles = DB::table('articles')->pluck('title', 'key');

foreach($titles as $key=>$title){
  echo '<br>'.$title;
}

Selecting specified columns

To select specified columns from a table, use the select() method with the columns name as arguments (you can even add an Alias, with "as"):

$users = DB::table('users')->select('name', 'emails as mail')->get();

If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect() method:

$query = DB::table('users')->select('name', 'emails as mail');

$users = $query->addSelect('age')->get();

Raw SQL Expressions

To create a raw SQL expression, you may use the DB::raw() method. These expressions will be injected into the query as strings (they are not automatically filtred against SQL injection).

$users = DB::table('users')->select(DB::raw('count(*) as nru, status'))
->where('status', '<>', 1)->groupBy('status')->get();

Chunking Results

The chunk() method is useful when you need to work with thousands of database records.
This method retrieves a small chunk of the results at a time and feeds each chunk into a Callback function for processing.
- Here is an example that selects the entire users table in chunks of 100 records at a time:

DB::table('users')->orderBy('id')->chunk(100, function($users){
  foreach($users as $user){
    //
  }
});

To stop further chunks from being processed, apply return false; in the Callback function:

DB::table('users')->orderBy('id')->chunk(100, function($users){
  //...
  return false;
});

Aggregates

The Laravel query builder provides a variety of aggregate methods: max(), min(), avg(), and sum(). You may call any of these methods after constructing your query:

$nr_users = DB::table('users')->count();

$price = DB::table('orders')->min('price');

- You may combine these methods with other clauses:

$price = DB::table('orders')->where('finalized', 1)->avg('price');

Where Clauses

To add WHERE clauses to the query, you may use the where() method .
The basic call to where() requires three arguments:
- The name of the column.
- An operator.
- The third argument is the value to evaluate against the column.

If you simply want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where() method.

For example, here is a query that selects the rows where the value of the "votes" column is equal to 10:

$users = DB::table('users')->where('votes', '=', 10)->get();
//or:
$users = DB::table('users')->where('votes', 10)->get();

- You may use a variety of other operators when in the WHERE clause:

$users = DB::table('users')->where('votes', '>=', 10)->get();

$users = DB::table('users')->where('votes', '<>', 12)->get();

$users = DB::table('users')->where('name', 'like', 'Ma%')->get();

You may pass an array of conditions to the where() function (they will be joined with the AND operator):

$users = DB::table('users')->where([
['status', '=', '1'], ['pages', '<>', '1']
])->get();

OR Statements

You may chain where() function together with the orWhere(), which is used for OR statements. The orWhere() method accepts the same arguments as the where():

$users = DB::table('users')->where('votes', '>', 8)
->orWhere('name', 'MarPlo')->get();

Additional Where Clauses

whereBetween() - verifies that a column's value is between two values.

$users = DB::table('users')->whereBetween('votes', [1, 10])->get();

whereNotBetween() - verifies that a column's value lies outside of two values.
```
$users = DB::table('users')->whereNotBetween('votes', [1, 6])->get();
```
whereIn() - verifies that a given column's value is contained within the given array.
```
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
```
whereNotIn() - verifies that the given column's value is not contained in the given array.
```
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();
```
whereNull() - check that the value of the given column is NULL.
```
$users = DB::table('users')->whereNull('address')->get();
```
whereNotNull() - check that the value of the given column is not NULL.
```
$users = DB::table('users')->whereNotNull('address')->get();
```

whereDate() - to compare a column's value against a date.

$users = DB::table('users')->whereDate('created_at', '2017-10-15')->get();

whereYear() - to compare a column's value against a year.

$users = DB::table('users')->whereYear('created_at', '2017')->get();

whereMonth() - to compare a column's value against a specific month.

$users = DB::table('users')->whereMonth('created_at', '10')->get();

whereDay() - to compare a column's value against a specific day of a month.
```
$users = DB::table('users')->whereDay('created_at', '15')->get();
```

whereColumn() - to verify a conditions between two columns.

$users = DB::table('users')->whereColumn('col_1', '=', 'col_2')->get();

$users = DB::table('users')->whereColumn('col_1', '>', 'col_2')->get();

- You may also pass an array of multiple conditions. These conditions will be joined using the AND operator:

$users = DB::table('users')->whereColumn([
 ['col_1', '=', 'col_2'],
 ['col_2', '>', 'col_3']
])->get();

Advanced where clauses

The Laravel query builder can handle nested parameter groupings or "where exists" clauses.

Parameter Grouping

Here is an example of grouping multiple where parameters:

DB::table('users')->where('name', '=', 'MarPlo')->orWhere(function($query){
  $query->where('votes', '>', 10)->where('title', '<>', 'Admin');
})->get();

The function passed into the orWhere() method receives a query builder instance which you can use to set the conditions that should be contained within the parenthesis group.
- The example above will produce the following SQL:

select * from users where name='MarPlo' or (votes >10 and title <> 'Admin')

Where Exists Clauses

The whereExists() method allows you to write where exists SQL clauses. The whereExists() method accepts a function as argument, which will receive a query builder instance allowing you to define the query that should be placed inside of the SQL "exists" clause:

DB::table('users')->whereExists(function($query){
  $query->select(DB::raw(1))->from('orders')
   ->whereRaw('orders.user_id = users.id');
})->get();

The query above will produce the following SQL:

select * from users where exists (
select 1 from orders where orders.user_id = users.id
)

JSON Where Clauses

Laravel supports querying JSON column types on databases that provide support for JSON column types: MySQL 5.7+ and Postgres.
To query the items of a JSON column, use the "->" operator:

$users = DB::table('users')->where('preferences->meal', 'salad')->get();

Ordering, Grouping, Limit, and Offset

orderBy() - allows you to sort the result of the query by a given column.
The first argument should be the column you wish to sort by, the second argument may be either asc or desc.
```
$users = DB::table('users')->orderBy('name', 'desc')->get();
```
latest() / oldest() - allow you to easily order results by date.
```
$user = DB::table('users')->latest('date_col')->first();
```
inRandomOrder() - may be used to sort the query results randomly.
```
$randomUser = DB::table('users')->inRandomOrder()->first();
```

groupBy() / having() - may be used to group the query results.

$users = DB::table('users')->groupBy('col')->having('col', '>', 10)->get();

havingRaw() - to set a raw SQL string in the having clause.

$users = DB::table('orders')->select('product', DB::raw('SUM(price) as sales'))
 ->groupBy('product')->havingRaw('SUM(price) > 25')->get();

skip() / take() - to limit the number of results returned from the query, or to skip a given number of results in the query.
```
$users = DB::table('users')->skip(10)->take(5)->get();
```
- Alternatively, you may use the limit() and offset() methods.
```
$users = DB::table('users')->offset(10)->limit(5)->get();
```

- Documentation: Laravel - Database: Query Builder

<<-Simple Laravel MySQL CRUD .. -- MySQL Query Builder: Insert, ..->>