Download the PHP package wpmvc/database without Composer

WpMVC Database

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.

Table of Contents

• Installation
• Schema Builder
  • Creating Tables
  • Altering Tables
  • Dropping or Renaming Tables
  • Returning SQL Queries
  • Foreign Key Safety
  • Supported Blueprint Methods
• Eloquent Models
  • Creating Models
  • Inserting Data
  • Updating Data
  • Deleting Data
  • Reading Data
  • Aggregates
  • Retrieving Models
  • Select Statements
  • Joins
  • Where Clauses
  • Ordering, Grouping, Limit & Offset
• Relationships
  • One-to-One
  • One-to-Many
  • One-to-Many (Inverse) / Belongs To
  • Constraining Query Loads
• License

Installation

Install WpMVC Database using Composer:

Schema Builder

The Schema Builder provides a fluent interface for creating and modifying database tables.

Creating Tables

Create a table with the Schema::create method:

Altering Tables

Modify an existing table with Schema::alter:

Dropping or Renaming Tables

Drop or rename tables as needed:

Returning SQL Queries

Generate SQL without executing it by passing true as the third argument:

Foreign Key Safety

Foreign keys are automatically checked to avoid duplicates. The naming convention is:

Example Schema

A complete example for a products table:

Supported Blueprint Methods

Column Types

• 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(): Adds created_at and updated_at TIMESTAMP columns.
• boolean(name): BOOLEAN column.

Column Modifiers

• 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 for ALTER).

Indexes & Constraints

• 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.

Eloquent Models

Creating Models

Define an Eloquent model by extending the Model class:

Inserting Data

Insert a single record:

Insert multiple records:

Insert and retrieve the ID:

Updating Data

Update a record based on a condition:

Deleting Data

Delete a record based on a condition:

Reading Data

Aggregates

Retrieve aggregate values like count, max, min, avg, or sum:

Retrieving Models

Fetch all records:

Fetch a single record:

Select Statements

Select specific columns:

Use distinct for unique results:

Joins

Perform an inner join:

Perform left or right joins:

Advanced join with a closure:

Where Clauses

Basic where clause:

Or where clause:

Where exists clause:

Where between:

Where in:

Ordering, Grouping, Limit & Offset

Order results:

Group results:

Limit and offset:

Relationships

WpMVC Database supports common Eloquent relationships for managing related data.

One-to-One

Define a one-to-one relationship (e.g., a User has one Phone):

Retrieve users with their phones:

One-to-Many

Define a one-to-many relationship (e.g., a Post has many PostMeta):

One-to-Many (Inverse) / Belongs To

Define the inverse relationship (e.g., a PostMeta belongs to a Post):

Constraining Query Loads

Add conditions to relationship queries:

License

WpMVC Database is open-source software licensed under the MIT License.