Booknetic uses a custom ActiveRecord-style ORM built on top of WordPress’s $wpdb. It provides a practical and expressive way to work with database records using models, a chainable query builder, collection objects, model relationships, global scopes, lifecycle hooks, and automatic multi-tenant isolation.

For addon developers and internal feature development, this ORM is the main database layer used across Booknetic. It allows you to write readable model-based queries instead of manually building SQL for common operations.

Architecture Overview

The ORM is built around four core classes:

The data flow looks like this:

Model::query() → QueryBuilder → DB (raw SQL) → Collection (result)

Models

Models represent database tables and act as the entry point for most ORM operations.

Defining a Model

Every database table is represented by a model class inside app/Models/. A model extends BookneticApp\Providers\DB\Model:

<?php

namespace BookneticApp\Models;

use BookneticApp\Providers\DB\Model;
use BookneticApp\Providers\DB\MultiTenant;

class Customer extends Model
{
    use MultiTenant;

    public static $relations = [
        'category' => [CustomerCategory::class, 'id', 'category_id']
    ];
}

That is enough to make the model functional. No explicit table name definition is required unless you want to override the default.

Table Name Convention

Table names are generated automatically from the class name using this rule:

PascalCase → snake_case + plural

All Booknetic ORM tables are prefixed with {wp_base_prefix}bkntc_, such as wp_bkntc_.

If needed, you can override the generated table name:

class MyModel extends Model
{
    protected static $tableName = 'custom_table_name';
}

Available Core Models

The following are commonly used built-in models:

Query Builder

The query builder is the main interface for retrieving, filtering, joining, updating, and deleting records.

Starting a Query

Every query starts from a model:

// Option 1: Using query()
$query = Customer::query();

// Option 2: Call methods directly on the Model (auto-creates QueryBuilder)
$customer = Customer::where('email', '[email protected]')->fetch();

// Option 3: Get by ID
$customer = Customer::get(5);

Retrieving Data

Fetch a Single Row

// Returns a Collection object, or null if not found
$customer = Customer::query()
    ->where('email', '[email protected]')
    ->fetch();

echo $customer->first_name;
echo $customer->email;

Fetch Multiple Rows

// Returns an array of Collection objects
$customers = Customer::query()
    ->where('gender', 'male')
    ->orderBy(['first_name' => 'ASC'])
    ->fetchAll();

foreach ($customers as $customer) {
    echo $customer->first_name . ' ' . $customer->last_name;
}

Fetch as Plain Arrays

// Returns array of associative arrays (no Collection wrapping)
$rows = Customer::query()
    ->where('gender', 'male')
    ->fetchAllAsArray();

Get by ID

$appointment = Appointment::get(42);
// Equivalent to: Appointment::query()->where('id', 42)->fetch();

WHERE Conditions

Basic Where

// Equals
Customer::where('email', '[email protected]')->fetch();

// With operator
Appointment::where('starts_at', '>', '2025-01-01 00:00:00')->fetchAll();

// Multiple conditions (AND)
Appointment::query()
    ->where('status', 'approved')
    ->where('staff_id', 3)
    ->fetchAll();

Array Where

// Pass multiple conditions as an array
Appointment::where([
    'status'   => 'approved',
    'staff_id' => 3
])->fetchAll();

OR Where

Customer::query()
    ->where('first_name', 'John')
    ->orWhere('first_name', 'Jane')
    ->fetchAll();

Where ID

// Shorthand for where('id', $value)
Customer::whereId(5)->fetch();

NULL Checks

// IS NULL
Appointment::query()->whereIsNull('recurring_id')->fetchAll();

// IS NOT NULL
Appointment::query()->whereIsNotNull('note')->fetchAll();

// Using where() directly
Appointment::where('tenant_id', 'is', null)->fetchAll();
Appointment::where('tenant_id', 'is not', null)->fetchAll();

IN Clause

// Pass an array as the value
Appointment::where('status', ['approved', 'pending'])->fetchAll();

// NOT IN
Appointment::where('status', 'not in', ['canceled', 'rejected'])->fetchAll();

LIKE

