Queryable

A fluent SQL query builder for WordPress. Built on $wpdb, with meta table support, relations, migrations, and a typed model layer

Installation

composer require alaca/queryable

Two ways to use

1. Query Builder (DB Facade)

Use DB::table() for direct queries

useQueryable\DB;
// Simple queries$posts = DB::table('posts')->select('ID', 'post_title')->where('post_status', 'publish')->getAll();
$user = DB::table('users')->find('ID', 1);
// If you want to work with meta and relations using DB class, you will have to configure the schema in DB::init()DB::init([
'schema' => [
'posts' => [
'meta' => [
'table' => 'postmeta', // table name is auto prefixed'foreignKey' => 'post_id',
'primaryKey' => 'ID',
'aliases' => [
'price' => '_product_price',
'color' => '_product_color',
],
],
'relations' => [
'comments' => [
'table' => 'comments',
'foreignKey' => 'comment_post_ID',
'primaryKey' => 'ID',
'type' => 'hasMany',
],
],
],
],
]);
$products = DB::table('posts')
->select('ID', 'post_title')
->withMeta('price', 'color')
->where('post_type', 'product')
->orderBy('price', 'DESC')
->getAll();

2. Model (Recommended)

Define a model class per table with typed public properties

useQueryable\Model;
useQueryable\Schema\Table;
class Campaign extends Model
{
protectedstring$table = 'campaigns';
protectedstring$version = '1.0.0';
publicint$id;
publicstring$name;
publicstring$slug;
publicfloat$price;
publicint$stock;
public ?string$created_at = null;
protectedfunctionmeta(): array
{
return [
'table' => 'campaign_meta',
'foreignKey' => 'campaign_id',
'primaryKey' => 'id',
'aliases' => [
'budget' => '_campaign_budget',
'channel' => '_campaign_channel',
'tags' => [
'key' => '_campaign_tags', 'multiple' => true
],
],
];
}
protectedfunctionrelations(): array
{
return [
'entries' => [
'table' => 'campaign_entries',
'foreignKey' => 'campaign_id',
'primaryKey' => 'id',
'type' => 'hasMany',
],
];
}
}
// Schema is defined separately
Campaign::schema(function (Table$table) {
$table->id();
$table->string('name');
$table->string('slug', 100)->unique();
$table->decimal('price', 8, 2)->default(0);
$table->integer('stock')->default(0);
$table->datetime('created_at')->nullable();
});
// Query results are Campaign instances$campaign = Campaign::query()->find('slug', 'summer');
$campaigninstanceof Campaign; // true$campaign->name;
$campaign->price; // float, auto-cast from DB - should be unsigned int in real app! 
Campaign::query()->select('id', 'name')->withMeta('budget')->where('status', 'active')->getAll();
Campaign::query()->insert(['name' => 'Summer Sale', 'slug' => 'summer-sale']);
Campaign::query()->where('id', 1950)->update(['name' => 'Updated']);
Campaign::query()->where('id', 1950)->delete();

Base Query

Apply default conditions to every query a model makes. Useful for models that represent a subset of a shared table

class Product extends Model
{
protectedstring$table = 'posts';
protectedstring$primaryKey = 'ID';
publicint$ID;
publicstring$post_title;
publicstring$post_status;
protectedstaticfunctionbaseQuery(ModelQueryBuilder$builder): ModelQueryBuilder
{
return$builder->where('post_type', 'product');
}
}
// Every query automatically includes WHERE post_type = 'product'
Product::query()->where('post_status', 'publish')->getAll();
Product::query()->find('ID', 1911);
Product::query()->count('ID');

Scopes

Define reusable query filters as static methods on your model

class Campaign extends Model
{
protectedstring$table = 'campaigns';
publicstaticfunctionactive(): ModelQueryBuilder
{
returnstatic::query()->where('status', 'active');
}
publicstaticfunctionp2p(): ModelQueryBuilder
{
returnstatic::query()->where('campaign_type', 'p2p');
}
}
Campaign::active()->getAll();
Campaign::active()->orderBy('name')->limit(10)->getAll();
Campaign::p2p()->getAll();

Save

Create or update a model instance

// Create new$campaign = Campaign::make();
$campaign->name = 'Summer';
$campaign->slug = 'summer';
$campaign->price = 29.99;
$campaign->save();
// Update existing$campaign = Campaign::query()->find('id', 1950);
$campaign->name = 'Updated';
$campaign->save();

The primary key defaults to id. Override $primaryKey in your model if needed:

class Post extends Model
{
protectedstring$table = 'posts';
protectedstring$primaryKey = 'ID';
}

