PHP code example of swew / db

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

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

    

swew / db example snippets

use Swew\Db\ModelConfig;

// PDO connection init
ModelConfig::init($dsn, $username, $password);

// Migration file
use Swew\Db\{Migrate,Migrator};

Migrate::up(function (Migrator $table) {
    $table->tableCreate('users');
    $table->id();
    $table->string('name')->unique();
    $table->string('login', 64)->unique()->index();
    $table->string('password', 64)->default('123456');
    $table->text('description')->fulltext();
    $table->integer('rating')->nullable();

    // $table->softDeletable(); // If need
    $table->timestamps();
});

Migrate::down(function (Migrator $table) {
    $table->tableDrop('users');
});

use Swew\Db\{Migrate,ModelConfig};

// path to autoload file
sqlite');
ModelConfig::setPDO($pdo);

// "**" - is alias for sub folders
$filePattern = __DIR__ . '/migrations/**.php';
// Run "UP" migrations
$isUpMigration = true;

Migrate::run($filePattern, $isUpMigration);

use Swew\Db\Model;

class UserModel extends Model {
    // acceptable fields, should be used with default values, so there are no errors in php 8.2
    public ?int   $id = null;
    public string $login = '';
    public string $name = '';
    public string $password = '';
    public int    $rating = 0;

    // Table name [al] [default: false]
    protected function hasTimestamp(): bool {
        return true;
    }


    protected function cast(): array {
        return [
            'to' => [
                'password' => fn ($str) => password_hash($str, PASSWORD_BCRYPT),
            ],
            'from' => [
                'created_at' => fn(int|string|null $timeStamp) => $timeStamp ? date('Y.m.d - H:i:s', (int)$timeStamp) : '',
                'updated_at' => fn(int|string|null $timeStamp) => $timeStamp ? date('Y.m.d - H:i:s', (int)$timeStamp) : '',
            ],
        ];
    }

    protected function mapTable(): array
    {
        return [
            // 'TABLE' => $this, // default value, fixed
            'T1' => $this,
            'T2' => CommentModel::class,
            'T3' => 'table_name',
        ];
    }

    // SQL Query
    const MOST_POPULAR_USER = 'SELECT id, login, name FROM [TABLE] WHERE rating >= 9';
    const FIND_BY_NAME = 'SELECT id, login, name FROM [TABLE] WHERE name = ?';
    const UPDATE_NAME_BY_ID = 'UPDATE [TABLE] SET name = ? WHERE id = ?';
    const UPDATE_NAME = 'UPDATE [TABLE] SET name = ?';
    const INSERT_LOGIN_NAME = 'INSERT INTO [TABLE] (login, name) VALUES (:login, :name)';
    const JOIN_COMMENT = 'SELECT [T1].name, [T2].comment FROM [T1] JOIN [T2] ON [T1].id=[T2].user_id';
}

// const MOST_POPULAR_USER = 'SELECT id, login, name FROM [TABLE] WHERE rating >= 9';
// const FIND_BY_NAME = 'SELECT id, login, name FROM [TABLE] WHERE name = ?';
UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->get(); // array list
UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->getFirst(); // array data with first item with limit
UserModel::vm()->query(UserModel::MOST_POPULAR_USER)->getFirst();

UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->getFirstItem(); // UserModel
UserModel::vm()->query(UserModel::FIND_BY_NAME, 'Jack')->getItems(); // UserModel[]

UserModel::vm()->query(UserModel::MOST_POPULAR_USER)->getValue(); // First value from first item

// Mapped values
UserModel::vm()->query(UserModel::MOST_POPULAR_USER)->getMap(
    fn ($v) => $v['login']
);

// const INSERT_LOGIN_NAME = 'INSERT INTO [TABLE] (login, name) VALUES (:login, :name)';
$user = new UserModel();

$user->login = 'Mr 007';
$user->name = 'James';

$lastId = UserModel::vm()->query(UserModel::INSERT_LOGIN_NAME)
    ->setData($user)
    ->exec()
    ->id();

UserModel::vm()
    ->query(UserModel::INSERT_LOGIN_NAME, ['login' => 'MyLogin', 'name' => 'My Name'])
    ->exec();

UserModel::vm()
    ->insert(['login' => 'MyLogin', 'name' => 'My Name']);

UserModel::vm()
    ->insertMany([
      ['login' => 'MyLogin_1', 'name' => 'My Name 1'],
      ['login' => 'MyLogin_2', 'name' => 'My Name 2'],
    ]);

