PHP code example of ascetic-soft / rowcast

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

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

    

ascetic-soft / rowcast example snippets

use AsceticSoft\Rowcast\Connection;
use AsceticSoft\Rowcast\DataMapper;

class UserDto
{
    public int $id;
    public string $email;
    public bool $isActive;
}

$connection = Connection::create('sqlite::memory:');
$mapper = new DataMapper($connection);

$user = new UserDto();
$user->email = '[email protected]';
$user->isActive = true;

$mapper->insert('users', $user);
$found = $mapper->findOne(UserDto::class, ['email' => $user->email]);

use AsceticSoft\Rowcast\Mapping;

$mapping = Mapping::auto(UserDto::class, 'custom_users')
    ->column('usr_email', 'email')
    ->ignore('internalNote');

$user = $mapper->findOne($mapping, ['id' => 1]);

$mapping = Mapping::explicit(UserDto::class, 'custom_users')
    ->column('id', 'id')
    ->column('usr_email', 'email');

use AsceticSoft\Rowcast\Connection;

// From DSN
$connection = Connection::create(
    dsn: 'mysql:host=localhost;dbname=app',
    username: 'root',
    password: 'secret',
    nestTransactions: true,
);

// From existing PDO
$pdo = new \PDO('sqlite::memory:');
$connection = new Connection($pdo, nestTransactions: true);

$stmt = $connection->executeQuery('SELECT * FROM users WHERE id = ?', [1]);
$affected = $connection->executeStatement('UPDATE users SET email = ? WHERE id = ?', ['[email protected]', 1]);
$rows = $connection->fetchAllAssociative('SELECT * FROM users');
$row = $connection->fetchAssociative('SELECT * FROM users WHERE id = ?', [1]);
$count = $connection->fetchOne('SELECT COUNT(*) FROM users');

$connection->onBeforeQuery(function (string $sql, array $params): void {
    // log or inspect SQL before execution
});

$connection->onAfterQuery(function (string $sql, array $params, float $duration, ?\Throwable $exception): void {
    // $exception is null for successful queries
});

$connection->transactional(function (Connection $conn) {
    $conn->executeStatement('INSERT INTO users (email) VALUES (?)', ['[email protected]']);
});

$dto = new UserDto();
$dto->email = '[email protected]';
$dto->isActive = true;

$mapper->insert('users', $dto);
$one = $mapper->findOne(UserDto::class, ['email' => $dto->email]);

$one->isActive = false;
$mapper->update('users', $one, ['id' => $one->id]);
$mapper->delete('users', ['id' => $one->id]);

$mapper->save('users', $dto, 'id');

$affected = $mapper->upsert('users', $dto, 'email');

$mapper->batchInsert('users', [$dto1, $dto2, $dto3]);
$mapper->batchUpsert('users', [$dto1, $dto2, $dto3], ['id']);
$mapper->batchUpdate('users', [$dto1, $dto2, $dto3], ['id']);

// Optional override for chunk sizing by bind parameter limit
$mapper->batchInsert('users', [$dto1, $dto2, $dto3], 500);
$mapper->batchUpsert('users', [$dto1, $dto2, $dto3], ['id'], 500);

$users = $mapper->findAll(UserDto::class, where: [
    'deleted_at' => null,
    '$or' => [
        ['status' => ['active', 'pending']],
        ['role' => 'admin'],
    ],
    'age >=' => 18,
]);

use AsceticSoft\Rowcast\DataMapper;
use AsceticSoft\Rowcast\TypeConverter\TypeConverterInterface;
use AsceticSoft\Rowcast\TypeConverter\TypeConverterRegistry;

final class UuidConverter implements TypeConverterInterface
{
    public function supports(string $phpType): bool
    {
        return $phpType === Uuid::class;
    }

    public function toPhp(mixed $value, string $phpType): mixed
    {
        return new Uuid((string) $value);
    }

    public function toDb(mixed $value): mixed
    {
        return (string) $value;
    }
}

