Query Builder
WP Bones’s database query builder provides a convenient, fluent interface for creating and running database queries. It can be used to perform most database operations in your WordPress instance.
This embedded version of Query Builder is a light version of the original. It does not include the advanced features of the original.
Retrieving All Rows From A Table
You can use the table
method provided by the DB
class to start a query. The table
method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get
method:
<?php
namespace WPKirk\Http\Controllers;
use WPKirk\WPBones\Database\DB;
class UserController extends Controller
{
/**
* Get the list of all WordPress users.
*
*/
public function index()
{
$users = DB::table('users')->get();
}
}
The get
method returns an WPKirk\WPBones\Database\Support\Collection
instance containing the results of the query 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:
use WPKirk\WPBones\Database\DB;
$users = DB::table('users')->get();
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 a database table, you may use the DB
class’s first
method. This method will return a single stdClass
object:
use WPKirk\WPBones\Database\DB;
$user = DB::table('users')->where('user_login', 'admin')->first();
return $user->email;
To retrieve a single row by its id
column value, use the find
method:
use WPKirk\WPBones\Database\DB;
$user = DB::table('users')->find(3);
Retrieving a List of Column Values
If you would like to retrieve a WPKirk\WPBones\Database\Support\Collection
instance containing the values of a single column, you may use the pluck
method. In this example, we will retrieve a collection of usernames:
use WPKirk\WPBones\Database\DB;
$names = DB::table('users')->pluck('user_login');
foreach ($names as $name) {
echo $name;
}
Aggregates
The query builder also provides a variety of methods for retrieving aggregate values like count
(max, min, avg, and sum in progress yet). You may call any of these methods after constructing your query:
use WPKirk\WPBones\Database\DB;
$users = DB::table('users')->count();
Select Statements
Specifying a Select Clause
You may not always want to select all columns from a database table. Using the select
method, you can specify a custom “select” clause for the query:
use WPKirk\WPBones\Database\DB;
$users = DB::table('users')
->select('user_login', 'user_email as email')
->get();
Basic Where Clauses
Where Clauses
You can utilize the query builder’s where
method to add “where” clauses to the query. The most basic call to the where
method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database’s supported operators. The third argument is the value to compare against the column’s value.
For instance, the subsequent query retrieves users where the value of the user_login
column is equal to admin
and the value of the user_status
column is greater than 0
:
$users = DB::table('users')
->where('user_login', '=', 'admin')
->where('user_status', '>', 0)
->get();
For convenience, if you want to verify that a column is equal to a given value, you may pass the value as the second argument to the where
method. WP Bones will assume you would like to use the equal operator:
$users = DB::table('users')->where('user_login', 'admin')->get();
As previously mentioned, you may use any operator that is supported by your database system:
$users = DB::table('users')
->where('user_status', '>=', 100)
->get();
$users = DB::table('users')
->where('user_status', '<>', 100)
->get();
$users = DB::table('users')
->where('display_name', 'like', 'T%')
->get();
You may also pass an array of conditions to the where
function. Each element of the array should be an array containing the three arguments typically passed to the where
method:
$users = DB::table('users')->where([
['user_status', '=', '0'],
['display_name', 'like', 'r%'],
])->get();
Or Where Clauses
When chaining together calls to the query builder’s where
method, the “where” clauses will be joined together using the and
operator. However, you may use the orWhere
method to join a clause to the query using the or operator. The orWhere
method accepts the same arguments as the where
method:
$users = DB::table('users')
->where('user_status', '>', 0)
->orWhere('user_login', 'root')
->get();
Additional Where Clauses
whereBetween
/ orWhereBetween
The whereBetween
method verifies that a column’s value is between two values:
$users = DB::table('users')
->whereBetween('user_status', [1, 100])
->get();
whereNotBetween
/ orWhereNotBetween
The whereNotBetween
method verifies that a column’s value lies outside of two values:
$users = DB::table('users')
->whereNotBetween('id', [1, 100])
->get();
whereIn
/ whereNotIn
/ orWhereIn
/ orWhereNotIn
The whereIn
method verifies that a given column’s value is contained within the given array:
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
Ordering, Limit & Offset
Ordering
The orderBy
Method
The orderBy
method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy
method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc or desc:
$users = DB::table('users')
->orderBy('display_name', 'desc')
->get();
To sort by multiple columns, you may simply invoke orderBy
as many times as necessary:
$users = DB::table('users')
->orderBy('display_name', 'desc')
->orderBy('user_email', 'asc')
->get();
Limit & Offset
You may use the limit
and offset
methods 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')
->offset(10)
->limit(5)
->get();
Insert Statements
The query builder also provides an insert
method that may be used to insert records into the database table. The insert
method accepts an array of column names and values:
DB::table('users')->insert([
'user_email' => 'kayla@example.com',
'user_login' => 'kayla',
]);
You may insert several records at once by passing an array of arrays. Each array represents a record that should be inserted into the table:
DB::table('users')->insert([
['user_email' => 'picard@example.com', 'user_login' => 'picard'],
['user_email' => 'janeway@example.com', 'user_login' => 'janeway'],
]);
Update Statements
In addition to inserting records into the database, the query builder can also update existing records using the update
method. The update
method, like the insert method, accepts an array of column and value pairs indicating the columns to be updated. You may constrain the update
query using where
clauses:
$affected = DB::table('users')
->where('id', 1)
->update(['user_email' => 'janeway@example.com']);
You may also use:
$user = DB::table('users')->find(1);
$user->user_email = 'janeway@example.com';
$user->save();
Delete Statements
The query builder’s delete
method may be used to delete records from the table. You may constrain delete
statements by adding “where” clauses before calling the delete
method:
DB::table('users')->delete();
DB::table('users')->where('id', '>', 100)->delete();
If you wish to truncate an entire table, which will remove all records from the table and reset the auto-incrementing ID to zero, you may use the truncate
method:
DB::table('users')->truncate();
Debugging
You may use the dump
methods while building a query to dump the current query bindings and SQL. The dump
method will display the records returned by the query in a JSON pretty-printed format:
echo DB::table('users')->get()->dump();
[
{
"ID": "1",
"user_login": "root",
"user_pass": "$P$BcsZ.SCXT1ItPV5vE.sRTwBpqx.vHs0",
"user_nicename": "root",
"user_email": "wpbones.info@gmail.com",
"user_url": "",
"user_registered": "2019-05-22 09:50:02",
"user_activation_key": "",
"user_status": "0",
"display_name": "root"
}
]
Tables without the WordPress prefix
Starting from version 1.7.0
, you can use tables without the WordPress prefix.
If you are using tables without the WordPress prefix, you can set the table name in the DB
class:
DB::table('my_table', 'id', false);
The third parameter is a boolean that indicates whether the table has the WordPress prefix or not.
You may use the short syntax to set the table name:
DB::tableWithoutPrefix('my_table');