Andrew Dorokhov
To get a query builder object we should use the table() method of the DB facade.
use Illuminate\Support\Facades\DB;
$countries = DB::table('country')->get();
$countries = DB::table('country')->limit(5)->get();
$countries = DB::table('country')->select('title', 'content')->limit(5)->get();
$countries = DB::table('country')->first();
$countries = DB::table('country')->orderBy('title', 'asc')->get();
Get a row by ID:
$post = DB::table('post')->select('id', 'name')->find(3);
// Or:
$post = DB::table('post')->select('id', 'name')->where('id', 3)->get();
$post = DB::table('post')->select('id', 'name')->where('id', '=', 3)->get();
where() method:
$post = DB::table('post')->select('id', 'name')->where([
['id', '>', 1],
['id', '<', 5],
])->get();
value() method:
$title = DB::table('post')->where('id', 3)->value('title');
We can get a column of rows:
// We will get an array:
$data = DB::table('post')->limit(10)->pluck('title');
// We will get an array with keys (id):
$data = DB::table('post')->limit(10)->pluck('title', 'id');
Number of rows:
$data = DB::table('post')->count();
Max and min values:
$data = DB::table('product')->max('price');
$data = DB::table('product')->min('price');
Sum:
$data = DB::table('product')->sum('price');
Average:
$data = DB::table('product')->avg('price');
Get only unique values:
$data = DB::table('product')->select('title')->distinct()->get();
Joins
$data = DB::table('city')->select('city.id', 'city.name', 'country.code', 'country.name')
->join('country', 'city.code', '=', 'country.code')->get();