WpMVC Database is a powerful SQL query builder tailored for WordPress plugins, offering a fluent and intuitive interface inspired by Laravel's Eloquent Query Builder. It simplifies database operations, relationships, and schema management for WordPress developers.
Install WpMVC Database using Composer:
composer require wpmvc/databaseThe Schema Builder provides a fluent interface for creating and modifying database tables.
Create a table with the Schema::create method:
use WpMVC\Database\Schema;
Schema::create('products', function (Blueprint $table) {
$table->big_increments('id');
$table->unsigned_big_integer('category_id');
$table->string('title');
$table->long_text('description')->nullable();
$table->enum('visibility', ['publish', 'draft'])->default('publish');
$table->timestamps();
$table->foreign('category_id')
->references('id')
->on('categories')
->on_delete('cascade');
});Modify an existing table with Schema::alter:
Schema::alter('products', function (Blueprint $table) {
$table->string('short_description')->after('title')->nullable();
$table->drop_column('legacy_column');
$table->drop_index('index_abc123');
});Drop or rename tables as needed:
Schema::drop_if_exists('products');
Schema::rename('old_products', 'products');Generate SQL without executing it by passing true as the third argument:
$sql = Schema::create('products', function (Blueprint $table) {
$table->string('title');
}, true);
echo $sql;Foreign keys are automatically checked to avoid duplicates. The naming convention is:
fk_{prefix}{table}_{column}
A complete example for a products table:
Schema::create('products', function (Blueprint $table) {
$table->big_increments('id');
$table->unsigned_big_integer('category_id');
$table->string('title');
$table->string('sku')->nullable();
$table->long_text('description')->nullable();
$table->decimal('price', 10, 2)->default(0.00);
$table->boolean('is_active')->default(true);
$table->enum('status', ['publish', 'draft'])->default('publish');
$table->timestamps();
$table->index(['status']);
$table->foreign('category_id')
->references('id')
->on('categories')
->on_delete('cascade');
});big_increments(name): Auto-incrementing big integer (primary key).unsigned_big_integer(name): Unsigned big integer.integer(name): Signed integer.unsigned_integer(name): Unsigned integer.decimal(name, precision, scale): DECIMAL column with optional precision and scale (default:10, 2).string(name, length): VARCHAR column with optional length.text(name): TEXT column.long_text(name): LONGTEXT column.json(name): JSON column.enum(name, values): ENUM column with specified values.tiny_integer(name): TINYINT column.timestamp(name): TIMESTAMP column.timestamps(): Addscreated_atandupdated_atTIMESTAMP columns.boolean(name): BOOLEAN column.
nullable(): Allows NULL values.default(value): Sets a default value.comment(text): Adds a column comment.use_current(): Sets the default to the current timestamp.use_current_on_update(): Updates timestamp on record update.after(column): Places the column after another (only forALTER).
primary(column|[columns]): Sets primary key.unique(column|[columns]): Sets unique index.index(column|[columns]): Creates an index.drop_column(name): Drops a column.drop_index(name): Drops an index.foreign(column)->references()->on()->on_delete()->on_update(): Defines a foreign key constraint.
Define an Eloquent model by extending the Model class:
namespace WpMVC\App\Models;
use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Resolver;
class Post extends Model {
public static function get_table_name(): string {
return 'posts';
}
public function resolver(): Resolver {
return new Resolver;
}
}Insert a single record:
Post::query()->insert([
'post_author' => wp_get_current_user()->ID,
'post_title' => 'Test Post',
]);Insert multiple records:
Post::query()->insert([
[
'post_author' => wp_get_current_user()->ID,
'post_title' => 'Test Post 1',
],
[
'post_author' => wp_get_current_user()->ID,
'post_title' => 'Test Post 2',
],
]);Insert and retrieve the ID:
$post_id = Post::query()->insert_get_id([
'post_author' => wp_get_current_user()->ID,
'post_title' => 'Test Post',
]);Update a record based on a condition:
Post::query()->where('post_id', 100)->update([
'post_title' => 'Updated Post',
]);Delete a record based on a condition:
Post::query()->where('post_id', 100)->delete();Retrieve aggregate values like count, max, min, avg, or sum:
$count = Post::query()->count();Fetch all records:
$posts = Post::query()->get();Fetch a single record:
$post = Post::query()->where('id', 100)->first();Select specific columns:
$posts = Post::query()->select('post_title', 'post_date')->get();Use distinct for unique results:
$posts = Post::query()->distinct()->select('post_title')->get();Perform an inner join:
$users = User::query()
->join('contacts', 'users.id', '=', 'contacts.user_id')
->select('users.*', 'contacts.phone', 'contacts.email')
->get();Perform left or right joins:
$users = User::query()
->left_join('posts', 'users.id', '=', 'posts.user_id')
->get();Advanced join with a closure:
use WpMVC\Database\Query\JoinClause;
$posts = Post::query()->join('postmeta', function (JoinClause $join) {
$join->on('postmeta.post_id', '=', 'posts.ID')
->where('postmeta.meta_value', '>', 500);
})->get();Basic where clause:
$posts = Post::query()->where('post_status', 'publish')->get();Or where clause:
$posts = Post::query()
->where('post_status', 'publish')
->orWhere('post_title', 'Test Post')
->get();Where exists clause:
$posts = Post::query()->where_exists(function (Builder $query) {
$query->select(1)
->from('postmeta')
->where_column('postmeta.post_id', 'posts.id')
->limit(1);
})->get();Where between:
$posts = Post::query()->where_between('ID', [1, 100])->get();Where in:
$posts = Post::query()->where_in('ID', [100, 105])->get();Order results:
$posts = Post::query()->order_by('post_title', 'asc')->get();Group results:
$posts = Post::query()
->group_by('post_author')
->having('post_author', '>', 100)
->get();Limit and offset:
$posts = Post::query()->offset(10)->limit(5)->get();WpMVC Database supports common Eloquent relationships for managing related data.
Define a one-to-one relationship (e.g., a User has one Phone):
namespace WpMVC\App\Models;
use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Eloquent\Relations\HasOne;
class User extends Model {
public function phone(): HasOne {
return $this->has_one(Phone::class, 'ID', 'user_id');
}
}Retrieve users with their phones:
$users = User::query()->with('phone')->get();Define a one-to-many relationship (e.g., a Post has many PostMeta):
namespace WpMVC\App\Models;
use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Eloquent\Relations\HasMany;
class Post extends Model {
public function meta(): HasMany {
return $this->has_many(PostMeta::class, 'ID', 'post_id');
}
}Define the inverse relationship (e.g., a PostMeta belongs to a Post):
namespace WpMVC\App\Models;
use WpMVC\Database\Eloquent\Model;
use WpMVC\Database\Eloquent\Relations\BelongsToOne;
class PostMeta extends Model {
public function post(): BelongsToOne {
return $this->belongs_to_one(Post::class, 'post_id', 'ID');
}
}Add conditions to relationship queries:
use WpMVC\Database\Query\Builder;
$posts = Post::query()->with([
'meta' => function (Builder $query) {
$query->where('meta_id', 672);
},
'user',
])->get();WpMVC Database is open-source software licensed under the MIT License.