Query Builder

Overview

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.

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');