// Automatically wraps with %...%
Customer::query()->like('first_name', 'John')->fetchAll();
// Generates: WHERE first_name LIKE '%John%'

// OR LIKE
Customer::query()
    ->like('first_name', 'John')
    ->orLike('last_name', 'John')
    ->fetchAll();

FIND_IN_SET

For comma-separated value columns:

Service::query()->whereFindInSet('staff_ids', 5)->fetchAll();
// Generates: WHERE FIND_IN_SET('5', staff_ids)

Grouped Conditions (Nested Where)

Customer::query()
    ->where(function ($query) {
        $query->where('first_name', 'John')
              ->orWhere('first_name', 'Jane');
    })
    ->where('gender', 'male')
    ->fetchAll();
// Generates: WHERE (first_name = 'John' OR first_name = 'Jane') AND gender = 'male'

SubQuery in Where

// Get customers who have at least one appointment
$appointmentCustomers = Appointment::query()->select('customer_id', true);

Customer::query()
    ->where('id', 'in', $appointmentCustomers)
    ->fetchAll();
// Generates: WHERE id IN (SELECT customer_id FROM wp_bkntc_appointments ...)

Selecting Columns

// Select specific columns
Customer::query()
    ->select(['first_name', 'last_name', 'email'])
    ->fetchAll();

// Replace all previous selects
Customer::query()
    ->select(['first_name', 'email'], true)
    ->fetchAll();

// SubQuery as a column
$countQuery = Appointment::query()
    ->select(['COUNT(*)'], true)
    ->where('customer_id', DB::field('id', Customer::getTableName()));

Customer::query()
    ->selectSubQuery($countQuery, 'appointment_count')
    ->fetchAll();

// Each result now has ->appointment_count

Ordering

Appointment::query()
    ->orderBy(['starts_at' => 'DESC'])
    ->fetchAll();

// Multiple order columns
Appointment::query()
    ->orderBy(['status' => 'ASC', 'starts_at' => 'DESC'])
    ->fetchAll();

Grouping

Appointment::query()
    ->select(['status', 'COUNT(*) as count'])
    ->groupBy(['status'])
    ->fetchAll();

Pagination

$page = 2;
$perPage = 10;

$appointments = Appointment::query()
    ->orderBy(['starts_at' => 'DESC'])
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->fetchAll();

Aggregates

// Count
$count = Appointment::query()
    ->where('status', 'approved')
    ->count();

// Count with GROUP BY
$count = Appointment::query()
    ->groupBy(['status'])
    ->countGroupBy();

// Sum
$total = Appointment::query()
    ->where('payment_status', 'paid')
    ->sum('paid_amount');

Joins

Basic Join

$appointments = Appointment::query()
    ->leftJoin('customers', ['first_name', 'last_name', 'email'], 'customer_id', 'id')
    ->where('status', 'approved')
    ->fetchAll();

// Joined columns are accessible with table prefix:
foreach ($appointments as $apt) {
    echo $apt->customers_first_name;  // Column alias: {table}_{column}
    echo $apt->customers_email;
}

The join methods use this signature:

->leftJoin($joinTo, $selectFields, $field1, $field2, $unselectOldFields, $alias)
->rightJoin(...)
->innerJoin(...)

Relation-Based Join

If a relation is defined on the model, you can join by relation name without manually defining the fields:

// Appointment has 'customer' relation defined
$appointments = Appointment::query()
    ->leftJoin('customer', ['first_name', 'last_name'])
    ->fetchAll();
// Automatically uses the relation's field mapping

Self Join

Appointment::query()
    ->leftJoinSelf('parent', ['status'], 'id', 'recurring_id')
    ->fetchAll();
// Joins appointments table to itself using alias 'parent'

Join with Alias

Appointment::query()
    ->leftJoin('staffs', ['name'], 'staff_id', 'id', false, 'assigned_staff')
    ->fetchAll();

// Access: $appointment->assigned_staff_name

Insert

Customer::query()->insert([
    'first_name'   => 'John',
    'last_name'    => 'Doe',
    'email'        => '[email protected]',
    'phone_number' => '+1234567890'
]);

// Get the inserted ID
$newId = DB::lastInsertedId();
// or
$newId = Customer::lastId();

