PHP code example of buildql / query-builder

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

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

    

buildql / query-builder example snippets

BuildQL\Database\Query\DB;

// Manual configuration
DB::setConnection(
    'localhost',  // host
    'root',       // username
    '',           // password
    'my_database',// database (optional)
    3306          // port (optional, default: 3306)
);

// Now you can use the query builder
$users = DB::table('users')->get();

uildQL\Database\Query\DB;

// Automatically loads from .env in project's root if exists
DB::boot();

// Or specify custom path (directory only, not .env file)
DB::boot("/path/to/your/env/directory");

public static function setConnection(
    string $server, 
    string $username, 
    string $pass, 
    ?string $database = null, 
    int $port = 3306
): void

DB::setConnection('localhost', 'root', '', 'my_app', 3306);

public static function boot(?string $absoluteEnvPath = null): void

// Load from project root
DB::boot();

// Load from custom directory
DB::boot("/var/www/config");

// If path contains .env filename, it will be automatically removed
DB::boot("/var/www/config/.env"); // Works the same as above

public static function table(string $table, ?string $database = null): Builder

$query = DB::table('users');
$query = DB::table('users:u'); // With alias
$query = DB::table('users', 'other_db'); // Different database

public static function raw(string $sql, array $bind = []): mixed

// SELECT query
$results = DB::raw("SELECT * FROM users WHERE age > ?", [18]);

// INSERT query
$success = DB::raw("INSERT INTO users (name, email) VALUES (?, ?)", ['John', '[email protected]']);

// UPDATE query
$success = DB::raw("UPDATE users SET status = ? WHERE id = ?", ['active', 1]);

// ❌ Wrong way to bind values
$success = DB::raw("UPDATE users SET status = :active WHERE id = :id", ['active' => 'active', 'id' => 1]);

public static function setDatabaseGlobally(string $database): void

DB::setDatabaseGlobally('my_app');

public static function getGlobalDatabase(): ?string

DB::setDatabaseGlobally('my_app');
$currentDb = DB::getGlobalDatabase(); // Returns: 'my_app'

public static function resetConnection(): void

DB::resetConnection();

public function select(array $columns = ['*']): self

// Select all columns
DB::table('users')->select(['*'])->get();

// Select specific columns
DB::table('users')->select(['name', 'email'])->get();

// With aliases
DB::table('users')->select(['name:user_name', 'email:user_email'])->get();

// Qualified columns (with table name)
DB::table('users')
    ->join('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.name', 'profiles.bio', 'users.email:user_email'])
    ->get();

// Aggregate functions in select
DB::table('orders')
    ->select(['user_id', 'count(*):total_orders', 'sum(amount):total_spent'])
    ->groupBy('user_id')
    ->get();

// count records check existance 
DB::table('orders')
    ->where("post", "like", "*mango*")
    ->count();

DB::table('orders')
    ->where("post", "like", "*mango*")
    ->exists(); // added in v1.0.4

public function selectAggregate(
    ?string $count = null,
    ?string $sum = null,
    ?string $min = null,
    ?string $max = null,
    ?string $avg = null
): self

// Count records
DB::table('users')
    ->selectAggregate(count: '*:total_users')
    ->first();
// Result: ['total_users' => 150]

// Multiple aggregates
DB::table('orders')
    ->select(['user_id'])
    ->selectAggregate(
        count: '*:order_count',
        sum: 'amount:total',
        avg: 'amount:average'
    )
    ->groupBy('user_id')
    ->get();

public function distinct(): self

// Get unique cities
DB::table('users')
    ->select(['city'])
    ->distinct()
    ->get();

public function where(string $column, $operator, $value = null): self

// Equal comparison (shorthand)
DB::table('users')->where('status', 'active')->get();

// Explicit operator
DB::table('users')->where('age', '>', 18)->get();

// LIKE operator
DB::table('users')->where('name', 'LIKE', '%John%')->get();

// Multiple WHERE conditions (AND)
DB::table('users')
    ->where('status', 'active')
    ->where('age', '>=', 18)
    ->where('country', 'USA')
    ->get();

public function orWhere(string $column, $operator, $value = null): self

