PHP code example of methorz / swift-db

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

methorz / swift-db example snippets

// config/autoload/database.global.php
return [
    'database' => [
        'connections' => [
            'default' => [
                'dsn' => 'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
                'username' => 'root',
                'password' => '',
            ],
            'replica' => [
                'dsn' => 'mysql:host=replica;dbname=myapp;charset=utf8mb4',
                'username' => 'readonly',
                'password' => '',
            ],
        ],
        'default' => 'default',
        'read_from' => 'replica',  // Optional: route reads to replica
        'write_to' => 'default',   // Optional: route writes to master
        'cache_dir' => 'data/cache/database',  // Optional: for reflection cache
    ],
];

declare(strict_types=1);

namespace App\Domain\Entity;

use MethorZ\SwiftDb\Entity\AbstractEntity;
use MethorZ\SwiftDb\Trait\TimestampsTrait;
use MethorZ\SwiftDb\Trait\UuidTrait;
use DateTimeImmutable;

class Product extends AbstractEntity
{
    use TimestampsTrait;
    use UuidTrait;

    public ?int $id = null;
    public string $name = '';
    public float $price = 0.0;
    public int $stock = 0;

    public static function getTableName(): string
    {
        return 'product';
    }

    public function getColumnMapping(): array
    {
        return [
            'id' => 'product_id',
            'name' => 'product_name',
            'price' => 'product_price',
            'stock' => 'product_stock',
            ...$this->getTimestampMapping(),
            ...$this->getUuidMapping(),
        ];
    }
}

declare(strict_types=1);

namespace App\Infrastructure\Repository;

use App\Domain\Entity\Product;
use MethorZ\SwiftDb\Repository\AbstractRepository;

/**
 * @extends AbstractRepository<Product>
 */
class ProductRepository extends AbstractRepository
{
    public function getTableName(): string
    {
        return 'product';
    }

    public function getEntityClass(): string
    {
        return Product::class;
    }

    /**
     * Find products by category with price range
     *
     * @return array<Product>
     */
    public function findByCategory(int $categoryId, float $minPrice, float $maxPrice): array
    {
        $rows = $this->query()
            ->where('product_category_id', $categoryId)
            ->whereBetween('product_price', $minPrice, $maxPrice)
            ->orderBy('product_price', 'ASC')
            ->get();

        return array_map(fn (array $row) => $this->hydrateEntity($row), $rows);
    }
}

// Get repository from container
$repository = $container->get(ProductRepository::class);

// Create and save
$product = $repository->create();
$product->name = 'Widget';
$product->price = 29.99;
$product->stock = 100;
$repository->save($product);

// Find
$product = $repository->find(1);
$product = $repository->findOrFail(1);  // Throws if not found

// Update
$product->price = 24.99;
$repository->save($product);  // Only updates changed fields

// Delete
$repository->delete($product);
// or
$repository->deleteById(1);

// Query builder
$products = $repository->query()
    ->where('product_price', '>', 10)
    ->whereNotNull('product_stock')
    ->orderBy('product_created', 'DESC')
    ->limit(10)
    ->get();

// Simple pagination
$result = $repository->paginate(perPage: 15, page: 1);

// Access items
foreach ($result as $row) {
    echo $row['product_name'];
}

// Or use items property
$items = $result->items;

// Pagination metadata
$result->total;             // Total records
$result->perPage;           // Items per page
$result->currentPage;       // Current page number
$result->lastPage();        // Last page number
$result->hasMorePages();    // Has next page?
$result->hasPreviousPage(); // Has previous page?

// SPL interfaces
count($result);             // Returns total (Countable)
foreach ($result as $row) { // Iterates items (IteratorAggregate)
    // ...
}

// With query conditions
$result = $repository->query()
    ->where('active', true)
    ->orderBy('created_at', 'DESC')
    ->paginate(20, 2);

// Bulk insert - fast!
$bulk = $repository->bulkInsert();
foreach ($products as $product) {
    $bulk->add($product);
}
$bulk->flush();  // Executes in batches of 1000

// Bulk upsert (INSERT ... ON DUPLICATE KEY UPDATE)
$bulk = $repository->bulkUpsert()
    ->onDuplicateKeyUpdate(['product_price', 'product_stock'])
    ->touchUpdatedOnDuplicate('product_updated');

foreach ($products as $product) {
    $bulk->add($product);
}
$bulk->flush();

// INSERT IGNORE
$bulk = $repository->bulkInsert()->ignore();

// Manual
$repository->beginTransaction();
try {
    $repository->save($product1);
    $repository->save($product2);
    $repository->commit();
} catch (\Throwable $e) {
    $repository->rollback();
    throw $e;
}

// Or use the helper
$repository->transaction(function () use ($repository, $product1, $product2) {
    $repository->save($product1);
    $repository->save($product2);
});

use MethorZ\SwiftDb\Trait\VersionTrait;

class Product extends AbstractEntity
{
    use VersionTrait;

    // ...

    public function getColumnMapping(): array
    {
        return [
            // ...
            ...$this->getVersionMapping(),
        ];
    }
}

// Now updates will check the version
try {
    $repository->save($product);
} catch (OptimisticLockException $e) {
    // Entity was modified by another process
}

// Implicit equals (Laravel-style)
$rows = $this->query()
    ->where('product_active', true)
    ->where('product_category_id', $categoryId)
    ->get();

// Array syntax
$rows = $this->query()
    ->where([
        'product_active' => true,
        'product_status' => 'published',
    ])
    ->get();

