PHP code example of codemonster-ru / database

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

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

    

codemonster-ru / database example snippets

use Codemonster\Database\DatabaseManager;

$manager = new DatabaseManager([
    'default' => 'mysql', // name of the default connection
    'connections' => [
        'mysql' => [
            'driver'   => 'mysql',
            'host'     => '127.0.0.1',
            'port'     => 3306,
            'database' => 'test',
            'username' => 'root',
            'password' => '',
            'charset'  => 'utf8mb4',
        ],
    ],
]);

$db = $manager->connection(); // default connection

$manager = new DatabaseManager([
    'default' => 'mysql',
    'connections' => [
        'mysql' => [
            'driver'   => 'mysql',
            'host'     => '127.0.0.1',
            'port'     => 3306,
            'database' => 'app',
            'username' => 'root',
            'password' => '',
        ],
        'sqlite' => [
            'driver'   => 'sqlite',
            'database' => __DIR__ . '/database.sqlite',
        ],
    ],
]);

$mysql  = $manager->connection();          // default (mysql)
$sqlite = $manager->connection('sqlite');  // explicit connection

$users = $db->table('users')
    ->select('id', 'name', 'email')
    ->where('active', 1)
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

$rows = $db->table('users')
    ->select('users.name label', 'COUNT(*) total')
    ->groupBy('users.name')
    ->get();

$db->table('users')->insert([
    'name'  => 'Vasya',
    'email' => '[email protected]',
]);

$id = $db->table('ideas')->insertGetId([
    'title' => 'New idea',
]);

$db->table('users')
    ->where('id', 5)
    ->update([
        'active'     => 0,
        'updated_at' => date('Y-m-d H:i:s'),
    ]);

$db->table('sessions')
    ->where('user_id', 10)
    ->delete();

[$sql, $bindings] = $db->table('users')
    ->where('active', 1)
    ->toSql();

// $sql      = 'SELECT * FROM `users` WHERE `active` = ?'
// $bindings = [1]

$db->table('users')
    ->selectRaw('COUNT(*) as total')
    ->whereRaw('JSON_VALID(metadata)')
    ->orderByRaw('FIELD(status, "new", "approved", "archived")')
    ->get();

$db->table('orders')
    ->join('users', 'users.id', '=', 'orders.user_id')
    ->leftJoin('payments', function ($join) {
        $join->on('payments.order_id', '=', 'orders.id')
             ->where('payments.status', 'paid');
    })
    ->get();

$db->table('orders')
    ->selectRaw('status, COUNT(*) as total')
    ->groupBy('status')
    ->having('total', '>', 10)
    ->get();

$count = $db->table('users')->count();
$sum   = $db->table('orders')->sum('amount');
$avg   = $db->table('ratings')->avg('score');
$min   = $db->table('logs')->min('id');
$max   = $db->table('visits')->max('duration');

$exists = $db->table('users')
    ->where('email', '[email protected]')
    ->exists();

$email = $db->table('users')
    ->where('id', 1)
    ->value('email');

$names = $db->table('users')->pluck('name');
$pairs = $db->table('users')->pluck('email', 'id'); // [id => email]

// Aliases are supported:
// $db->table('users')->pluck('users.name label', 'users.id key');
// $db->table('users')->value('COUNT(*) total');

$pairs = $db->table('users')
    ->pluck('users.name label', 'users.id key'); // [id => name]

$total = $db->table('users')->value('COUNT(*) total');

$currentPage = 1;

$page = $db->table('posts')->simplePaginate(20, $currentPage);

// $page = [
//     'data'        => [...],
//     'per_page'    => 20,
//     'current_page'=> 1,
//     'next_page'   => 2,
//     'prev_page'   => null,
// ];

$db->table('users')
    ->setEmptyWhereInBehavior(\Codemonster\Database\Query\QueryBuilder::EMPTY_CONDITION_EXCEPTION)
    ->whereIn('id', []);

$db->table('users')
    ->setEmptyWhereNotInBehavior(\Codemonster\Database\Query\QueryBuilder::EMPTY_CONDITION_NONE)
    ->whereNotIn('id', []);

$db->transaction(function ($db) {
    $db->table('users')->insert([
        'name'  => 'New user',
        'email' => '[email protected]',
    ]);

    $db->table('logs')->insert([
        'message' => 'User created',
    ]);
});

db();                 // returns default ConnectionInterface
db('sqlite');         // specific connection
schema();             // schema builder for default connection
transaction(fn() =>   // convenience wrapper
    db()->table('logs')->insert(['message' => 'ok'])
);

use Codemonster\Database\Schema\Blueprint;

// You can also use Schema::forConnection($db) if you need a schema instance directly.
$db->schema()->create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->boolean('active')->default(1);
    $table->timestamps();
});

$db->schema()->table('users', function (Blueprint $table) {
    $table->string('avatar')->nullable();
    $table->integer('age')->default(0);
});

$db->schema()->drop('users');

// or:
$db->schema()->dropIfExists('users');

use Codemonster\Database\Migrations\Migration;
use Codemonster\Database\Schema\Blueprint;

return new class extends Migration {
    public function up(): void
    {
        schema()->create('posts', function (Blueprint $table) {
            $table->id();
            $table->string('title');
        });
    }

    public function down(): void
    {
        schema()->drop('posts');
    }
};

use Codemonster\Database\Seeders\Seeder;

return new class extends Seeder {
    public function run(): void
    {
        db()->table('users')->insert([
            'name' => 'Admin',
            'email' => '[email protected]',
        ]);
    }
};

use Codemonster\Database\ORM\Model;

class User extends Model
{
    protected string $table = 'users';

    protected array $fillable = ['name', 'email', 'password'];

    protected array $hidden = ['password'];

    protected array $casts = [
        'created_at' => 'datetime',
    ];
}

$user = User::find(1);

$active = User::query()
    ->where('active', 1)
    ->orderBy('id')
    ->get();

User::create([
    'name' => 'John',
    'email' => '[email protected]',
]);

$user->email = '[email protected]';
$user->save();

$user->delete();

class User extends Model {
    public function posts() {
        return $this->hasMany(Post::class);
    }
}

class Post extends Model {
    public function author() {
        return $this->belongsTo(User::class, 'user_id');
    }
}

$user->posts;

$user->load('posts');

use Codemonster\Database\Traits\SoftDeletes;

class User extends Model {
    use SoftDeletes;
}

$kernel->getPathResolver()->addPath('/path/to/migrations');

$kernel->getSeedPathResolver()->addPath('/path/to/seeds');