MySQL Database: Query Builder

Laravel Course

- 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: 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:

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:

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"): 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).

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:

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.
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():

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:

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:

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

<dl>
 <dt>HTML</dt>
  <dd> - Hyper Text Markup Language</dd>
  <dd> - Language for web pages</dd>
</dl>

#id {
  visibility: hidden;
}

document.getElementById("id").onclick = function(){
  alert("http://CoursesWeb.net/");
}

if(isset($_REQUEST["id"])) {
  echo $_REQUEST["id"];
}