// WHERE status = 'active' OR status = 'pending'
DB::table('users')
    ->where('status', 'active')
    ->orWhere('status', 'pending')
    ->get();

// WHERE (age < 18) OR (age > 65)
DB::table('users')
    ->where('age', '<', 18)
    ->orWhere('age', '>', 65)
    ->get();

public function whereIn(string $column, array $values): self

// WHERE id IN (1, 2, 3, 4, 5)
DB::table('users')
    ->whereIn('id', [1, 2, 3, 4, 5])
    ->get();

// Multiple whereIn conditions (AND)
DB::table('products')
    ->whereIn('category_id', [1, 2, 3])
    ->whereIn('status', ['active', 'featured'])
    ->get();

public function orWhereIn(string $column, array $values): self

DB::table('users')
    ->whereIn('role', ['admin', 'moderator'])
    ->orWhereIn('permission', ['write', 'delete'])
    ->get();

public function whereNotIn(string $column, array $values): self

// Exclude specific user IDs
DB::table('users')
    ->whereNotIn('id', [1, 2, 3])
    ->get();

// Exclude blocked statuses
DB::table('accounts')
    ->whereNotIn('status', ['banned', 'suspended', 'deleted'])
    ->get();

public function orWhereNotIn(string $column, array $values): self

DB::table('users')
    ->whereNotIn('status', ['banned'])
    ->orWhereNotIn('role', ['guest'])
    ->get();

public function whereNull(string $column): self

// Find users without email verification
DB::table('users')
    ->whereNull('email_verified_at')
    ->get();

// Multiple NULL checks
DB::table('profiles')
    ->whereNull('phone')
    ->whereNull('address')
    ->get();

public function orWhereNull(string $column): self

DB::table('users')
    ->whereNull('deleted_at')
    ->orWhereNull('banned_at')
    ->get();

public function whereNotNull(string $column): self

// Find verified users
DB::table('users')
    ->whereNotNull('email_verified_at')
    ->get();

public function orWhereNotNull(string $column): self

DB::table('users')
    ->whereNotNull('phone')
    ->orWhereNotNull('mobile')
    ->get();

public function join(
    string $table, 
    ?string $primaryKey = null, 
    ?string $foreignKey = null, 
    string $type = "inner"
): self

// Simple INNER JOIN
DB::table('users')
    ->join('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.name', 'profiles.bio'])
    ->get();

// JOIN with table aliases
DB::table('users:u')
    ->join('profiles:p', 'u.id', 'p.user_id')
    ->join('posts:po', 'u.id', 'po.user_id')
    ->select(['u.name', 'p.bio', 'count(po.id):post_count'])
    ->groupBy('u.id')
    ->get();

// Multiple JOINs
DB::table('orders')
    ->join('users', 'orders.user_id', 'users.id')
    ->join('products', 'orders.product_id', 'products.id')
    ->select(['users.name', 'products.title', 'orders.amount'])
    ->get();

public function leftJoin(string $table, string $primaryKey, string $foreignKey): self

// Get all users, including those without profiles
DB::table('users')
    ->leftJoin('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.name', 'profiles.bio'])
    ->get();

public function rightJoin(string $table, string $primaryKey, string $foreignKey): self

DB::table('orders')
    ->rightJoin('users', 'orders.user_id', 'users.id')
    ->get();

public function crossJoin(string $table): self

// Generate all combinations of sizes and colors
DB::table('sizes')
    ->crossJoin('colors')
    ->get();

public function groupBy(string ...$columns): self

// Group by single column
DB::table('orders')
    ->select(['user_id', 'count(*):total_orders'])
    ->groupBy('user_id')
    ->get();

// Group by multiple columns
DB::table('sales')
    ->select(['country', 'city', 'sum(amount):total'])
    ->groupBy('country', 'city')
    ->get();

public function having(string $column, $operator, $value = null): self

// Users with more than 5 orders
DB::table('orders')
    ->select(['user_id', 'count(*):order_count'])
    ->groupBy('user_id')
    ->having('order_count', '>', 5)
    ->get();