Update

// Update by ID
Customer::query()
    ->whereId(5)
    ->update(['first_name' => 'Jane']);

// Update with conditions
Appointment::query()
    ->where('status', 'pending')
    ->where('starts_at', '<', '2025-01-01 00:00:00')
    ->update(['status' => 'canceled']);

Updating with Raw Field References

Use DB::field() when you want an update value to reference another column instead of being treated as a plain string:

Appointment::query()
    ->whereId($id)
    ->update([
        'paid_amount' => DB::field('paid_amount + 100')
    ]);
// Generates: SET paid_amount = paid_amount + 100

Delete

// Delete by ID
Customer::query()->whereId(5)->delete();

// Delete with conditions
Appointment::query()
    ->where('status', 'canceled')
    ->where('starts_at', '<', '2024-01-01 00:00:00')
    ->delete();

Debugging

You can inspect the generated SQL without executing the query:

$sql = Appointment::query()
    ->where('status', 'approved')
    ->orderBy(['starts_at' => 'DESC'])
    ->limit(10)
    ->toSql();

error_log($sql);
// Output: SELECT * FROM wp_bkntc_appointments WHERE status = 'approved' ORDER BY starts_at DESC LIMIT 0, 10

Collection

When fetch() returns a single record, it is returned as a Collection object. When fetchAll() is used, it returns an array of Collection objects.

Accessing Data

$customer = Customer::get(1);

// Property access
echo $customer->first_name;
echo $customer->email;

// Array access
echo $customer['first_name'];
echo $customer['email'];

// Convert to plain array
$array = $customer->toArray();

Computed Attributes

Models can define virtual or computed attributes using get{AttributeName}Attribute methods.

Example in a Customer model:

public function getFullNameAttribute($customer)
{
    return $customer->first_name . ' ' . $customer->last_name;
}

// Usage:
$customer = Customer::get(1);
echo $customer->full_name; // "John Doe"

Example in an Appointment model:

public static function getStatusNameAttribute($appointmentInf)
{
    $statuses = Helper::getAppointmentStatuses();
    return $statuses[$appointmentInf->status]['title'] ?? $appointmentInf->status;
}

// Usage:
$appointment = Appointment::get(1);
echo $appointment->status_name; // "Approved"

Null Safety

If fetch() does not find a matching row, it returns null, not an empty collection. Always handle that case:

$customer = Customer::where('email', '[email protected]')->fetch();

if ($customer) {
    echo $customer->first_name;
}

Relations

Relations let you move between related models and also power automatic join mapping.

Defining Relations

Relations are defined in the model $relations array:

public static $relations = [
    'relationName' => [RelatedModel::class, 'foreignKey', 'localKey']
];

Example from an Appointment model:

public static $relations = [
    'extras'   => [AppointmentExtra::class, 'appointment_id', 'id'],
    'location' => [Location::class, 'id', 'location_id'],
    'service'  => [Service::class, 'id', 'service_id'],
    'staff'    => [Staff::class, 'id', 'staff_id'],
    'customer' => [Customer::class, 'id', 'customer_id'],
    'prices'   => [AppointmentPrice::class, 'appointment_id', 'id'],
];

Using Relations

Lazy Loading

Calling a relation as a method on a collection returns a QueryBuilder for the related records:

$appointment = Appointment::get(1);

// Returns a QueryBuilder — call fetch()/fetchAll() to get results
$customer = $appointment->customer()->fetch();
$extras = $appointment->extras()->fetchAll();
$service = $appointment->service()->fetch();

echo $customer->first_name;
echo $service->name;

Eager Loading via Join

For better performance and to avoid N+1 queries, use joins:

$appointments = Appointment::query()
    ->leftJoin('customer', ['first_name', 'last_name', 'email'])
    ->leftJoin('service', ['name', 'price', 'duration'])
    ->leftJoin('staff', ['name'])
    ->where('status', 'approved')
    ->orderBy(['starts_at' => 'DESC'])
    ->limit(20)
    ->fetchAll();

foreach ($appointments as $apt) {
    // Joined columns use {relation}_{column} naming
    echo $apt->customer_first_name;
    echo $apt->service_name;
    echo $apt->staff_name;
    echo $apt->starts_at;
}

