PHP code example of solophp / query-builder

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

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

    

solophp / query-builder example snippets

use Solo\QueryBuilder\Utility\QueryFactory;
use Solo\QueryBuilder\Facade\Query;

// Simple initialization using helper factory
$query = QueryFactory::createWithPdo('localhost', 'username', 'password', 'database');

// Enable global cache (ExampleCache, TTL 600 seconds)
use ExampleCache;
Query::enableCache(new ExampleCache(), 600);

// Select data
$results = $query->from('users')
    ->select('id', 'name', 'email')
    ->where('status = ?', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->getAllAssoc();

// Insert data
$insertId = $query->insert('users')
    ->values([
        'name' => 'John Doe', 
        'email' => '[email protected]', 
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->insertGetId();

// Update data
$affectedRows = $query->update('users')
    ->set('status', 'inactive')
    ->set('updated_at', date('Y-m-d H:i:s'))
    ->where('last_login < ?', date('Y-m-d', strtotime('-6 months')))
    ->execute();

// Delete data
$affectedRows = $query->delete('users')
    ->where('id = ?', 5)
    ->execute();

use Solo\QueryBuilder\Facade\Query;
use ExampleCache;

// Global cache for all Query instances (TTL 600s)
Query::enableCache(new ExampleCache(), 600);

// Local override (TTL 300s)
$query = $query->withCache(new ExampleCache(), 300);
$users = $query->from('users')->getAllAssoc();

// Disable global cache entirely
Query::disableCache();

use Solo\QueryBuilder\Facade\Query;
use Solo\QueryBuilder\Executors\PdoExecutor\PdoExecutor;
use Solo\QueryBuilder\Executors\PdoExecutor\Connection;
use Solo\QueryBuilder\Executors\PdoExecutor\Config;
use Solo\QueryBuilder\Factory\BuilderFactory;
use Solo\QueryBuilder\Factory\GrammarFactory;

// Create factories
$grammarFactory = new GrammarFactory();

// Create PDO executor
$config = new Config(
    'localhost',        // host
    'username',         // username
    'password',         // password
    'database',         // database
    PDO::FETCH_ASSOC,   // fetchMode
    'mysql',            // driver
    null,               // port (optional)
    []                  // options (optional)
);

$connection = new Connection($config);
$executor = new PdoExecutor($connection);

// Creating a BuilderFactory with executor
$builderFactory = new BuilderFactory($grammarFactory, $executor, 'mysql');

// Creating a Query instance
$query = new Query($builderFactory);

// Build a query without executing
[$sql, $bindings] = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->build();

// Now you have the SQL string and parameter bindings
echo $sql;
// SELECT `id`, `name` FROM `users` WHERE status = ?

print_r($bindings);
// ['active']

// Set MySQL as default grammar
$query->setDatabaseType('mysql');

// Set PostgreSQL as default grammar
$query->setDatabaseType('postgresql'); // or 'postgres', 'pgsql'

// Set SQLite as default grammar
$query->setDatabaseType('sqlite');

// Query with MySQL grammar
$query->setDatabaseType('mysql');
$mysqlResults = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->getAllAssoc();

// Query with PostgreSQL grammar
$query->setDatabaseType('postgresql');
$postgresResults = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->getAllAssoc();

// Select all records from table
$allUsers = $query->from('users')->getAllAssoc();

// Select specific columns
$users = $query->from('users')
    ->select('id', 'name', 'email')
    ->getAllAssoc();

// Use DISTINCT to select only unique values
$uniqueCities = $query->from('users')
    ->select('city')
    ->distinct()
    ->getAllAssoc();

// WHERE conditions
$activeUsers = $query->from('users')
    ->where('status = ?', 'active')
    ->getAllAssoc();

// Multiple conditions
$recentActiveUsers = $query->from('users')
    ->where('status = ?', 'active')
    ->where('created_at > ?', '2023-01-01')
    ->getAllAssoc();

// Sorting
$sortedUsers = $query->from('users')
    ->orderBy('name')                // ASC by default
    ->addOrderBy('created_at', 'DESC') // additional sorting
    ->getAllAssoc();

// Limit and offset
$paginatedUsers = $query->from('users')
    ->limit(10, 100) // 10 records starting from offset 100
    ->getAllAssoc();
    
// Pagination with page
$paginatedUsers = $query->from('users')
    ->paginate(25, 1) // 25 records per page, page 1
    ->getAllAssoc();
    
// Get a single record
$user = $query->from('users')
    ->where('id = ?', 1)
    ->getAssoc();
    
// Get records as objects
$userObjects = $query->from('users')
    ->where('status = ?', 'active')
    ->getAllObj();
    
// Get a single value
$count = $query->from('users')
    ->select('COUNT(*) as count')
    ->getValue();
    
// Get an array of email addresses
$emails = $query->from('users')
    ->select('id', 'email', 'name')
    ->where('status = ?', 'active')
    ->getColumn('email');
// Result: ['[email protected]', '[email protected]', '[email protected]']

// Get an associative array of [id => name]
$userNames = $query->from('users')
    ->select('id', 'name')
    ->getColumn('name', 'id');
// Result: [1 => 'John', 2 => 'Jane', 3 => 'Bob']

// Get prices from products
$prices = $query->from('products')
    ->select('id', 'name', 'price')
    ->where('category_id = ?', 5)
    ->getColumn('price');
// Result: [19.99, 24.99, 14.50]

// Count the number of records
$totalUsers = $query->from('users')->count();

// Count records with conditions
$activeUserCount = $query->from('users')
    ->where('status = ?', 'active')
    ->count();

// Count specific fields or unique values
$emailCount = $query->from('users')->count('email'); // Count of non-NULL emails
$uniqueCities = $query->from('users')->count('city', true); // Count of unique cities

// Count distinct with DISTINCT keyword for better readability
$uniqueDepartments = $query->from('users')
    ->select('department')
    ->distinct()
    ->count();

// Raw expressions in select
$users = $query->from('users')
    ->select('id', 'name', '{CONCAT(first_name, " ", last_name) as full_name}')
    ->getAllAssoc();

// Aggregation functions
$userStats = $query->from('orders')
    ->select('user_id', '{COUNT(*) as order_count}', '{SUM(amount) as total_spend}')
    ->groupBy('user_id')
    ->having('total_spend > ?', 1000)
    ->getAllAssoc();

// Date functions
$ordersByMonth = $query->from('orders')
    ->select('id', '{DATE_FORMAT(created_at, "%Y-%m") as month}', 'status')
    ->where('created_at >= ?', '2023-01-01')
    ->getAllAssoc();

// Complex expressions
$categorizedProducts = $query->from('products')
    ->select(
        'id', 
        'name',
        '{CASE WHEN price > 100 THEN "Premium" WHEN price > 50 THEN "Standard" ELSE "Basic" END as category}'
    )
    ->getAllAssoc();

// Use DISTINCT with raw expressions
$uniqueCategories = $query->from('products')
    ->select('{DISTINCT category}')
    ->getAllAssoc();

// Alternative with distinct() method
$uniqueCategories = $query->from('products')
    ->select('category')
    ->distinct()
    ->getAllAssoc();

// Only apply where clause if condition is true
$email = '[email protected]';
$status = null;

$users = $query->from('users')
    ->when($email !== null, function($q) use ($email) {
        return $q->where('email = ?', $email);
    })
    ->when($status !== null, function($q) use ($status) {
        return $q->where('status = ?', $status);
    })
    ->getAllAssoc();

// Apply a default callback when condition is false
$minPrice = null;
$defaultMinPrice = 10;

$products = $query->from('products')
    ->when($minPrice !== null,
        function($q) use ($minPrice) {
            return $q->where('price >= ?', $minPrice);
        },
        function($q) use ($defaultMinPrice) {
            return $q->where('price >= ?', $defaultMinPrice);
        }
    )
    ->getAllAssoc();

// Nested conditions
$users = $query->from('users')
    ->where(function($condition) {
        $condition->where('status = ?', 'active')
                 ->orWhere('role = ?', 'admin');
    })
    ->getAllAssoc();

// IN conditions
$specificUsers = $query->from('users')
    ->where('id IN (?, ?, ?)', 1, 2, 3)
    ->getAllAssoc();

// BETWEEN conditions
$usersInRange = $query->from('users')
    ->where('created_at BETWEEN ? AND ?', '2023-01-01', '2023-12-31')
    ->getAllAssoc();

// INNER JOIN
$ordersWithUsers = $query->from('orders')
    ->select('orders.id', 'orders.amount', 'users.name')
    ->join('users', 'orders.user_id = users.id')
    ->getAllAssoc();
    
// JOIN with additional condition
$activeOrdersWithUsers = $query->from('orders')
    ->select('orders.id', 'orders.amount', 'users.name')
    ->join('users', 'orders.user_id = users.id AND users.status = ?', 'active')
    ->getAllAssoc();

// LEFT JOIN
$usersWithProfiles = $query->from('users')
    ->select('users.id', 'users.name', 'profiles.bio')
    ->leftJoin('profiles', 'users.id = profiles.user_id')
    ->getAllAssoc();

// RIGHT JOIN
$usersWithOrders = $query->from('orders')
    ->select('orders.id', 'users.name')
    ->rightJoin('users', 'orders.user_id = users.id')
    ->getAllAssoc();

// FULL JOIN
$allUsersProfiles = $query->from('users')
    ->select('users.id', 'profiles.bio')
    ->fullJoin('profiles', 'users.id = profiles.user_id')
    ->getAllAssoc();

// GROUP BY with aggregate functions
$userOrderStats = $query->from('orders')
    ->select('user_id', '{COUNT(*) as order_count}', '{SUM(amount) as total_spend}')
    ->groupBy('user_id')
    ->having('total_spend > ?', 1000)
    ->getAllAssoc();

// Insert one record and get ID
$userId = $query->insert('users')
    ->values([
        'name' => 'John Doe', 
        'email' => '[email protected]', 
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->insertGetId();

// Insert one record and get affected rows
$affectedRows = $query->insert('users')
    ->values([
        'name' => 'John Doe', 
        'email' => '[email protected]', 
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

// Insert multiple records
$affectedRows = $query->insert('logs')
    ->values([
        ['user_id' => 1, 'action' => 'login', 'created_at' => date('Y-m-d H:i:s')],
        ['user_id' => 2, 'action' => 'logout', 'created_at' => date('Y-m-d H:i:s')]
    ])
    ->execute();

// Update with array of values
$affectedRows = $query->update('users')
    ->set([
        'status' => 'inactive',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where('last_login < ?', date('Y-m-d', strtotime('-6 months')))
    ->execute();

// Or update by setting fields individually
$affectedRows = $query->update('users')
    ->set('status', 'inactive')
    ->set('updated_at', date('Y-m-d H:i:s'))
    ->where('id = ?', 5)
    ->execute();

// Delete with condition
$affectedRows = $query->delete('expired_tokens')
    ->where('expires_at < ?', date('Y-m-d H:i:s'))
    ->execute();

// Delete by ID
$affectedRows = $query->delete('users')
    ->where('id = ?', 5)
    ->execute();

// Check if records exist
$exists = $query->from('users')
    ->where('status = ?', 'active')
    ->exists();

use Solo\QueryBuilder\Utility\QueryFactory;

// Create a Query instance with one line
$query = QueryFactory::createWithPdo(
    'localhost',       // host
    'db_user',         // username
    'db_password',     // password
    'database_name',   // database
    PDO::FETCH_ASSOC,  // fetchMode
    'mysql',           // optional database type
    null,              // optional port
    []                 // optional PDO options
);

// Execute query and get results
$users = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->getAllAssoc();

use Solo\QueryBuilder\Executors\PdoExecutor\PdoExecutor;
use Solo\QueryBuilder\Executors\PdoExecutor\Connection;
use Solo\QueryBuilder\Executors\PdoExecutor\Config;
use Solo\QueryBuilder\Factory\BuilderFactory;
use Solo\QueryBuilder\Factory\GrammarFactory;
use Solo\QueryBuilder\Facade\Query;

// Configure PDO connection
$config = new Config(
    'localhost',        // host
    'db_user',          // username
    'db_password',      // password
    'database_name',    // database
    PDO::FETCH_ASSOC,   // fetchMode
    'mysql',            // driver
    3306,               // port (optional)
    []                  // options (optional)
);

// Create connection and executor
$connection = new Connection($config);
$executor = new PdoExecutor($connection);

// Create factories
$grammarFactory = new GrammarFactory();
$builderFactory = new BuilderFactory($grammarFactory, $executor, 'mysql');

// Create query builder
$query = new Query($builderFactory);

namespace Solo\QueryBuilder\Grammar;

final class CustomGrammar extends AbstractGrammar
{
    protected string $tableQuote = '`';
    protected string $columnQuote = '`';
    
    // No need to implement any methods as they are already in AbstractGrammar
    // Just override the quote characters as needed
}

// Then update GrammarFactory to recognize your new dialect