Lifecycle Hooks

Override onBeforeSave() and onSave() to run logic before and after save()

class Campaign extends Model
{
protectedstring$table = 'campaigns';
protectedfunctiononBeforeSave(): void
{
if (empty($this->slug)) {
$this->slug = sanitize_title($this->name);
}
}
protectedfunctiononSave(): void
{
do_action('campaign_saved', $this);
}
}

onBeforeSave() runs before the database write. Any property changes made there are included in the save

onSave() runs after the database write

Insert

// Single row
Campaign::query()->insert(['name' => 'Summer', 'slug' => 'summer']);
// Returns: QueryResult { affectedRows: 1, insertId: 1 }// bulk insert
Campaign::query()->insert([
['name' => 'Summer', 'slug' => 'summer'],
['name' => 'Winter', 'slug' => 'winter'],
]);
// insert with meta (meta fields are separated automatically)
Campaign::query()->withMeta('budget', 'channel')->insert([
'name' => 'Summer',
'slug' => 'summer',
'budget' => '1950', // campaign_meta table'channel' => 'email', // campaign_meta table
]);
// upsert (insert or update on duplicate key)
Campaign::query()->upsert(
['name' => 'Summer', 'slug' => 'summer'],
['slug'], // conflict columns
['name'], // columns to update
);

Update

Campaign::query()->where('id', 1)->update(['name' => 'Updated']);
// update meta
Campaign::query()->withMeta('budget')->where('id', 1)->update([
'name' => 'Updated',
'budget' => '1911',// updates meta row
]);
// Increment / Decrement
Campaign::query()->where('id', 1)->increment('stock');
Campaign::query()->where('id', 1)->increment('stock', 5);
Campaign::query()->where('id', 1)->decrement('stock', 3);
// raw update
Campaign::query()->where('id', 1)->updateRaw("stock = stock + 1");

Delete

Campaign::query()->where('id', 1)->delete();
// meta rows are deleted automatically if the model has meta config// truncate (clears both the table and meta table)
Campaign::query()->truncate();

Select

// all columns
Campaign::query()->getAll();
// select specific columns
Campaign::query()->select('id', 'name', 'slug')->getAll();
// single row (LIMIT 1, returns null if not found)
Campaign::query()->select('id', 'name')->get();
// distinct
Campaign::query()->distinct()->select('status')->getAll();
// select with alias
Campaign::query()->select(['id' => 'campaign_id'])->getAll();
// raaw select
Campaign::query()->selectRaw('COUNT(*) as total')->get();
// Subquery in FROMDB::table(function ($qb) {
$qb->table('orders')->select('user_id')->groupBy('user_id');
}, 'order_totals')->selectRaw('user_id, total')->getAll();

Where Clauses

// basic
Campaign::query()->where('status', 'active')->getAll();
Campaign::query()->where('status', 'active')->orWhere('status', 'pending')->getAll();
// LIKE
Campaign::query()->whereLike('name', 'summer')->getAll(); // LIKE '%summer%'
Campaign::query()->whereLike('name', 'summer%')->getAll(); // LIKE 'summer%'// IN / NOT IN
Campaign::query()->whereIn('id', [1, 2, 3])->getAll();
Campaign::query()->whereNotIn('status', ['draft', 'trash'])->getAll();
// BETWEEN
Campaign::query()->whereBetween('price', 10, 100)->getAll();
// NULL
Campaign::query()->whereIsNull('deleted_at')->getAll();
Campaign::query()->whereIsNotNull('email')->getAll();
// column comparison (no value escaping), useful for JOINS
Campaign::query()->whereColumn('users.id', 'orders.user_id')->getAll();
// nested groups
Campaign::query()->where('status', 'active')
->orWhere(function ($qb) {
$qb->where('role', 'admin')->where('verified', 1);
})
->getAll();
// WHERE status = 'active' OR (role = 'admin' AND verified = 1)// Subquery
Campaign::query()->whereIn('id', function ($qb) {
$qb->table('orders')->select('campaign_id')->where('total', 100, '>');
})->getAll();
// EXISTS
Campaign::query()->whereExists(function ($qb) {
$qb->table('orders')->select('id')->whereRaw('orders.campaign_id = campaigns.id');
})->getAll();
// Raw
Campaign::query()->whereRaw('created_at > NOW() - INTERVAL 30 DAY')->getAll();

All where methods have or variants: orWhere, orWhereLike, orWhereIn, orWhereBetween, orWhereIsNull, orWhereExists, orWhereColumn.

Joins

