StellarWP DB

A WPDB wrapper and query builder library. Authored by the development team at StellarWP and provided free for the WordPress community.

Inspired and largely forked from the GiveWP codebase!

Installation

It's recommended that you install DB as a project dependency via Composer:

composer require stellarwp/db

We actually recommend that this library gets included in your project using Strauss.

Luckily, adding Strauss to your composer.json is only slightly more complicated than adding a typical dependency, so checkout our strauss docs.

Table of contents

• Quick start

• Configuration

• DB

• Select statements

• From Clause

• Joins

  • LEFT Join
  • RIGHT Join
  • INNER Join
  • Join Raw
  • Advanced Join Clauses

• Unions

• Where Clauses

  • Where
  • Where IN
  • Where BETWEEN
  • Where LIKE
  • Where IS NULL
  • Where EXISTS
  • Subquery Where Clauses
  • Nested Where Clauses

• Ordering, Grouping, Limit & Offset

  • Ordering
  • Grouping
  • Limit & Offset

• Special methods for working with meta tables

  • attachMeta
  • configureMetaTable

• CRUD

  • Insert
  • Update
  • Upsert
  • Delete
  • Get

• Inherited from $wpdb

  • get_var()
  • get_col()
  • esc_like()
  • remove_placeholder_escape()

• Aggregate Functions

  • Count
  • Sum
  • Avg
  • Min
  • Max

Quick start

Getting up and running with this library is easy. You'll want to initialize the DB class. Doing so during the plugins_loaded action is a reasonable location, though you can do it anywhere that feels appropriate.

For this example and all future ones, let's assume you have included this library with Strauss and your project's namespace is Boom\Shakalaka.

use Boom\Shakalaka\StellarWP\DB\DB;

add_action( 'plugins_loaded', function() {
	DB::init();
}, 0 );

The two main classes that make up the core of this library are the DB class and the QueryBuilder class. Here are their namespaces:

# For DB, it is "StellarWP\DB\DB", but with your namespace prefix it'll be:
use Boom\Shakalaka\StellarWP\DB\DB;

# For QueryBuilder, it is "StellarWP\DB\QueryBuilder\QueryBuilder", but with your namespace prefix it'll be:
use Boom\Shakalaka\StellarWP\DB\QueryBuilder\QueryBuilder;

Configuration

This library provides default hooks and exceptions, however, if you have additional needs for your own application, you can override one or both via the StellarWP\DB\Config class:

use Boom\Shakalaka\StellarWP\DB\Config;

// Ensure hooks are prefixed with your project's prefix.
Config::setHookPrefix( 'boom_shakalaka' );

// Use your own exception class rather than the default Database\Exceptions\DatabaseQueryException class.
Config::setDatabaseQueryException( 'MyCustomException' );

// Fetch the hook prefix.
$prefix = Config::getHookPrefix();

// Fetch the database query exception class.
$class = Config::getDatabaseQueryException();

DB

DB class is a static decorator for the $wpdb class, but it has a few methods that are exceptions to that. Methods DB::table() and DB::raw().

DB::table() is a static facade for the QueryBuilder class, and it accepts two string arguments, $tableName and $tableAlias.

Under the hood, DB::table() will create a new QueryBuilder instance, and it will use QueryBuilder::from method to set the table name. Calling QueryBuilder::from when using DB::table method will return an unexpected result. Basically, we are telling the QueryBuilder that we want to select data from two tables.

Important

When using DB::table(tableName) method, the tableName is prefixed with $wpdb->prefix. To bypass that, you can use DB::raw method which will tell QueryBuilder not to prefix the table name.

DB::table(DB::raw('posts'));

Select statements

Available methods - select / selectRaw / distinct

By using the QueryBuilder::select method, you can specify a custom SELECT statement for the query.

DB::table('posts')->select('ID', 'post_title', 'post_date');

Generated SQL

SELECT ID, post_title, post_date FROM wp_posts

You can also specify the column alias by providing an array [column, alias] to the QueryBuilder::select method.

DB::table('posts')->select(
    ['ID', 'post_id'],
    ['post_status', 'status'],
    ['post_date', 'createdAt']
);

Generated SQL:

SELECT ID AS post_id, post_status AS status, post_date AS createdAt FROM wp_posts

The distinct method allows you to force the query to return distinct results:

DB::table('posts')->select('post_status')->distinct();

You can also specify a custom SELECT statement with QueryBuilder::selectRaw method. This method accepts an optional array of bindings as its second argument.

DB::table('posts')
    ->select('ID')
    ->selectRaw('(SELECT ID from wp_posts WHERE post_status = %s) AS subscriptionId', 'give_subscription');

Generated SQL

SELECT ID, (SELECT ID from wp_posts WHERE post_status = 'give_subscription') AS subscriptionId FROM wp_posts

By default, all columns will be selected from a database table.

DB::table('posts');

Generated SQL

SELECT * FROM wp_posts

From clause

By using the QueryBuilder::from() method, you can specify a custom FROM clause for the query.

$builder = new QueryBuilder();
$builder->from('posts');

Set multiple FROM clauses

$builder = new QueryBuilder();
$builder->from('posts');
$builder->from('postmeta');

Generated SQL

SELECT * FROM wp_posts, wp_postmeta

Important

Table name is prefixed with $wpdb->prefix. To bypass that, you can use DB::raw method which will tell QueryBuilder not to prefix the table name.

$builder = new QueryBuilder();
$builder->from(DB::raw('posts'));

Joins

The Query Builder may also be used to add JOIN clauses to your queries.

Available methods - leftJoin / rightJoin / innerJoin / joinRaw / join

LEFT Join

LEFT JOIN clause.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->leftJoin('give_donationmeta', 'donationsTable.ID', 'metaTable.donation_id', 'metaTable');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable LEFT JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

RIGHT Join

RIGHT JOIN clause.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->rightJoin('give_donationmeta', 'donationsTable.ID', 'metaTable.donation_id', 'metaTable');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable RIGHT JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

INNER Join

INNER JOIN clause.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->innerJoin('give_donationmeta', 'donationsTable.ID', 'metaTable.donation_id', 'metaTable');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable INNER JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

Join Raw

Insert a raw expression into query.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->joinRaw('LEFT JOIN give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable LEFT JOIN give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

Advanced Join Clauses

The closure will receive a Give\Framework\QueryBuilder\JoinQueryBuilder instance

DB::table('posts')
    ->select('donationsTable.*', 'metaTable.*')
    ->join(function (JoinQueryBuilder $builder) {
        $builder
            ->leftJoin('give_donationmeta', 'metaTable')
            ->on('donationsTable.ID', 'metaTable.donation_id')
            ->andOn('metaTable.meta_key', 'some_key', $qoute = true);
    });

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts LEFT JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id AND metaTable.meta_key = 'some_key'

Unions

The Query Builder also provides a convenient method to "union" two or more queries together.

Available methods - union / unionAll

Union

$donations = DB::table('give_donations')->where('author_id', 10);

DB::table('give_subscriptions')