// Nested conditions (AND with OR inside)
// WHERE active = 1 AND (price < 10 OR featured = 1)
$rows = $this->query()
    ->where('product_active', true)
    ->where(function ($q) {
        $q->where('product_price', '<', 10)
          ->orWhere('product_featured', true);
    })
    ->get();

// Conditional building
$rows = $this->query()
    ->where('product_active', true)
    ->when($categoryId, fn($q, $id) => $q->where('product_category_id', $id))
    ->when($minPrice, fn($q, $min) => $q->where('product_price', '>=', $min))
    ->get();

// Join with conditions
$rows = $this->query()
    ->leftJoin('discount', function ($join) use ($today) {
        $join->on('product.product_id', 'discount.discount_product_id')
             ->where('discount.discount_active', true)
             ->where('discount.discount_start', '<=', $today);
    })
    ->get();

// Subquery in whereIn
$rows = $this->query()
    ->whereIn('product_category_id', function ($sub) {
        $sub->table('category')
            ->select('category_id')
            ->where('category_active', true);
    })
    ->get();

// EXISTS subquery
$rows = $this->query()
    ->whereExists(function ($sub) {
        $sub->table('inventory')
            ->select('1')
            ->whereColumn('inventory.product_id', 'product.product_id')
            ->where('inventory.quantity', '>', 0);
    })
    ->get();

// OR convenience methods - Multi-field search
// WHERE active = 1 AND (name LIKE ? OR sku LIKE ?)
$rows = $this->query()
    ->where('product_active', true)
    ->where(function ($q) use ($searchTerm) {
        $q->whereLike('product_name', "%{$searchTerm}%")
          ->orWhereLike('product_sku', "%{$searchTerm}%");
    })
    ->get();

// OR BETWEEN - Price range or high stock
// WHERE (price BETWEEN 10 AND 50) OR (stock BETWEEN 100 AND 500)
$rows = $this->query()
    ->whereBetween('product_price', 10.0, 50.0)
    ->orWhereBetween('product_stock', 100, 500)
    ->get();

// OR NOT BETWEEN - Exclude middle range
// WHERE price NOT BETWEEN 50 AND 100 OR stock NOT BETWEEN 10 AND 50
$rows = $this->query()
    ->whereNotBetween('product_price', 50.0, 100.0)
    ->orWhereNotBetween('product_stock', 10, 50)
    ->get();

// OR IN - Multiple category sets
// WHERE category_id IN (1, 2) OR status IN ('active', 'featured')
$rows = $this->query()
    ->whereIn('product_category_id', [1, 2])
    ->orWhereIn('product_status', ['active', 'featured'])
    ->get();

// OR NOT IN - Exclude multiple sets
// WHERE status NOT IN ('deleted', 'archived') OR category_id NOT IN (5, 6)
$rows = $this->query()
    ->whereNotIn('product_status', ['deleted', 'archived'])
    ->orWhereNotIn('product_category_id', [5, 6])
    ->get();

// OR IN with subquery - Active categories OR featured tags
$rows = $this->query()
    ->whereIn('product_category_id', function ($sub) {
        $sub->table('category')->select('category_id')->where('category_active', true);
    })
    ->orWhereIn('product_tag_id', function ($sub) {
        $sub->table('tag')->select('tag_id')->where('tag_featured', true);
    })
    ->get();

// OR EXISTS - Has inventory OR has pre-orders
$rows = $this->query()
    ->whereExists(function ($sub) {
        $sub->table('inventory')
            ->select('1')
            ->whereColumn('inventory.product_id', 'product.product_id')
            ->where('inventory.quantity', '>', 0);
    })
    ->orWhereExists(function ($sub) {
        $sub->table('pre_order')
            ->select('1')
            ->whereColumn('pre_order.product_id', 'product.product_id');
    })
    ->get();

// OR NOT EXISTS - No reviews OR no ratings
$rows = $this->query()
    ->whereNotExists(function ($sub) {
        $sub->table('review')->select('1')->whereColumn('review.product_id', 'product.product_id');
    })
    ->orWhereNotExists(function ($sub) {
        $sub->table('rating')->select('1')->whereColumn('rating.product_id', 'product.product_id');
    })
    ->get();

// OR RAW - Complex business logic
// WHERE margin > 20 OR (price * discount_multiplier) < cost
$rows = $this->query()
    ->whereRaw('(product_price - product_cost) / product_price > ?', [0.2])
    ->orWhereRaw('product_price * ? < product_cost', [0.8])
    ->get();

$this->query()
    ->leftJoin('discount', function (JoinClause $join) use ($today) {
        $join->on('product.product_id', 'discount.product_id')
             ->where('discount.active', true)
             ->where('discount.start_date', '<=', $today)
             ->whereNull('discount.deleted_at');
    })
    ->get();

use MethorZ\SwiftDb\Query\QueryLogger;

// Create with PSR-3 logger (optional)
$logger = new QueryLogger($psrLogger);

// Pass to repository/query builder
$repository = new ProductRepository($connection, $logger);

// After some operations...
$summary = $logger->getSummary();
// ['count' => 5, 'total_time_ms' => 12.5, 'avg_time_ms' => 2.5, 'slowest_ms' => 5.2]

// Find slow queries (> 100ms)
$slowQueries = $logger->getSlowQueries(0.1);

// Clear for next request
$logger->clear();

use MethorZ\SwiftDb\Cache\MappingCache;

// Configure with cache directory
$cache = new MappingCache('data/cache/database');

// First call: builds mapping via reflection, stores as PHP file
$mapping = $cache->getMapping(Product::class);

// Subsequent calls: loads from OPcache (fast!)
$mapping = $cache->getMapping(Product::class);

// Clear cache after entity changes (e.g., in deployment)
$cache->clear();