Relations also enable automatic field mapping in joins, so the foreign and local keys do not need to be repeated manually:

// These two are equivalent:
Appointment::query()->leftJoin('customer', ['first_name'], 'customer_id', 'id');
Appointment::query()->leftJoin('customer', ['first_name']); // Uses relation definition

Global Scopes

Global scopes automatically apply conditions to every query on a model. This is how Booknetic handles features such as tenant isolation and staff-based access restriction.

How Scopes Work

class Appointment extends Model
{
    use MultiTenant {
        booted as private tenantBoot;
    }

    public static function booted()
    {
        self::tenantBoot(); // Adds tenant_id scope

        // Staff members can only see their own appointments
        self::addGlobalScope('staff_id', function (QueryBuilder $builder, $queryType) {
            if (!Permission::isBackEnd() || Permission::isAdministrator()) {
                return; // No restriction for frontend or admin users
            }

            $builder->where('staff_id', Permission::myStaffId());
        });
    }
}

Every query on Appointment now automatically includes:

• WHERE tenant_id = {current_tenant} from MultiTenant
• WHERE staff_id = {current_staff} for non-admin backend users

The $queryType Parameter

The scope callback receives the query type, so you can vary behavior depending on the operation:

Disabling a Scope

You can disable a specific scope when needed:

// Skip a specific scope
Appointment::query()
    ->withoutGlobalScope('staff_id')
    ->fetchAll();

// Skip tenant scope
Appointment::query()
    ->noTenant()
    ->fetchAll();

Multi-Tenancy

The MultiTenant trait provides automatic tenant isolation in SaaS mode.

How It Works

When a model uses MultiTenant:

• SELECT / UPDATE / DELETE queries automatically include WHERE tenant_id = {current_tenant}
• INSERT queries automatically set tenant_id = {current_tenant}
• In non-SaaS mode, filtering is done using WHERE tenant_id IS NULL

class Customer extends Model
{
    use MultiTenant;
}

// In SaaS mode, this query automatically becomes:
// SELECT * FROM wp_bkntc_customers WHERE tenant_id = 5
Customer::query()->fetchAll();

Bypassing Tenant Filter

Use noTenant() when you intentionally need to query across all tenants, such as in super admin logic:

// Query ALL customers across all tenants
$allCustomers = Customer::query()
    ->noTenant()
    ->fetchAll();

Models Without Multi-Tenancy

Some tables do not have a tenant_id column, such as appointment_extras, appointment_prices, and data. Their models simply do not use the MultiTenant trait, so no tenant filtering is applied.

Lifecycle Hooks

Models support lifecycle hooks that fire around CRUD operations:

If a before hook such as onCreating, onUpdating, or onDeleting returns false, the operation is canceled.

Usage

// Cancel deletion of a specific record
Customer::onDeleting(function ($customerId) {
    if ($customerId === 1) {
        return false; // Prevent deletion of customer #1
    }
});

// Log every new appointment
Appointment::onCreated(function (QueryBuilder $queryBuilder) {
    $data = $queryBuilder->getProperties();
    error_log('New appointment for service: ' . $data['service_id']);
});

Automatic Timestamps

Set $timeStamps = true to automatically fill created_at and updated_at:

class MyModel extends Model
{
    protected static bool $timeStamps = true;
}

On insert, both created_at and updated_at are filled automatically. On update, updated_at is updated automatically.

Automatic Ownership

Set $enableOwnershipFields = true to automatically fill created_by and updated_by with the current WordPress user ID:

class MyModel extends Model
{
    protected static bool $enableOwnershipFields = true;
}

Meta Data (Key-Value Storage)

Every model includes support for storing arbitrary key-value data through the data table. This is useful when addon-specific data needs to be stored without changing the main table schema.

On a Model (Static)

// Set meta data for a customer
Customer::setData($customerId, 'preferred_language', 'en');

// Get meta data
$lang = Customer::getData($customerId, 'preferred_language', 'default_value');

// Delete meta data
Customer::deleteData($customerId, 'preferred_language');

On a Collection Instance

$customer = Customer::get(5);

