PHP code example of mathsgod / light-db

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

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

    

mathsgod / light-db example snippets

use Light\Db\Model;

class User extends Model
{
    // Uses class name as table name by default
    // Customize with: protected static $_table = 'custom_table_name';
}

class Post extends Model
{
    protected static $_table = 'posts';
}

// Method 1: Create + Save
$user = User::Create([
    'name' => 'Raymond Chong',
    'email' => '[email protected]',
    'age' => 30
]);
$user->save();

// Method 2: Direct property assignment
$user = User::Create();
$user->name = 'John Doe';
$user->email = '[email protected]';
$user->save();

// Query by primary key
$user = User::Get(1);

// Basic queries
$users = User::Query(['status' => 'active'])->toArray();

// Complex queries
$activeUsers = User::Query()
    ->filters([
        'age' => ['gte' => 18, 'lte' => 65],
        'status' => ['in' => ['active', 'premium']],
        'email' => ['contains' => '@gmail.com']
    ])
    ->sort('created_at:desc')
    ->toArray();

// Get first record
$firstUser = User::Query(['status' => 'active'])->first();

// Single record update
$user = User::Get(1);
$user->name = 'Updated Name';
$user->email = '[email protected]';
$user->save();

// Batch update
$affected = User::Query(['status' => 'inactive'])
    ->update(['status' => 'archived']);

// Single record deletion
$user = User::Get(1);
$user->delete();

// Batch deletion
$deleted = User::Query(['status' => 'spam'])
    ->delete();

$query = User::Query()->filters([
    'age' => ['eq' => 25],           // Equal to
    'score' => ['gt' => 80],         // Greater than
    'salary' => ['gte' => 50000],    // Greater than or equal
    'rating' => ['lt' => 5],         // Less than
    'points' => ['lte' => 100],      // Less than or equal
    'status' => ['in' => ['active', 'premium']], // In array
    'name' => ['contains' => 'john'],  // Contains pattern
    'category' => ['ne' => 'spam']   // Not equal
]);

$users = User::Query()
    ->sort('created_at:desc,name:asc')  // Multi-field sorting
    ->limit(10)                         // Limit results
    ->offset(20)                        // Offset
    ->toArray();

use Light\Db\Model;

class User extends Model {}

// WHERE (age >= 18 OR name = 'Peter')
$users = User::Query()->filters([
    '_or' => [
        ['age' => ['gte' => 18]],
        ['name' => 'Peter']
    ]
])->toArray();

// WHERE status = 'active' AND (age >= 30 OR name = 'Test User 1')
$users = User::Query()
    ->where(['status' => 'active'])
    ->filters([
        '_or' => [
            ['age' => ['gte' => 30]],
            ['name' => 'Test User 1']
        ]
    ])
    ->toArray();

// WHERE (age > 28 AND status = 'active')
//    OR (name = 'Test User 3' AND status = 'inactive')
$users = User::Query()->filters([
    '_or' => [
        [
            '_and' => [
                ['age' => ['gt' => 28]],
                ['status' => 'active']
            ]
        ],
        [
            '_and' => [
                ['name' => 'Test User 3'],
                ['status' => 'inactive']
            ]
        ]
    ]
])->toArray();

use Laminas\Db\Sql\Predicate\PredicateSet;

// WHERE (age >= 30 OR name = 'Test User 1')
$users = User::Query()
    ->where(['age >= ?' => 30], PredicateSet::OP_OR)
    ->where(['name = ?' => 'Test User 1'], PredicateSet::OP_OR)
    ->toArray();

$userCount = User::Query()->count();
$avgAge = User::Query()->avg('age');
$totalSalary = User::Query()->sum('salary');
$minAge = User::Query()->min('age');
$maxAge = User::Query()->max('age');

$query = User::Query(['status' => 'active']);
$paginator = $query->getPaginator();

// Set items per page
$paginator->setItemCountPerPage(20);
$paginator->setCurrentPageNumber(1);

// Get current page data
$currentItems = $paginator->getCurrentItems();
$totalItems = $paginator->getTotalItemCount();
$totalPages = $paginator->getPages()->pageCount;

// Create record with JSON data
$user = User::Create([
    'name' => 'John',
    'profile' => [
        'avatar' => 'avatar.jpg',
        'settings' => [
            'theme' => 'dark',
            'notifications' => true
        ],
        'tags' => ['developer', 'php', 'mysql']
    ]
]);
$user->save();

// Read JSON data
$user = User::Get(1);
echo $user->profile['settings']['theme']; // 'dark'

// Update JSON data
$user->profile['settings']['theme'] = 'light';
$user->profile['tags'][] = 'javascript';
$user->save();

// Assuming UserList model with user_id column
$user = User::Get(1);

// Get related UserList query object
$userLists = $user->UserList;  // Returns Query object

// Further querying
$activeLists = $user->UserList
    ->filters(['status' => 'active'])
    ->sort('created_at:desc')
    ->toArray();

use Light\Db\Model;

class Schedule extends Model
{
    protected static function filterDefinitions(): array
    {
        return [
            'Letter' => function ($value) {
                return "letter_id IN (SELECT letter_id FROM letter WHERE subject = " . $value . ")";
            },
        ];
    }

    protected static function orderDefinitions(): array
    {
        return [
            'recent' => fn($dir) => "created_at $dir",
        ];
    }
}

class Letter extends Schedule
{
    protected static function filterDefinitions(): array
    {
        return array_merge(parent::filterDefinitions(), [
            'Tag' => fn($v) => "letter_id IN (SELECT letter_id FROM letter_tag WHERE tag = $v)",
        ]);
    }
}

'Schedule' => Schedule::filterDefinitions()['Letter'],

$users = User::Query(['status' => 'active']);

// Map operation
$names = $users->map(fn($user) => $user->name)->toArray();

// Filter operation
$premiumUsers = $users->filter(fn($user) => $user->type === 'premium');

// Method chaining
$emailList = User::Query()
    ->filters(['status' => 'active'])
    ->map(fn($user) => $user->email)
    ->toArray();

// Legacy: register a sort ad-hoc from anywhere (still supported)
User::RegisterOrder('popular', function($query) {
    return $query->order(['score DESC', 'views DESC']);
});

// Use custom sorting
$popularUsers = User::Query()->sort('popular')->toArray();

// cursor() returns a Laravel LazyCollection — perfect for streaming large result sets
foreach (User::Query()->cursor() as $user) {
    echo $user->name;
}

// execute() returns a regular Collection eagerly materialized
$users = User::Query()->execute()->toArray();

// The framework already binds values from where() conditions automatically,
// so this is normally a no-op. It exists for symmetry and advanced cases
// (e.g. when the Select contains Expression objects with named placeholders).
User::Query(['status' => 'active'])->execute([
    'minAge' => 18,
])->toArray();