PHP code example of mysaaspackage / query-builder

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

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

    

mysaaspackage / query-builder example snippets

use MySaasPackage\QueryBuilder\QueryBuilder;

// Basic select
$query = new QueryBuilder('users');
$query->select('id', 'name', 'email');
// SELECT id, name, email FROM users

// Select with where clause
$query = new QueryBuilder('users');
$query->select('*')
    ->where('age > :age', ['age' => 18]);
// SELECT * FROM users WHERE age > :age

// Select with multiple conditions
$query = new QueryBuilder('users');
$query->select('*')
    ->where('age > :age', ['age' => 18])
    ->andWhere('status = :status', ['status' => 'active'])
    ->orWhere('is_admin = :is_admin', ['is_admin' => true]);
// SELECT * FROM users WHERE age > :age AND status = :status OR is_admin = :is_admin

// Select with joins
$query = new QueryBuilder('users');
$query->select('users.*', 'profiles.bio')
    ->join('profiles', 'p', 'users.id = p.user_id')
    ->leftJoin('addresses', 'a', 'users.id = a.user_id');
// SELECT users.*, profiles.bio FROM users JOIN profiles AS p ON users.id = p.user_id LEFT JOIN addresses AS a ON users.id = a.user_id

// Select with group by and having
$query = new QueryBuilder('orders');
$query->select('user_id', 'COUNT(*) as order_count')
    ->groupBy('user_id')
    ->having('COUNT(*) > :min_orders', ['min_orders' => 5]);
// SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id HAVING COUNT(*) > :min_orders

// Select with order by and limit
$query = new QueryBuilder('products');
$query->select('*')
    ->orderBy('price', 'DESC')
    ->orderBy('name')
    ->limit(10)
    ->offset(20);
// SELECT * FROM products ORDER BY price DESC, name LIMIT 10 OFFSET 20

// Basic CTE
$subQuery = new QueryBuilder('orders');
$subQuery->select('user_id', 'SUM(amount) as total_amount')
    ->groupBy('user_id');

$query = new QueryBuilder('users');
$query->with('user_totals', $subQuery)
    ->select('users.*', 'user_totals.total_amount')
    ->join('user_totals', 'ut', 'users.id = ut.user_id');
// WITH user_totals AS (SELECT user_id, SUM(amount) as total_amount FROM orders GROUP BY user_id)
// SELECT users.*, user_totals.total_amount FROM users JOIN user_totals AS ut ON users.id = ut.user_id

// Recursive CTE (for hierarchical data)
$baseQuery = new QueryBuilder('categories');
$baseQuery->select('id', 'name', 'parent_id')
    ->where('parent_id IS NULL');

$recursiveQuery = new QueryBuilder();
$recursiveQuery->select('c.id', 'c.name', 'c.parent_id')
    ->from('categories', 'c')
    ->join('category_tree', 'ct', 'c.parent_id = ct.id');

$query = new QueryBuilder();
$query->withRecursive('category_tree', $baseQuery, $recursiveQuery)
    ->select('*')
    ->from('category_tree');
// WITH RECURSIVE category_tree AS (
//     SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
//     UNION ALL
//     SELECT c.id, c.name, c.parent_id FROM categories AS c
//     JOIN category_tree AS ct ON c.parent_id = ct.id
// )
// SELECT * FROM category_tree

// INSERT
$query = new QueryBuilder();
$query->insert('users')
    ->values([
        'name' => ':name',
        'email' => ':email',
        'age' => ':age'
    ])
    ->setParameter('name', 'John Doe')
    ->setParameter('email', '[email protected]')
    ->setParameter('age', 30);
// INSERT INTO users (name, email, age) VALUES (:name, :email, :age)

// UPDATE
$query = new QueryBuilder();
$query->update('users')
    ->set([
        'name' => ':new_name',
        'email' => ':new_email'
    ])
    ->where('id = :id', ['id' => 1])
    ->setParameter('new_name', 'Jane Doe')
    ->setParameter('new_email', '[email protected]');
// UPDATE users SET name = :new_name, email = :new_email WHERE id = :id

// DELETE
$query = new QueryBuilder();
$query->delete('users')
    ->where('id = :id', ['id' => 1]);
// DELETE FROM users WHERE id = :id

// Subquery in WHERE clause
$subQuery = new QueryBuilder('orders');
$subQuery->select('user_id')
    ->where('total > :min_total', ['min_total' => 1000]);

$query = new QueryBuilder('users');
$query->select('*')
    ->where('id IN :user_ids', ['user_ids' => $subQuery]);
// SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > :min_total)

// Subquery in SELECT clause
$avgQuery = new QueryBuilder('products');
$avgQuery->select('AVG(price)');

$query = new QueryBuilder('products');
$query->select('name', 'price')
    ->where('price > :avg_price', ['avg_price' => $avgQuery]);
// SELECT name, price FROM products WHERE price > (SELECT AVG(price) FROM products)