Query
Yuga's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform all database operations in your application.
Retrieving results from the database
Retrieving All Rows From A Table
You can use the table
method on the DB
class to begin a query. The table
method returns a \Yuga\Database\Query\DB
query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get
or all
methods:
<?php
namespace App\Controllers;
class UserController extends BaseController
{
/**
* Show a list of all of the users in the database.
*
* @return Response
*/
public function index()
{
$users = \DB::table('users')->get();
return view('user.index', ['users' => $users]);
}
}
The get / all
methods return a Yuga\Database\Elegant\Collection
containing the results where each result is an instance of the PHP stdClass
object. You may access each column's value by accessing the column as a property of the object:
foreach ($users as $user) {
echo $user->name;
}
Retrieving A Single Row / Column From A Table
If you just need to retrieve a single row from the database table, you can use the first / last
methods. These methods will return a single stdClass
object:
// Retrieve the first record from the collection
$user = DB::table('users')->where('username', 'hamnaj')->first();
// Retrive the last record from the collection
$user = DB::table('users')->where('username', 'hamnaj')->last();
echo $user->name;
Aggregates
The query builder also provides a variety of aggregate methods such as count
, max
, min
, avg
, and sum
. You can call any of these methods after constructing your query:
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
You can also combine these methods with other clauses:
$price = DB::table('orders')
->where('finalized', 1)
->average('price');
Simple query:
Get a user with the id of 3
. Note that null
is returned when no match is found.
// This will return a php stdClass object
$users = DB::table('users')->find(3);
Full queries: Get all users with blue or red hair.
$users = DB::table('users')
->where('hair_color', '=', 'blue'))
->orWhere('hair_color', '=', 'red')
->get();
Select
We recommend that you use table()
method before every query
, except raw query()
.
To select from more that one table, pass an array of your tables instead of a plain string.
But this is not a requirement as you can also pass in the different tables as below.
Method 1 (array)
$results = DB::table(['users', 'posts'])
->where('users.post_id', 'posts.id')
->take(10)
->get();
Method 2 (tables as arguments)
$results = DB::table('users', 'posts')
->where('users.post_id', 'posts.id')
->take(10)
->get();
Table alias
You can easily set the table alias as below:
$query = DB::table(['users' => 'u'])
->join('posts', 'posts.user_id', '=', 'u.id');
You can change the alias anytime by using:
$query->alias('uu', 'users'); // uu for users
// or
$query->table('users')->alias('uu');
Output:
SELECT * FROM `users` AS `uu` INNER JOIN `posts` ON `posts`.`user_id` = `uu`.`id`
Multiple selects
$query = DB::select([ 'mytable.myfield1', 'mytable.myfield2', 'another_table.myfield3' ]);
Using select method multiple times select('a')->select('b')
will also select `a` and b
. This can be useful if you want to do conditional selects (within a PHP if
).
Select distinct
$query = DB::selectDistinct(['mytable.myfield1', 'mytable.myfield2']);
Select from query
Items from another query can easily be selected as below:
$subQuery = DB::table('countries');
$query = DB::table(DB::subQuery($subQuery))->where('id', 2);
Output:
SELECT * FROM (SELECT * FROM `countries`) WHERE `id` = 2
Select single field
This can be done as below:
$query = DB::table('users')->select('*');
// or
$query = DB::table('users')->select('username', 'email');
// or
$query = DB::table('users')->select(['username', 'email', 'fullname']);
Select with sub-queries
// first sub-query
$firstSubQuery = DB::table('mails')
->select(DB::raw('COUNT(*)'));
// send sub-query
$secondSubQuery = DB::table('events')->select(DB::raw('COUNT(*)'));
// Execute the query
$count = DB::select(
DB::subQuery($firstSubQuery, 'column1'),
DB::subQuery($secondSubQuery, 'column2')
)->first();
Result:
SELECT
(SELECT COUNT(*) FROM `mails`) AS column1,
(SELECT COUNT(*) FROM `events`) AS column2
LIMIT 1
You can also easily create a sub-query within the where
clause as below:
$query = DB::table('posts')
->where(DB::subQuery($subQuery), '<>', 'value');
Last updated
Was this helpful?