PHP code example of johnnyjoy / uda

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

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

    

johnnyjoy / uda example snippets

declare(strict_types=1);

namespace App\Repository;

use UDA\Link;

final class UserRepository
{
    use Link;

    protected static string $connection = 'app';

    public function findName(int $id): ?string
    {
        $name = $this->value(
            'SELECT name FROM users WHERE id = :id',
            ['id' => $id],
            ['users']
        );

        return is_string($name) ? $name : null;
    }
}

use UDA\Database;

$db = Database::connectDefault();

$user = $db->row(
    'SELECT id, name FROM users WHERE id = :id',
    ['id' => 42],
    ['users']
);

$names = $db->values(
    'SELECT name FROM users WHERE active = :active ORDER BY name',
    ['active' => 1],
    ['users']
);

// Large result sets — callback per row, not one giant array
$db->each(
    'SELECT id, payload FROM events WHERE processed = :p',
    ['p' => 0],
    function (array $row): void {
        // process one row
    },
    ['events']
);

$affected = $db->exec(
    'UPDATE users SET name = :name WHERE id = :id',
    ['name' => 'Ada', 'id' => 42],
    ['users']
);

$inserted = $db->returning(
    'INSERT INTO users (name) VALUES (:name) RETURNING id',
    ['name' => 'Ada'],
    ['users']
);

$db->transaction(function (Database $db): void {
    $db->exec(
        'INSERT INTO users (id, name) VALUES (:id, :name)',
        ['id' => 1, 'name' => 'Ada'],
        ['users']
    );
});

// Read terminator straight off WHERE (proxy end)
$name = $db->select()->from('users')->where('id', 42)->value();

// ORDER BY after WHERE — explicit end
$rows = $db->select('id', 'name')
    ->from('users')
    ->where('active', 1)
    ->end()
    ->orderBy('name')
    ->rows();

// UPDATE — end before exec
$db->update()
    ->table('users')
    ->set('name', 'Ada')
    ->where('id', 42)
    ->end()
    ->exec();

$name = $db->select()
    ->from('users')
    ->where('id', 42)
    ->value();

$active = $db->select('id', 'name')
    ->from('users')
    ->where('active', 1)
    ->orderBy('name')
    ->limit(100)
    ->rows();

// EXISTS — pass a compiled subquery or Select (then ->end() back to Select)
$activeOnly = $db->select('1')->from('managers')->where('active', 1)->end();
$withManagers = $db->select('e.id')
    ->from('employees e')
    ->whereExists($activeOnly)
    ->end()
    ->rows();

// Dynamic filters — each where() returns a clone
$q = $db->select()->from('employees');
if ($departmentId !== null) {
    $q = $q->where('department_id', $departmentId);
}
$rows = $q->rows();

$db->insert()
    ->into('users')
    ->values(['id' => 1, 'name' => 'Ada'])
    ->exec();

$db->update()
    ->table('users')
    ->set('name', 'Ada')
    ->where('id', 42)
    ->end()
    ->exec();

// INSERT … SELECT (copy rows from a query)
$db->insert()
    ->into('users_archive')
    ->columns('id', 'name', 'archived_at')
    ->select(
        $db->select('id', 'name')->from('users')->where('deleted', 1)->end()
    )
    ->exec();

// Postgres-style RETURNING on builders (where supported)
$newId = $db->insert()
    ->into('users')
    ->set('name', 'Ada')
    ->returning('id')
    ->value();

$db->upsert()
    ->into('users')
    ->values(['id' => 42, 'name' => 'Ada', 'email' => '[email protected]'])
    ->key(['id'])
    ->update(['name', 'email'])
    ->exec();

$db->upsert()
    ->into('events')
    ->rows([
        ['external_id' => 'e1', 'payload' => '{}'],
        ['external_id' => 'e2', 'payload' => '{}'],
    ])
    ->key(['external_id'])
    ->doNothing()
    ->exec();

$db->update()
    ->table('import_staging')
    ->set('processed', 1)
    ->unsafe()
    ->exec();

$sql = 'SELECT id, name FROM users WHERE department_id = :dept AND active = :active';

$east = $db->rows($sql, ['dept' => 10, 'active' => 1], ['users']);
$west = $db->rows($sql, ['dept' => 20, 'active' => 1], ['users']);

use UDA\Query\Sql;

$template = Sql::of('SELECT id, name FROM users WHERE id = :id', [], ['users']);

$alice = $db->row($template, ['id' => 1], ['users']);
$bob   = $db->row($template, ['id' => 2], ['users']);

$list = $db->select('id', 'name', 'department_id')->from('users');

$east = $list->where('department_id', 10)->where('active', 1)->rows();
$west = $list->where('department_id', 20)->where('active', 1)->rows();

$activeUsers = $db->select('id')
    ->from('users')
    ->where('active', 1)
    ->toSql();

$rows = $db->select('e.id', 'e.name', 'u.id AS manager_id')
    ->from('employees e')
    ->joinSub($activeUsers, 'u', 'u.id = e.manager_id')
    ->rows();

$report = $db->with('active', $activeUsers)
    ->select('*')
    ->from('active')
    ->rows();

$sql = $db->select('id', 'name')->from('users')->where('active', 1)->toSql();

$primary = Database::connectNamed('app')->rows($sql);
$replica = Database::connectNamed('replica')->rows($sql);

[$inSql, $inParams] = $db->inList($ids, 'id');
$sql = 'SELECT * FROM users WHERE id ' . $inSql;
$rows = $db->rows($sql, $inParams, ['users']);

$statusFilter = 'o.status = :status';

$open = $db->select()->from('orders o')
    ->whereRaw($statusFilter, ['status' => 'open'])
    ->rows();

$shipped = $db->select()->from('orders o')
    ->whereRaw($statusFilter, ['status' => 'shipped'])
    ->rows();

$db->insert()
    ->into('employees')
    ->rows([
        ['employee_no' => 'E100', 'first_name' => 'Ann', 'last_name' => 'One'],
        ['employee_no' => 'E101', 'first_name' => 'Bob', 'last_name' => 'Two'],
    ])
    ->exec();