// Multiple HAVING conditions
DB::table('sales')
    ->select(['product_id', 'sum(amount):total', 'count(*):sales_count'])
    ->groupBy('product_id')
    ->having('total', '>', 1000)
    ->having('sales_count', '>=', 10)
    ->get();

public function orHaving(string $column, $operator, $value = null): self

DB::table('orders')
    ->select(['user_id', 'count(*):total', 'sum(amount):revenue'])
    ->groupBy('user_id')
    ->having('total', '>', 10)
    ->orHaving('revenue', '>', 5000)
    ->get();

public function orderBy(string $column, string $sort = "ASC"): self

// Sort ascending (default)
DB::table('users')->orderBy('name')->get();

// Sort descending
DB::table('users')->orderBy('created_at', 'DESC')->get();

// Multiple sort columns
DB::table('products')
    ->orderBy('category', 'ASC')
    ->orderBy('price', 'DESC')
    ->get();

public function limit(int $limit, ?int $offset = null): self

// Get first 10 records
DB::table('users')->limit(10)->get();

// Skip 20 records, get next 10 (pagination)
DB::table('users')->limit(10, 20)->get();

public function offset(int $offset): self

// Page 3 of results (20 per page)
DB::table('posts')
    ->orderBy('created_at', 'DESC')
    ->limit(20)
    ->offset(40)
    ->get();

public function get(array $columns = ["*"]): array

// Get all users
$users = DB::table('users')->get();

// Get specific columns
$users = DB::table('users')->get(['id', 'name', 'email']);

// With WHERE condition
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->get();

// Result format:
// [
//     ['id' => 1, 'name' => 'John', 'email' => '[email protected]'],
//     ['id' => 2, 'name' => 'Jane', 'email' => '[email protected]'],
// ]

public function all(): array

$allUsers = DB::table('users')->all();

public function first(array $columns = ['*']): array

// Get first user
$user = DB::table('users')->first();
// Result: ['id' => 1, 'name' => 'John', ...]

// Get first matching record
$admin = DB::table('users')
    ->where('role', 'admin')
    ->first();

// Specific columns
$user = DB::table('users')->first(['id', 'name']);

public function find(int $id, array $columns = ['*']): array

// Find user by ID
$user = DB::table('users')->find(1);

// With specific columns
$user = DB::table('users')->find(1, ['name', 'email']);

// Works with table aliases
$user = DB::table('users:u')
    ->join('profiles:p', 'u.id', 'p.user_id')
    ->find(1, ['u.name', 'p.bio']);

public function count(): int

// Count all users
$total = DB::table('users')->count();

// Count with condition
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->count();

// Count distinct values
$uniqueCities = DB::table('users')
    ->select(['city'])
    ->distinct()
    ->count();

public function exists(): bool

// check whether the users table empty or not
$total = DB::table('users')->exists();

// check existance with condition
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->exists();

// check existance with multiple condition
$uniqueCities = DB::table('users')
    ->where('name', 'like', "a%")
    ->whereNotNull('email_verified')
    ->exists();

public function insert(array $data): bool

// Insert single record
$success = DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => '[email protected]',
    'password' => password_hash('secret', PASSWORD_DEFAULT),
    'created_at' => date('Y-m-d H:i:s')
]);

// Insert with NULL values
DB::table('profiles')->insert([
    'user_id' => 1,
    'bio' => 'Software Developer',
    'phone' => null  // NULL value
]);

public function update(array $data): bool

// Update single record
DB::table('users')
    ->where('id', 1)
    ->update([
        'name' => 'John Smith',
        'updated_at' => date('Y-m-d H:i:s')
    ]);

// Update multiple records
DB::table('users')
    ->where('status', 'pending')
    ->update(['status' => 'active']);

// Update with multiple conditions
DB::table('orders')
    ->where('status', 'pending')
    ->where('created_at', '<', date('Y-m-d', strtotime('-30 days')))
    ->update(['status' => 'cancelled']);

// ❌ wrong way without where condition. In that case exception will be thrown
DB::table('users')
    ->update(['status' => 'active']);

public function delete(): bool

// Delete single record
DB::table('users')
    ->where('id', 1)
    ->delete();

// Delete multiple records
DB::table('logs')
    ->where('created_at', '<', date('Y-m-d', strtotime('-90 days')))
    ->delete();