Campaign::query()->leftJoin('entries', 'campaigns.id', 'entries.campaign_id')->getAll();
Campaign::query()->innerJoin('entries', 'campaigns.id', 'entries.campaign_id')->getAll();
Campaign::query()->rightJoin('entries', 'campaigns.id', 'entries.campaign_id')->getAll();
Campaign::query()->crossJoin('statuses')->getAll();
// with alias
Campaign::query()->leftJoin('entries', 'campaigns.id', 'e.campaign_id', 'e')->getAll();
// raw join
Campaign::query()->joinRaw('LEFT JOIN entries e ON campaigns.id = e.campaign_id')->getAll();

Ordering, Grouping, Limit & Offset

Campaign::query()->orderBy('name')->getAll();
Campaign::query()->orderBy('name', 'DESC')->getAll();
Campaign::query()->orderByRaw('RAND()')->getAll();
Campaign::query()->groupBy('status')->getAll();
Campaign::query()->groupBy('status', 'channel')->getAll();
Campaign::query()->groupByRaw('YEAR(created_at)')->getAll();
Campaign::query()->limit(10)->offset(20)->getAll();
// order and group by meta keys
Campaign::query()->withMeta('budget')->orderBy('budget', 'DESC')->getAll();
Campaign::query()->withMeta('channel')->groupBy('channel')->getAll();

Having

Campaign::query()->groupBy('status')->havingCount('id', '>', 5)->getAll();
Campaign::query()->groupBy('status')->havingSum('stock', '>', 100)->getAll();
Campaign::query()->groupBy('status')->havingAvg('price', '>', 50)->getAll();
Campaign::query()->groupBy('status')->havingMin('price', '>', 10)->getAll();
Campaign::query()->groupBy('status')->havingMax('price', '<', 1000)->getAll();
Campaign::query()->groupBy('status')->havingRaw('COUNT(id) > 5')->getAll();
// combine with OR
Campaign::query()->groupBy('status')
->havingSum('stock', '>', 100)
->orHavingAvg('price', '>', 200)
->getAll();

Union

$drafts = Campaign::query()->where('status', 'draft')->select('id', 'name');
Campaign::query()->where('status', 'active')->select('id', 'name')->union($drafts)->getAll();
// Union ALL
Campaign::query()->select('id')->unionAll($drafts)->getAll();

Aggregates

Campaign::query()->count('id'); // int
Campaign::query()->sum('price'); // float
Campaign::query()->avg('price'); // float
Campaign::query()->min('price'); // float
Campaign::query()->max('price'); // float// with conditions
Campaign::query()->where('status', 'active')->count('id');

Find & Exists

// find by column. returns model instance or null
Campaign::query()->find('id', 1950);
Campaign::query()->find('slug', 'summer');
// check if rows exist. returns bool
Campaign::query()->where('slug', 'summer')->exists();

Pluck

Returns a flat array of a single column values:

Campaign::query()->pluck('name');
// ['Save the dolphins', 'All alcoholic beverages 20% off', 'Yeah, no']

Meta Tables

Meta tables follow the WordPress pattern: a separate table with meta_key and meta_value columns linked by a foreign key

Configuration

Define meta config in your models meta() method or in DB::init():

protectedfunctionmeta(): array
{
return [
'table' => 'campaign_meta', // meta table name (auto-prefixed)'foreignKey' => 'campaign_id', // FK in meta table'primaryKey' => 'id', // PK in main table'aliases' => [ // meta keys aliases'budget' => '_campaign_budget',
'channel' => '_campaign_channel',
'tags' => [
'key' => '_campaign_tags', 'multiple' => true
],
],
];
}

Aliases

Aliases let you use friendly names instead of ugly meta key names:

// without aliases, you have to write
->withMeta('_campaign_budget')
// with aliases
->withMeta('budget')

Single vs Multiple values

Some meta keys can have multiple rows with the same key. Mark these with 'multiple' => true:

'aliases' => [
'budget' => '_campaign_budget', // single value'tags' => [
'key' => '_campaign_tags', 'multiple' => true// multiple values
], ],

Single values

$campaign = Campaign::query()->select('id')->withMeta('budget')->get();
$campaign->budget; // 5000

Multiple values are parsed into arrays:

$campaign = Campaign::query()->select('id')->withMeta('tags')->groupBy('id')->get();
$campaign->tags; // ['promo', 'seasonal', 'email']

Meta in Queries

withMeta() makes meta keys available in select, where, orderBy, groupBy