// const UPDATE_NAME = 'UPDATE [TABLE] SET name = ?';
UserModel::vm()
    ->query(UserModel::UPDATE_NAME, 'Garry')
    ->where('id', 1)
    ->exec();

UserModel::vm()
    ->query(UserModel::UPDATE_NAME)
    ->where('id', 1)
    ->exec('Garry');

UserModel::vm()
    ->query(UserModel::UPDATE_NAME)
    ->where('id', 1)
    ->execMany(['Garry']);

$count = UserModel::vm()
    ->count()
    ->where('id', '>', 2)
    ->getValue();

// const JOIN_COMMENT = 'SELECT [T1].name, [T2].comment FROM [T1] JOIN [T2] ON [T1].id=[T2].user_id';
UserModel::vm()->query(UserModel::JOIN_COMMENT)->get();

// Paginate

// const JOIN_COMMENT = 'SELECT [T1].name, [T2].comment FROM [T1] JOIN [T2] ON [T1].id=[T2].user_id';
UserModel::vm()->query(UserModel::JOIN_COMMENT)->getPages($pageNumber = 1, $perPage = 10);
UserModel::vm()->query(UserModel::JOIN_COMMENT)->getPagesWithCount();

// Result
[
    'data' => $items, // array
    'page' => 1,
    'next' => 2,
    'prev' => 0,
    // 'count' => 10, // if use ->getPageWithCount()
];

// cursor pagination

// const JOIN_COMMENT = 'SELECT [T1].name, [T2].comment FROM [T1] JOIN [T2] ON [T1].id=[T2].user_id';
UserModel::vm()->query(UserModel::JOIN_COMMENT)->getCursorPages($id = 11, $pageNumber = 2, $perPage = 10);

// Result
[
    'data' => $items, // array
    'next_id' => 21,
    'prev_id' => 1,
    'page' => 1,
    'next' => 2,
    'prev' => 0,
];

// const UPDATE_NAME = 'UPDATE [TABLE] SET name = ?';
$isOk = UserModel::transaction(function () {
    UserModel::vm()->query(UserModel::UPDATE_NAME, 'Leo')->where('id', 1)->exec();
    UserModel::vm()->query(UserModel::UPDATE_NAME, 'Don')->where('id', 2)->exec();
    UserModel::vm()->query(UserModel::UPDATE_NAME, 'Mike')->where('id', 3)->exec();
});

UserModel::vm()
    ->select('name', 'rating')
    ->where('rating', '>', 4)
    ->getFirst();
    // [
    //     'name' => 'Leo',
    //     'rating' => 5,
    // ],

UserModel::vm()
    ->max('rating')
    ->getValue('rating'); // 5

UserModel::vm()
    ->min('rating')
    ->getValue(); // 1

$user = new UserModel();
$user->name = 'Leo';
$user->login = 'Ninja';
$user->password = 'secret';

$user->save();

UserModel::vm()->save([
    'name' => 'Don',
    'login' => 'Ninja',
    'password' => 'secret',
]);

$user = new UserModel();
$user->name = 'Master Splinter';
$user->email = '[email protected]';
// OR
// $user = [
//     'name' => 'Master Splinter',
//     'email' => '[email protected]',
// ];
UserModel::vm()->where('id', 1)->update($user);

UserModel::vm()->where('id', 1)->delete();

UserModel::vm()->where('id', 1)->softDelete();

UserModel::vm()->select()->where('id', 1)->exec();
UserModel::vm()->select()->where('id', '=', 1)->exec();
UserModel::vm()->select()->where('id', '!=', 1)->exec();
UserModel::vm()->select()->where('id', '>', 1)->exec();
UserModel::vm()->select()->where('id', '<', 1)->exec();

UserModel::vm()->select()->orWhere('id', 1)->exec();
UserModel::vm()->select()->orWhere('id', '=', 1)->exec();
UserModel::vm()->select()->orWhere('id', '!=', 1)->exec();
UserModel::vm()->select()->orWhere('id', '>', 1)->exec();
UserModel::vm()->select()->orWhere('id', '<', 1)->exec();

UserModel::vm()->select()->whereIn('id', [1, 2, 3])->exec();

UserModel::vm()->select()->whereNotIn('id', [1, 2, 3])->exec();

UserModel::vm()->select()
    ->offset(2)
    ->limit(1)
    ->get();

UserModel::vm()
    ->select('name')
    ->where('id', 3)
    ->cache(3600) // seconds
    ->getFirst();