// Delete with multiple conditions
DB::table('spam')
    ->where('reported', '>', 5)
    ->whereNull('verified_at')
    ->delete();

// ❌ Wrong way without where condition. In that case exception will be thrown
DB::table('users')
    ->delete();

public function insertOrUpdate(array $data, array $where = []): bool

// Update if exists, insert if not
DB::table('settings')->insertOrUpdate(
    ['value' => 'dark'],
    ['key' => 'theme']
);

// With multiple conditions
DB::table('user_preferences')->insertOrUpdate(
    [
        'user_id' => 1,
        'preference' => 'notifications',
        'value' => 'enabled'
    ],
    [
        'user_id' => 1,
        'preference' => 'notifications'
    ]
);

// With whereIn condition
DB::table('inventory')->insertOrUpdate(
    ['stock' => 100],
    ['product_id' => [1, 2, 3]]  // Array value = whereIn
);

public function toRawSql(): string

$sql = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 18)
    ->toRawSql();
// Result: "SELECT * FROM `users` WHERE `status` = ? AND `age` > ?"

// Complex query preview
$sql = DB::table('orders:o')
    ->join('users:u', 'o.user_id', 'u.id')
    ->select(['u.name', 'count(o.id):total'])
    ->where('o.status', 'completed')
    ->groupBy('u.id')
    ->having('total', '>', 5)
    ->orderBy('total', 'DESC')
    ->toRawSql();

// Result: SELECT `u`.`name`, count(`o`.`id`) as `total` FROM `orders` as `o` INNER JOIN `users` as `u` ON `o`.`user_id` = `u`.`id` WHERE `o`.`status` = ? GROUP BY `u`.`id` HAVING `total` > ? ORDER BY `total` DESC

// Register new user
DB::table('users')->insert([
    'name' => 'Alice Johnson',
    'email' => '[email protected]',
    'password' => password_hash('secure_password', PASSWORD_DEFAULT),
    'role' => 'user',
    'created_at' => date('Y-m-d H:i:s')
]);

// Authenticate user
$user = DB::table('users')
    ->where('email', '[email protected]')
    ->whereNotNull('email_verified_at')
    ->first();

if ($user && password_verify('secure_password', $user['password'])) {
    // Login successful
}

// Get user with profile
$userProfile = DB::table('users:u')
    ->leftJoin('profiles:p', 'u.id', 'p.user_id')
    ->where('u.id', 1)
    ->first([
        'u.id', 'u.name', 'u.email',
        'p.bio', 'p.avatar', 'p.location'
    ]);

// Search products with filters
$products = DB::table('products')
    ->where('name', 'LIKE', '%laptop%')
    ->where('price', '>=', 500)
    ->where('price', '<=', 2000)
    ->whereIn('category_id', [1, 2, 3])
    ->whereNotNull('stock')
    ->where('status', 'active')
    ->orderBy('price', 'ASC')
    ->limit(20)
    ->get(['id', 'name', 'price', 'stock', 'image']);

// Product details with reviews
$product = DB::table('products:p')
    ->leftJoin('reviews:r', 'p.id', 'r.product_id')
    ->select([
        'p.*',
        'count(r.id):review_count',
        'avg(r.rating):avg_rating'
    ])
    ->where('p.id', 1)
    ->groupBy('p.id')
    ->first();

// Sales by month
$monthlySales = DB::table('orders')
    ->select([
        'DATE_FORMAT(created_at, "%Y-%m"):month',
        'count(*):order_count',
        'sum(total):revenue'
    ])
    ->where('status', 'completed')
    ->where('created_at', '>=', date('Y-01-01'))
    ->groupBy('month')
    ->orderBy('month', 'DESC')
    ->get();

// Top customers
$topCustomers = DB::table('orders:o')
    ->join('users:u', 'o.user_id', 'u.id')
    ->select([
        'u.id',
        'u.name',
        'count(o.id):order_count',
        'sum(o.total):lifetime_value'
    ])
    ->where('o.status', 'completed')
    ->groupBy('u.id')
    ->having('order_count', '>', 5)
    ->orderBy('lifetime_value', 'DESC')
    ->limit(10)
    ->get();