// get specific keys
Campaign::query()->select('id', 'name')
->withMeta('budget', 'channel')
->where('budget', 1000, '>') // WHERE meta_budget.meta_value > 1000
->orderBy('budget', 'DESC') // ORDER BY meta_budget.meta_value DESC
->groupBy('channel') // GROUP BY meta_channel.meta_value
->getAll();
// get campaign with all meta keys
Campaign::query()->select('id', 'name')->withMeta()->getAll();

Meta in Mutations

// Insert: meta fields are auto-separated from table columns
Campaign::query()->withMeta('budget', 'tags')->insert([
'name' => 'Summer',
'slug' => 'summer',
'budget' => '1911', // 1 row in campaign_meta'tags' => ['promo', 'seasonal'], // 2 rows in campaign_meta
]);
// Update: single meta upserted, multiple meta replaced
Campaign::query()->withMeta('budget', 'tags')->where('id', 1)->update([
'budget' => '1950', // updates existing meta row'tags' => ['promo', 'holiday'], // deletes old rows, inserts new
]);
// Delete: meta rows are deleted before campaign
Campaign::query()->withMeta('budget')->where('id', 1)->delete();

Relations

Relations define how tables are connected. The parent table's rows are fetched first, then related rows are loaded in a separate query and attached

Configuration

protectedfunctionrelations(): array
{
return [
'entries' => [
'table' => 'campaign_entries',
'foreignKey' => 'campaign_id',
'primaryKey' => 'id',
'type' => 'hasMany', // hasMany | hasOne | belongsTo
],
];
}

Usage

// eager load relations with with()$campaign = Campaign::query()->select('id', 'name')->with('entries')->get();
// hasMany returns an array$campaign->entries;
// [// ['id' => 1, 'email' => 'a@test.com'],// ['id' => 2, 'email' => 'b@test.com'],// ]// hasOne returns single row or null$campaign->profile; // ['id' => 1, 'bio' => '...'] or null

Relations work with both get() and getAll().

Migrations

Each model defines its table structure via schema() and a $version string. Calling migrate() runs dbDelta()

Defining Schema

Schema is defined separately from the model class using a static callback

class Campaign extends Model
{
protectedstring$table = 'campaigns';
protectedstring$version = '1.0.0';
publicint$id;
publicstring$name;
// ...
}
Campaign::schema(function (Table$table) {
$table->id();
$table->string('name');
$table->string('slug', 100)->unique();
$table->decimal('price', 8, 2)->default(0);
$table->integer('stock')->default(0);
$table->boolean('active')->default(true);
$table->text('description')->nullable();
$table->datetime('created_at')->nullable();
});

Running Migrations

When you change the schema, bump the version

protected string $version = '1.1.0'; // added 'priority' column

Call migrate() on plugin activation:

register_activation_hook(__FILE__, function () {
require_onceABSPATH . 'wp-admin/includes/upgrade.php';
Campaign::migrate();
CampaignEntry::migrate();
});

Force migration regardless of version

Campaign::migrate(true);

Available Column Types

Method	MySQL Type
`$table->id()`	`BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY`
`$table->string('name')`	`VARCHAR(255) NOT NULL`
`$table->string('code', 50)`	`VARCHAR(50) NOT NULL`
`$table->text('body')`	`TEXT NOT NULL`
`$table->longText('content')`	`LONGTEXT NOT NULL`
`$table->integer('count')`	`INT NOT NULL`
`$table->bigInteger('views')`	`BIGINT NOT NULL`
`$table->tinyInteger('priority')`	`TINYINT NOT NULL`
`$table->float('rating')`	`FLOAT NOT NULL`
`$table->decimal('price', 8, 2)`	`DECIMAL(8,2) NOT NULL`
`$table->boolean('active')`	`TINYINT(1) NOT NULL`
`$table->date('birth_date')`	`DATE NOT NULL`
`$table->datetime('published_at')`	`DATETIME NOT NULL`
`$table->timestamp('verified_at')`	`TIMESTAMP NOT NULL`
`$table->json('settings')`	`JSON NOT NULL`
`$table->enum('status', ['a', 'b'])`	`ENUM('a','b') NOT NULL`

JSON Columns

Models with array properties on json() columns roundtrip transparently. Arrays are JSON-encoded on save and decoded on read.

class Donation extends Model
{
protectedstring$table = 'donations';
publicint$id;
public ?array$gateway_metadata = null;
public ?array$source_attribution = null;
}
Donation::schema(function (Table$t) {
$t->id();
$t->json('gateway_metadata')->nullable();
$t->json('source_attribution')->nullable();
});
$d = Donation::make();
$d->gateway_metadata = ['stripe' => 'pi_123', 'fee' => 25];
$d->save();
$reloaded = Donation::query()->find('id', $d->id);
$reloaded->gateway_metadata; // ['stripe' => 'pi_123', 'fee' => 25]

