PHP code example of jardissupport / dbquery

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

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

    

jardissupport / dbquery example snippets

use JardisSupport\DbQuery\DbQuery;

$query = (new DbQuery())
    ->select('id, name, email')
    ->from('users')
    ->where('status')->equals('active')
    ->and('created_at')->greaterEquals('2024-01-01')
    ->orderBy('name')
    ->limit(50);

// Generate prepared SQL for MySQL
$prepared = $query->sql('mysql', prepared: true);
// $prepared->sql()      → "SELECT `id`, `name`, `email` FROM `users` WHERE `status` = ? AND `created_at` >= ? ORDER BY `name` ASC LIMIT 50"
// $prepared->bindings() → ['active', '2024-01-01']

use JardisSupport\DbQuery\DbQuery;
use JardisSupport\DbQuery\DbInsert;

// CTE with recursive traversal
$cte = (new DbQuery())
    ->select('id, parent_id, name, 0 AS depth')
    ->from('categories')
    ->where('parent_id')->isNull()
    ->union(
        (new DbQuery())
            ->select('c.id, c.parent_id, c.name, r.depth + 1')
            ->from('categories', 'c')
            ->innerJoin('category_tree', 'c.parent_id = r.id', 'r')
    );

$query = (new DbQuery())
    ->withRecursive('category_tree', $cte)
    ->select('id, name, depth')
    ->from('category_tree')
    ->orderBy('depth')
    ->orderBy('name');

// Window function for ranking
$ranked = (new DbQuery())
    ->select('id, customer_id, total')
    ->selectWindow('ROW_NUMBER', 'row_num')
        ->over()
        ->partitionBy('customer_id')
        ->orderBy('total', 'DESC')
        ->end()
    ->from('orders');

// JSON column condition (PostgreSQL)
$query = (new DbQuery())
    ->select('id, payload')
    ->from('events')
    ->whereJson('payload')->path('$.type')->equals('order.created')
    ->andJson('payload')->path('$.amount')->greaterEquals(100);

// INSERT with conflict resolution
$insert = (new DbInsert())
    ->into('products')
    ->fields('sku', 'name', 'price')
    ->values('ABC-001', 'Widget', 9.99)
    ->onConflict('sku')                    // conflict target (used by PostgreSQL/SQLite)
    ->onDuplicateKeyUpdate('name', 'Widget')
    ->onDuplicateKeyUpdate('price', 9.99);

$sql = $insert->sql('mysql', prepared: true);

use JardisSupport\DbQuery\Data\LikePattern;

$query->where('name')->like(LikePattern::contains('50%'));    // LIKE ? ESCAPE '!'  with '%50!%%'
$query->where('name')->like(LikePattern::startsWith('a_b'));  // 'a!_b%'
$query->where('name')->like(LikePattern::endsWith('x'));      // '%x'
$query->where('name')->like(LikePattern::literal('a_b'));     // 'a!_b' (no wildcards at all)
$query->where('name')->notLike(LikePattern::contains('spam'));