// Published posts with author info
$posts = DB::table('posts:p')
    ->join('users:u', 'p.author_id', 'u.id')
    ->leftJoin('categories:c', 'p.category_id', 'c.id')
    ->select([
        'p.id', 'p.title', 'p.slug', 'p.excerpt',
        'p.published_at', 'u.name:author_name',
        'c.name:category_name'
    ])
    ->whereNotNull('p.published_at')
    ->where('p.status', 'published')
    ->orderBy('p.published_at', 'DESC')
    ->limit(10)
    ->get();

// Post with comments count
$post = DB::table('posts:p')
    ->leftJoin('comments:c', 'p.id', 'c.post_id')
    ->select(['p.*', 'count(c.id):comment_count'])
    ->where('p.slug', 'my-first-post')
    ->groupBy('p.id')
    ->first();

// ✅ SAFE - Uses prepared statements (Recommended way)
$email = $_POST['email'];
$user = DB::table('users')->where('email', $email)->first();

// ❌ NEVER DO THIS
$user = DB::raw("SELECT * FROM users WHERE email = '$email'"); // SQL Injection risk!

// ✅ SAFE - If you want to manually write query then replace actual value to placeholder (?)  
//           and attach value to second param of raw() method as an array Like
$user = DB::raw("SELECT * FROM users WHERE email = ?", [$email]); // No SQL Injection risk!

// ✅ SAFE - Whitelist allowed columns
$allowedSortColumns = ['name', 'created_at', 'price'];
$sortBy = $_GET['sort'] ?? 'created_at';

if (in_array($sortBy, $allowedSortColumns)) {
    $products = DB::table('products')->orderBy($sortBy)->get();
}

// ✅ SAFE - Validate before insert
$name = trim($_POST['name']);
$email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);

if ($email && strlen($name) > 0) {
    DB::table('users')->insert([
        'name' => $name,
        'email' => $email
    ]);
}

DB::raw("START TRANSACTION");

try {
    DB::table('orders')->insert([
        'user_id' => 1,
        'total' => 99.99
    ]);
    
    DB::table('inventory')->where('product_id', 1)->update(['stock' => 5]);
    
    DB::raw("COMMIT");
} catch (BuilderException $e) {
    DB::raw("ROLLBACK");
    throw $e;
}

use BuildQL\Database\Query\DB;
use BuildQL\Database\Query\Exception\BuilderException;

try {
    $users = DB::table('users')->where('age', '>', 18)->get();
} catch (BuilderException $e) {
    // Get detailed error message with trace
    echo $e->getErrorMessage();
    // Output: "Query Execution Failed: Table 'database.users' doesn't exist - Check your code in app.php at line 25"
    
    // Log error
    error_log($e->getErrorMessage());
}

// With trace (default behavior)
throw new BuilderException("Error message"); // Shows: "Error message - Check your code in file.php at line 25"

// Without trace (useful for production)
throw new BuilderException("Error message", false); // Shows: "Error message"

// Connection is not established right now.
// Connection already established. Connection will not be established more than once.
// Connection Failed Due to : Access denied for user

// Where method is not optional in update case
// Where method is not optional in delete case
// Invalid column name : user_id;DROP TABLE users
// Query Preparation Failed: You have an error in your SQL syntax
// Query Execution Failed: You have an error in your SQL syntax

// $values parameter must be a non-empty array
// Select method must be contain a proper non-empty array of columns name
// Invalid join clause (INVALID_TYPE)

// Subquery-like behavior using multiple queries
$userIds = DB::table('orders')
    ->select(['user_id'])
    ->where('status', 'completed')
    ->where('created_at', '>', date('Y-m-d', strtotime('-30 days')))
    ->groupBy('user_id')
    ->having('count(*)', '>', 5)
    ->get();

$topBuyers = array_column($userIds, 'user_id');

$users = DB::table('users')
    ->whereIn('id', $topBuyers)
    ->orderBy('name')
    ->get();

$query = DB::table('products');

// Apply filters conditionally
if (isset($_GET['category'])) {
    $query->where('category_id', $_GET['category']);
}