Notes:

null stays null in both directions.
If the property type is ?string, the user supplied string is written verbatim.
Encoding uses JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES so multibyte characters and URLs stay readable in the DB.
Column metadata is derived once per class per request from the registered schema() callback and cached.

Column Modifiers

$table->string('email')->unique();
$table->integer('stock')->default(0)->unsigned();
$table->datetime('deleted_at')->nullable();
$table->boolean('featured')->default(false);
$table->bigInteger('user_id')->unsigned()->references('users', 'ID')->onDelete('CASCADE');
$table->string('country', 2)->index();

Available modifiers: ->nullable(), ->unique(), ->primary(), ->unsigned(), ->default($value), ->references($table, $column), ->onDelete($action), ->index($name = null)

Indexes

Single-column indexes can be declared fluently on the column:

$table->string('country', 2)->index(); // KEY idx_country (country)$table->string('country', 2)->index('cc_idx'); // KEY cc_idx (country)

Composite indexes are declared on the table:

$table->index(['status', 'paid_at']); // KEY idx_status_paid_at (status,paid_at)$table->index(['status', 'paid_at'], 'idx_pay_status'); // KEY idx_pay_status (status,paid_at)

Composite unique indexes use Table::unique()

$table->unique(['gateway', 'external_id']); // UNIQUE KEY uk_gateway_external_id (gateway,external_id)$table->unique(['gateway', 'external_id'], 'uk_gw_ext'); // UNIQUE KEY uk_gw_ext (gateway,external_id)

When no name is supplied, names are generated as idx_ / uk_ + the column list joined with underscores, lowercased. Names longer than 64 characters (MySQL's identifier limit) are truncated with a 6-char hash suffix to stay collision-safe.

Output is dbDelta()-compatible — uppercase KEY on its own line with tight (col1,col2) formatting.

Meta Table Auto-Creation

If the model has a meta() config, migrate() automatically creates the meta table

protectedfunctionmeta(): array
{
return [
'table' => 'campaign_meta',
'foreignKey' => 'campaign_id',
'primaryKey' => 'id',
];
}
// Campaign::migrate() creates both tables

Transactions

// using a model
Campaign::transaction(function () {
Campaign::query()->insert(['name' => 'A', 'slug' => 'a']);
Campaign::query()->insert(['name' => 'B', 'slug' => 'b']);
});
// using DB facadeDB::transaction(function () {
DB::table('campaigns')->insert(['name' => 'A', 'slug' => 'a']);
DB::table('campaigns')->insert(['name' => 'B', 'slug' => 'b']);
});
// auto-commits on success, rollbacks if an exception is thrown

Raw Queries

DB::raw('SELECT * FROM wp_posts WHERE post_status = %s', ['publish']);

Parameters use $wpdb->prepare() for escaping

Conditional Clauses

Conditionally add clauses

$status = $_GET['status'] ?? null;
Campaign::query()->select('id', 'name')
->when($status, fn ($qb) => $qb->where('status', $status))
->getAll();

Clone

Create an independent copy to build query variants

$base = Campaign::query()->select('id', 'name');
$active = $base->clone()->where('status', 'active');
$drafts = $base->clone()->where('status', 'draft');

SQL Output

Get the generated SQL without executing:

Campaign::query()->select('id', 'name')->where('status', 'active')->toSQL();
// SELECT id, name FROM wp_campaigns WHERE status = 'active'

Name		Name	Last commit message	Last commit date
Latest commit History 12 Commits
bin		bin
src		src
tests		tests
.gitignore		.gitignore
.php-cs-fixer.php		.php-cs-fixer.php
README.md		README.md
composer.json		composer.json
composer.lock		composer.lock
phpunit-wp.xml		phpunit-wp.xml
phpunit.xml		phpunit.xml

Folders and files

Latest commit

History

Repository files navigation

Queryable

Installation

Two ways to use

1. Query Builder (DB Facade)

2. Model (Recommended)

Table of Contents

Base Query

Scopes

Save

Lifecycle Hooks

Insert

Update

Delete

Select

Where Clauses

Joins

Ordering, Grouping, Limit & Offset

Having

Union

Aggregates

Find & Exists

Pluck

Meta Tables

Configuration

Aliases

Single vs Multiple values

Meta in Queries

Meta in Mutations

Relations

Configuration

Usage

Migrations

Defining Schema

Running Migrations

Available Column Types

JSON Columns

Column Modifiers

Indexes

Meta Table Auto-Creation

Transactions

Raw Queries

Conditional Clauses

Clone

SQL Output

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages