Skip to content

Home

Jump to bottom
Glynn Quelch edited this page Feb 6, 2022 · 31 revisions

Pixie WPDB A port of the Pixie Query Builder for WordPress/WPDB
GitHub issues codecov Scrutinizer Code Quality GitHub issues Open Source Love

An expressive, query builder for WordPRess it can also be referred as a Database Abstraction Layer. Pixie WPDB supports WPDB ONLY and it takes care of query sanitization, table prefixing and many other things with a unified API.

Pixie WPDB is an adaption of pixie originally written by usmanhalalit. Pixie is no longer under active development.

Features

$thing = QB::table('someTable')->where('something','=', 'something else')->first();

Install

Perquisites

  • WordPress 5.7+ (tested upto 5.9)
  • PHP 7.1+ (includes support for PHP8)
  • MySql 5.7+ or MariaDB 10.2+
  • Composer (optional)

Using Composer

The easiest way to include Pixie in your project is to use composer.

composer require gin0115/pixie-wpdb

Static Loader

If you are planning to just inlcude Pixie direct in your plugin, you can extract the src directory and add this to your functions.php or similar.

require_once '/path/to/src/loader.php';

Each class is checked if already loaded, to avoid conflicts if used on multiple plugins.

Setup Connection

If you are only planning on having a single connection, you will only need to configure the connection once.

# Basic setup

// Access the global WPDB or a custom instance for additional tables.
global $wpdb;

// Configure the builder and/or internal WPDB instance
$connection_config = [Connection::PREFIX => 'gin0115_'];

// Give a *single* Alias
$builder_alias = 'Gin0115\\DB';

new Connection( $wpdb, $connection_config, $builder_alias );

This would then give access to an instance of the QueryBuilder using this connection, via the alias defined Gin0115\DB

$foos = Gin0115\DB::table('foo')->where('column', 'red')->get();

Generated & executed query :: "SELECT * FROM gin0115_foo WHERE column = 'red'; "

Connection Config

It is possible to configure the connection used by your instance of the query builder.

Values

Key Constant Value Description
prefix Connection:: PREFIX STRING Custom table prefix (will ignore WPDB prefix)
use_wpdb_prefix Connection:: USE_WPDB_PREFIX BOOL If true will use WPDB prefix and ignore custom prefix
clone_wpdb Connection:: CLONE_WPDB BOOL If true, will clone WPDB to not use reference to the instance (usually the $GLOBAL)
show_errors Connection:: SHOW_ERRORS BOOL If set to true will configure WPDB to show/hide errors 
$config = [
    Connection::PREFIX          => 'acme_',
    Connection::USE_WPDB_PREFIX => true,
    Connection::CLONE_WPDB      => true,
    Connection::SHOW_ERRORS     => false,
];

It is advised to use the class constants over string keys, to avoid BC breakages later on

PREFIX

@type string

This allows for the setting of a table prefix. This is then automatically prepended to the table name, in either a table definition or whenever a colum is referenced with its table table.column

  • DB::table('foo') would be acme_foo
  • select('foo.id') would be acme_foo.id

USE_WPDB_PREFIX

@type Bool

If this is defined and set to true, the table prefix will be set to match that of the passed WPDB instance. Any custom prefix added will be over ruled using this option.

CLONE_WPDB

@type Bool

If this is defined and set to true, the instance of WPDB passed, will be cloned. This allows you to make changes such as show errors, without having other plugins change the GLOBAL WPDB or your changes effecting them.

SHOW_ERRORS

@type Bool

If you are planning on using Transactions , it is best to ensure errors are enabled. This allows for auto ROLLBACK or COMMIT, through catching Exceptions. If you need to define this either way, its best to also CLONE_WPDB to avoid side effects.

Connection Alias

When you create a connection:

new Connection($wpdb, $config, 'MyAlias');

MyAlias is the name for the class alias you want to use (like MyAlias::table(...) ), you can use whatever name (with Namespace also, MyNamespace\\MyClass ) you like or you may skip it if you don't need an alias. Alias gives you the ability to easily access the QueryBuilder class across your application.

Usage

Once a connection is created, the builder can be accessed either directly using the Alias Facade or by creating an instance.

Static Usage

The easiest way to use Pixie is to use the alias facade provided. This allows you to access a builder instance anywhere, much like WPDB.

// Create the connection early on.
$connection = new Connection($wpdb, $config, 'Alias');

// Insert some data to bar.
Alias::table('bar')->insert(['column'=>'value']);

None Static Usage

When not using an alias you can instantiate the QueryBuilder handler separately, helpful for Dependency Injection and Testing.

// Create connection and builder instance.
$connection = new Connection($wpdb, $config);
$qb = new QueryBuilderHandler($connection);

$query = $qb->table('my_table')->where('name', '=', 'Sana');
$results = $query->get();

$connection here is optional, if not given it will always associate itself to the first connection, but it can be useful when you have multiple database connections.

Credits

This package began as a fork of Pixie originally written by usmanhalalit A few features have been inspired by the Pecee-pixie fork and continuation, especially the extended aggregate methods.

Clone this wiki locally