Examples

Real-world code samples for WPZylos Database.

Product Repository

use WPZylos\Framework\Database\Connection;

class ProductRepository
{
    private Connection $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function all(): array
    {
        return $this->db->table('products')
            ->where('deleted_at', '=', null)
            ->orderBy('name')
            ->get();
    }

    public function find(int $id): ?object
    {
        return $this->db->table('products')
            ->where('id', $id)
            ->first();
    }

    public function create(array $data): int|false
    {
        return $this->db->table('products')->insert([
            'name' => sanitize_text_field($data['name']),
            'price' => floatval($data['price']),
            'stock' => intval($data['stock']),
            'created_at' => current_time('mysql'),
        ]);
    }

    public function update(int $id, array $data): int|false
    {
        return $this->db->table('products')
            ->where('id', $id)
            ->update([
                'name' => sanitize_text_field($data['name']),
                'price' => floatval($data['price']),
                'updated_at' => current_time('mysql'),
            ]);
    }

    public function delete(int $id): int|false
    {
        // Soft delete
        return $this->db->table('products')
            ->where('id', $id)
            ->update(['deleted_at' => current_time('mysql')]);
    }

    public function paginate(int $page = 1, int $perPage = 10): array
    {
        $offset = ($page - 1) * $perPage;

        return [
            'data' => $this->db->table('products')
                ->where('deleted_at', '=', null)
                ->orderBy('created_at', 'DESC')
                ->limit($perPage)
                ->offset($offset)
                ->get(),
            'total' => $this->db->table('products')
                ->where('deleted_at', '=', null)
                ->count(),
        ];
    }
}

Order Processing

use WPZylos\Framework\Database\Connection;

class OrderService
{
    private Connection $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function placeOrder(array $items, int $userId): mixed
    {
        return $this->db->transaction(function () use ($items, $userId) {
            // Create order
            $orderId = $this->db->table('orders')->insert([
                'user_id' => $userId,
                'status' => 'pending',
                'total' => array_sum(array_column($items, 'subtotal')),
                'created_at' => current_time('mysql'),
            ]);

            // Create order items and update stock
            foreach ($items as $item) {
                $this->db->table('order_items')->insert([
                    'order_id' => $orderId,
                    'product_id' => $item['product_id'],
                    'quantity' => $item['quantity'],
                    'price' => $item['price'],
                ]);

                // Update stock via raw query
                $table = $this->db->wpdb()->prefix . 'myplugin_products';
                $this->db->query(
                    "UPDATE `{$table}` SET stock = stock - %d WHERE id = %d",
                    $item['quantity'],
                    $item['product_id']
                );
            }

            return $orderId;
        });
    }
}

Search and Filter

use WPZylos\Framework\Database\Connection;

class ProductSearch
{
    private Connection $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function search(array $filters): array
    {
        $query = $this->db->table('products')
            ->where('deleted_at', '=', null);

        if (!empty($filters['search'])) {
            $search = '%' . $filters['search'] . '%';
            $query->where('name', 'LIKE', $search);
        }

        if (!empty($filters['category'])) {
            $query->where('category_id', $filters['category']);
        }

        if (!empty($filters['min_price'])) {
            $query->where('price', '>=', $filters['min_price']);
        }

        if (!empty($filters['max_price'])) {
            $query->where('price', '<=', $filters['max_price']);
        }

        if (!empty($filters['in_stock'])) {
            $query->where('stock', '>', 0);
        }

        $orderBy = $filters['order_by'] ?? 'created_at';
        $orderDir = $filters['order_dir'] ?? 'DESC';
        $query->orderBy($orderBy, $orderDir);

        return $query->get();
    }
}