if (isset($_GET['min_price'])) {
    $query->where('price', '>=', $_GET['min_price']);
}

if (isset($_GET['max_price'])) {
    $query->where('price', '<=', $_GET['max_price']);
}

if (isset($_GET['search'])) {
    $query->where('name', 'LIKE', '%' . $_GET['search'] . '%');
}

// Apply sorting
$sortBy = $_GET['sort'] ?? 'name';
$sortDir = $_GET['dir'] ?? 'ASC';
$query->orderBy($sortBy, $sortDir);

// Pagination
$page = $_GET['page'] ?? 1;
$perPage = 20;
$query->limit($perPage, ($page - 1) * $perPage);

$products = $query->get();

function paginate($table, $perPage = 15, $page = 1, $conditions = []) {
    $query = DB::table($table);
    
    // Apply conditions
    foreach ($conditions as $column => $value) {
        if (is_array($value)) {
            $query->whereIn($column, $value);
        } else {
            $query->where($column, $value);
        }
    }
    
    // Get total count
    $total = $query->count();
    
    // Get paginated results
    $offset = ($page - 1) * $perPage;
    $data = $query->limit($perPage, $offset)->get();
    
    return [
        'data' => $data,
        'total' => $total,
        'per_page' => $perPage,
        'current_page' => $page,
        'last_page' => ceil($total / $perPage)
    ];
}

// Usage
$result = paginate('products', 20, 2, ['status' => 'active']);

php
// Create a query logger
function logQuery($query) {
    $sql = $query->toRawSql();
    $timestamp = date('Y-m-d H:i:s');
    file_put_contents('query.log', "[$timestamp] $sql\n", FILE_APPEND);
}

// Use in your application
$query = DB::table('users')->where('status', 'active');
logQuery($query);
$users = $query->get();

php
// ✅ Good practice
$recentPosts = DB::table('posts')
    ->orderBy('created_at', 'DESC')
    ->limit(50)
    ->get();

php
// ❌ BAD - N+1 queries
$users = DB::table('users')->get();
$profiles = [];
foreach ($users as $user) {
    $profile = DB::table('profiles')->where('user_id', $user['id'])->first();
    if ($profile){
        $profiles[] = $profile;
    }
    else{
        $profiles[] = null;
    }
    // Process...
}

// ✅ GOOD - Single query with JOIN
$usersWithProfiles = DB::table('users')
    ->leftJoin('profiles', 'users.id', 'profiles.user_id')
    ->select(['users.*', 'profiles.bio', 'profiles.avatar'])
    ->get();

php
use BuildQL\Database\Query\DB;
use BuildQL\Database\Query\Exception\BuilderException;

beforeEach(function() {
    DB::resetConnection(); // for security perpective
    DB::setConnection('localhost', 'root', '', 'test_db');
});

test('select query generates correct SQL', function() {
    $sql = DB::table('users')
        ->where('status', 'active')
        ->toRawSql();
    
    expect($sql)->toContain('SELECT * FROM `users` WHERE `status` = ?');
});

test('insert method works correctly', function() {
    $result = DB::table('users')->insert([
        'name' => 'Test User',
        'email' => '[email protected]'
    ]);
    
    expect($result)->toBeTrue();
});

afterEach(function() {
    DB::resetConnection(); // after the end of the script, reset the database connection
});

php
DB::setConnection('localhost', 'root', '', 'mydb');

php
DB::resetConnection();
DB::setConnection('localhost', 'root', '', 'new_db');

php
// ❌ Invalid
DB::table('users')->where('user name', 'John');
DB::table('users')->where('`user_name`', 'John');
DB::table('posts')->where('count(`user_id`):user_post_count', 'John');

// ✅ Valid
DB::table('users')->where('user_name', 'John');
DB::table('posts')->where('count(user_id):user_post_count', 'John');

php
// 1. Check the generated SQL
$sql = DB::table('users')->where('status', 'active')->toRawSql();
echo $sql;

// 2. Test with raw SQL
$result = DB::raw("SELECT * FROM users WHERE status = ?", ['active']);
print_r($result);

// 3. Check database connection
$db = DB::raw("SELECT DATABASE() as db");
print_r($db);