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

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



- Documentation: Laravel - Database: Query Builder

Daily Test with Code Example

HTML
CSS
JavaScript
PHP-MySQL
Which tag is used to add lists into <ul> and <ol> elements?
<dt> <dd> <li>
<ul>
 <li>http://coursesweb.net/html/</li>
 <li>http://coursesweb.net/css/</li>
</ul>
Which value of the "display" property creates a block box for the content and ads a bullet marker?
block list-item inline-block
.some_class {
  display: list-item;
}
Which instruction converts a JavaScript object into a JSON string.
JSON.parse() JSON.stringify eval()
var obj = {
 "courses": ["php", "javascript", "ajax"]
};
var jsonstr = JSON.stringify(obj);
alert(jsonstr);    // {"courses":["php","javascript","ajax"]}
Indicate the PHP class used to work with HTML and XML content in PHP.
stdClass PDO DOMDocument
$strhtml = '<body><div id="dv1">CoursesWeb.net</div></body>';
$dochtml = new DOMDocument();
$dochtml->loadHTML($strhtml);
$elm = $dochtml->getElementById("dv1");
echo $elm->nodeValue;    // CoursesWeb.net
MySQL Database: Query Builder

Last accessed pages

  1. PHP getElementById and getElementsByTagName (49471)
  2. Mysql SELECT JOIN tables on two different Databases (4498)
  3. jQuery UI draggable - Drag elements (11448)
  4. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (142520)
  5. Using the Bone Tool (4253)

Popular pages this month

  1. Courses Web: PHP-MySQL JavaScript Node.js Ajax HTML CSS (523)
  2. The Mastery of Love (65)
  3. CSS cursor property - Custom Cursors (62)
  4. Read Excel file data in PHP - PhpExcelReader (59)
  5. PHP-MySQL free course, online tutorials PHP MySQL code (44)