PHP code example of abdulelahragih / querybuilder

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

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

    

abdulelahragih / querybuilder example snippets

use Abdulelahragih\QueryBuilder\QueryBuilder;

$pdo = /* your PDO connection */;
$qb = new QueryBuilder($pdo);

$rows = $qb->table('users')
    ->select('id', 'username', 'phone_number', 'gender')
    ->where('role_id', '=', 1)
    ->join('images', 'images.user_id', '=', 'users.id')
    ->orderBy('id', 'DESC')
    ->limit(10)
    ->get(); // returns Collection

use Abdulelahragih\QueryBuilder\DB;
use Abdulelahragih\QueryBuilder\DBSingleton;

// Instance wrapper
$db = new DB($pdo);
$users = $db->table('users')->select('id')->get();

// Static facade
DBSingleton::init($pdo);
$firstId = DBSingleton::table('users')->first('id');

$p = $qb->table('users')
    ->orderBy('id', 'DESC')
    ->paginate($page, $perPage, pageName: 'page');
// $p is LengthAwarePaginator (items + total, pages, prev/next)

$p = $qb->table('users')->simplePaginate($page, $perPage);
// $p is SimplePaginator (items + prev/next)

$qb->table('users')->insert(['id' => 100, 'name' => 'John']);
$qb->table('users')->insert([
    ['id' => 100, 'name' => 'John'],
    ['id' => 101, 'name' => 'Jane'],
]);

use Abdulelahragih\QueryBuilder\Grammar\Expression;

// Update non-unique columns automatically when conflicts occur
$qb->table('users')->upsert(
    ['id' => 100, 'name' => 'John', 'age' => 26],
    uniqueBy: ['id'],
);

// Explicit assignments (MySQL uses VALUES(), Postgres uses EXCLUDED)
$qb->table('users')->upsert(
    ['id' => 100, 'name' => 'John', 'age' => 26],
    uniqueBy: ['id'],
    updateOnDuplicate: [
        'name',                   // infer from column name
        'updated_at' => Expression::make('NOW()'), // raw expression
    ],
);

$qb->table('users')->insertOrIgnore(['id' => 100, 'name' => 'John'], uniqueColumns: ['id']);

$id = $qb->table('users')->insertGetId(['name' => 'John']);
// Optionally specify id column name
$id = $qb->table('users')->insertGetId(['name' => 'Jane'], 'user_id');

$updated = $qb->table('users')->where('id', '=', 1)->update(['name' => 'Sam']);
$deleted = $qb->table('users')->whereIn('id', [1, 2])->delete();

// Basic where and multiple conditions (AND)
$qb->table('users')
   ->where('role_id', '=', 1)
   ->where('status', '=', 'active')
   ->toSql();

// OR conditions
$qb->table('users')
   ->where('role_id', '=', 1)
   ->orWhere('role_id', '=', 2)
   ->toSql();

// Nested groups
$qb->table('users')
   ->where('id', '=', 1)
   ->orWhere(function ($w) {
       $w->where('id', '=', 2)
         ->where(function ($inner) {
             $inner->where('name', '=', 'Sam')
                   ->orWhere('name', '=', 'John');
         });
   })
   ->toSql();

// IN / NOT IN
$qb->table('users')
   ->whereIn('id', [1, 2, 3])
   ->whereNotIn('status', ['banned'])
   ->toSql();

// LIKE / NOT LIKE
$qb->table('users')
   ->whereLike('name', '%Sam%')
   ->whereNotLike('email', '%@spam.com')
   ->toSql();

// NULL / NOT NULL
$qb->table('users')
   ->whereNull('deleted_at')
   ->whereNotNull('email')
   ->toSql();

// BETWEEN / NOT BETWEEN
$qb->table('orders')
   ->whereBetween('amount', 10, 100)
   ->whereNotBetween('discount', 20, 30)
   ->toSql();

// Simple inner join with column-to-column comparison
$qb->table('users')
   ->join('images', 'images.user_id', '=', 'users.id')
   ->toSql();

// LEFT / RIGHT / FULL joins
$qb->table('users')
   ->leftJoin('orders', 'orders.user_id', '=', 'users.id')
   ->rightJoin('profiles', 'profiles.user_id', '=', 'users.id')
   ->toSql();

// Join with additional filters and nested groups
$qb->table('users')
   ->join('images', function ($j) {
       $j->on('images.user_id', '=', 'users.id');
       $j->orWhere('images.user_id', '=', 1);
       $j->where(function ($nested) {
           $nested->where('images.user_id', '=', 3);
           $nested->orWhere('images.id', '=', 1);
       });
   })
   ->toSql();

// Using orOn for alternative column matches
$qb->table('users')
   ->join('orders', function ($j) {
       $j->on('orders.user_id', '=', 'users.id')
         ->orOn('orders.alt_user_id', '=', 'users.id');
   })
   ->toSql();

// Multiple joins chained
$qb->table('users u')
   ->join('orders o', 'o.user_id', '=', 'u.id')
   ->join('comments c', 'c.user_id', '=', 'u.id')
   ->select('u.id', 'o.amount')
   ->toSql();

$users = $qb->table('users')
    ->objectConverter(fn(array $row) => (object) $row)
    ->get();

use Abdulelahragih\QueryBuilder\DB;

$db = new DB($pdo);
$db->transaction(function () use ($db) {
    $db->table('users')->insert(['id' => 200, 'name' => 'Txn']);
});

use Abdulelahragih\QueryBuilder\DB;

$db = new DB($pdo);
$db->beginTransaction();
try {
    $db->table('users')->insert(['id' => 200, 'name' => 'Txn']);
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
}