// Set
$customer->setData('vip_level', 'gold');

// Get
$level = $customer->getData('vip_level', 'standard');

// Delete
$customer->deleteData('vip_level');

Internally, this data is stored in the wp_bkntc_data table:

DB Class (Low-Level)

When the ORM layer is not sufficient or when you need direct access to WordPress database tools, you can work with the DB class directly.

Table Name Resolution

use BookneticApp\Providers\DB\DB;

// Get the full table name with prefix
DB::table('appointments');     // "wp_bkntc_appointments"
DB::table(Appointment::class); // "wp_bkntc_appointments"

Raw Queries

// Prepared query (safe from SQL injection)
$sql = DB::raw("SELECT * FROM %s WHERE status = %s", [
    DB::table('appointments'),
    'approved'
]);

// Execute via WordPress $wpdb
$results = DB::DB()->get_results($sql, ARRAY_A);

Direct Database Access

// Get the WordPress $wpdb instance
$wpdb = DB::DB();

// Use any $wpdb method
$wpdb->query("...");
$wpdb->get_row("...");
$wpdb->get_results("...");

Query Caching

// Enable caching — identical queries return cached results
DB::enableCache();

// Your queries here...
$a = Customer::get(1); // Hits database
$b = Customer::get(1); // Returns cached result

// Disable and clear cache
DB::disableCache();

Last Inserted ID

Customer::query()->insert([...]);
$id = DB::lastInsertedId();
// or
$id = Customer::lastId();

Field References

Use DB::field() when a query needs to reference a column name instead of a literal string value:

// Compare two columns
Appointment::where('starts_at', '>', DB::field('busy_from'))->fetchAll();

// Update using another column's value
Appointment::query()->whereId($id)->update([
    'paid_amount' => DB::field('paid_amount + 50')
]);

Translations

Models that support multi-language content use the Translator trait:

class Service extends Model
{
    use Translator, MultiTenant;

    protected static $translations = ['name', 'note'];
}

To retrieve translated values, call withTranslations():

// Fetch with translations applied (based on current locale)
$services = Service::query()
    ->withTranslations()
    ->fetchAll();

echo $services[0]->name; // Returns translated name if available

Common Patterns

These are some common ORM usage patterns in Booknetic development.

Pagination with Total Count

$page = 1;
$perPage = 15;

$query = Appointment::query()
    ->where('status', 'approved')
    ->orderBy(['starts_at' => 'DESC']);

$total = $query->count();
$appointments = $query
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->fetchAll();

Complex Report Query

$report = Appointment::query()
    ->select([
        'service_id',
        'COUNT(*) as total_bookings',
        'SUM(paid_amount) as total_revenue'
    ], true)
    ->leftJoin('service', ['name'])
    ->where('status', 'approved')
    ->where('starts_at', '>=', '2025-01-01 00:00:00')
    ->groupBy(['service_id'])
    ->orderBy(['total_bookings' => 'DESC'])
    ->fetchAll();

foreach ($report as $row) {
    echo $row->service_name . ': ' . $row->total_bookings . ' bookings, $' . $row->total_revenue;
}

Conditional Query Building

$query = Appointment::query()
    ->orderBy(['starts_at' => 'DESC']);

if ($statusFilter) {
    $query->where('status', $statusFilter);
}

if ($staffId) {
    $query->where('staff_id', $staffId);
}

if ($search) {
    $query->leftJoin('customer', ['first_name', 'last_name']);
    $query->like('customers.first_name', $search);
}

$results = $query->limit(20)->fetchAll();

Check If Record Exists

$exists = Customer::query()
    ->where('email', '[email protected]')
    ->count() > 0;

Get or Create Pattern

$customer = Customer::where('email', $email)->fetch();

if (!$customer) {
    Customer::query()->insert([
        'first_name' => $firstName,
        'last_name'  => $lastName,
        'email'      => $email
    ]);
    $customer = Customer::get(Customer::lastId());
}

Booknetic’s ORM and query builder provide a full model-driven database layer with expressive query building, relationship support, automatic tenant isolation, lifecycle hooks, translation support, key-value metadata, and direct low-level database access when needed.