$converters = TypeConverterRegistry::defaults()->add(new UuidConverter());
$mapper = new DataMapper($connection, typeConverter: $converters);

use AsceticSoft\Rowcast\DataMapper;
use AsceticSoft\Rowcast\NameConverter\NameConverterInterface;

final class PrefixedConverter implements NameConverterInterface
{
    public function toPropertyName(string $columnName): string
    {
        return lcfirst(str_replace('usr_', '', $columnName));
    }

    public function toColumnName(string $propertyName): string
    {
        return 'usr_' . $propertyName;
    }
}

$mapper = new DataMapper($connection, nameConverter: new PrefixedConverter());

$rows = $connection->createQueryBuilder()
    ->select('u.id', 'u.email')
    ->from('users', 'u')
    ->where('u.is_active = :active')
    ->orderBy('u.id', 'DESC')
    ->setOffset(20)
    ->setLimit(10)
    ->setParameter('active', 1)
    ->fetchAllAssociative();

$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->where(['email' => '[email protected]', 'is_active' => 1])
    ->fetchAllAssociative();
// SQL: SELECT * FROM users WHERE email = :w_email AND is_active = :w_is_active

// IS NULL / IS NOT NULL
->where(['deleted_at' => null])        // deleted_at IS NULL
->where(['deleted_at !=' => null])     // deleted_at IS NOT NULL

// IN / NOT IN
->where(['status' => ['active', 'pending']])     // status IN (...)
->where(['status !=' => ['banned']])             // status NOT IN (...)
->where(['status IN' => ['active']])             // explicit IN
->where(['status NOT IN' => ['banned']])         // explicit NOT IN

// BackedEnum in WHERE (direct QueryBuilder usage)
->where(['status' => UserStatus::Active])                               // status = :w_status, parameter value: 'active'
->where(['status' => [UserStatus::Active, UserStatus::Inactive]])       // status IN (...), parameters: 'active', 'inactive'

// Comparison operators
->where(['age >' => 18, 'age <=' => 65, 'score !=' => 0])

// LIKE / ILIKE / NOT LIKE / NOT ILIKE
->where(['name LIKE' => '%alice%'])
->where(['name ILIKE' => '%alice%'])             // useful for PostgreSQL
->where(['name NOT LIKE' => '%bot%'])
->where(['name NOT ILIKE' => '%bot%'])           // PostgreSQL only

// BETWEEN
->where(['age BETWEEN' => [18, 65]])

// (status = 'active' AND age > 18) OR (role = 'admin')
$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->whereOr(
        ['status' => 'active', 'age >' => 18],
        ['role' => 'admin'],
    )
    ->fetchAllAssociative();

// deleted_at IS NULL AND ((status = 'active') OR (role = 'admin'))
$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->where(['deleted_at' => null])
    ->andWhereOr(['status' => 'active'], ['role' => 'admin'])
    ->fetchAllAssociative();

$rows = $connection->createQueryBuilder()
    ->select('*')
    ->from('users')
    ->where([
        'age >' => 18,
        '$or' => [
            ['status' => 'active'],
            ['$and' => [
                ['role' => 'admin'],
                ['verified' => true],
            ]],
        ],
    ])
    ->fetchAllAssociative();

$connection->createQueryBuilder()
    ->insert('users')
    ->values(['email' => ':email', 'is_active' => ':is_active'])
    ->setParameter('email', '[email protected]')
    ->setParameter('is_active', 1)
    ->executeStatement();

$connection->createQueryBuilder()
    ->update('users')
    ->values(['is_active' => ':is_active'])
    ->where('id = :id')
    ->setParameter('is_active', 0)
    ->setParameter('id', 1)
    ->executeStatement();

$connection->createQueryBuilder()
    ->delete('users')
    ->where('id = :id')
    ->setParameter('id', 1)
    ->executeStatement();

$sql = $connection->createQueryBuilder()
    ->upsert('users')
    ->values(['email' => ':email', 'name' => ':name'])
    ->onConflict('email')
    ->doUpdateSet(['name'])
    ->getSQL();