PHP download

PHP code example of flowpack / query-object-builder

1. Go to this page and download the library: Download flowpack/query-object-builder library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

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

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

flowpack / query-object-builder example snippets


use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg(true)))
    ->orderBy(Q::n('name'));

[$sql, $args] = Q::build($q)->toSql();

echo $sql;        // SELECT name,email FROM users WHERE active = $1 ORDER BY name
var_dump($args);  // [true]


use Flowpack\QueryObjectBuilder\MySQL\Q;

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg(true)))
    ->orderBy(Q::n('name'));

[$sql, $args] = Q::build($q)->toSql();

echo $sql;        // SELECT name,email FROM users WHERE active = ? ORDER BY name
var_dump($args);  // [true]


$base = Q::select(Q::n('*'))->from(Q::n('users'));

$active = $base->where(Q::n('active')->eq(Q::bool(true)));
$recent = $base->where(Q::n('created_at')->gt(Q::string('2024-01-01')));
// $base is unchanged


[$sql, $args] = Q::build($q)->toSql();                       // validate + render
[$sql, $args] = Q::build($q)->withoutValidation()->toSql();  // skip value checks
[$sql, $args] = Q::build($q)->withNamedArgs([...])->toSql();  // bind Q::bind() names


use Flowpack\QueryObjectBuilder\MySQL\Q;
use Flowpack\QueryObjectBuilder\MySQL\Builder\Target;

$q = Q::select(Q::n('id'))->from(Q::n('t'))->forShare();   // FOR SHARE is MySQL-only

Q::build($q)->withValidateTarget(Target::mysql())->toSql();    // ok
Q::build($q)->withValidateTarget(Target::mariaDb())->toSql();  // throws QueryBuilderException:
// "FOR SHARE


$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg(true)));


$q = Q::select(Q::n('*'))
    ->from(Q::n('employees'))
    ->where(Q::and(
        Q::or(
            Q::n('firstname')->like(Q::arg('John%')),
            Q::n('lastname')->like(Q::arg('John%')),
        ),
        Q::n('active')->eq(Q::bool(true)),
    ));


$q = Q::select(Q::n('department'))->distinct()->from(Q::n('employees'));


$q = Q::select(Q::n('name'), Q::n('salary'))
    ->from(Q::n('employees'))
    ->orderBy(Q::n('salary'))->desc()
    ->limit(Q::int(10))
    ->offset(Q::int(20));


$q = Q::select(Q::n('u.name'), Q::n('p.title'))
    ->from(Q::n('users'))->as('u')
    ->leftJoin(Q::n('posts'))->as('p')->on(Q::n('u.id')->eq(Q::n('p.user_id')));


$q = Q::select(Q::n('*'))
    ->from(Q::n('orders'))->as('o')
    ->joinLateral(
        Q::select(Q::n('*'))->from(Q::n('items'))->as('i')
            ->where(Q::n('i.order_id')->eq(Q::n('o.id')))
            ->limit(Q::int(3)),
    )->as('top')->on(Q::bool(true));


$q = Q::select(Q::n('department'), Q\Func::count(Q::n('*')))->as('n')
    ->from(Q::n('employees'))
    ->groupBy(Q::n('department'))
    ->having(Q\Func::count(Q::n('*'))->gt(Q::int(5)));


// PostgreSQL: GROUP BY ROLLUP (...)
$q = Q::select(Q::n('department'), Q::n('job_title'), Q\Func::sum(Q::n('salary')))
    ->from(Q::n('employees'))
    ->groupBy()->rollup(Q::exps(Q::n('department')), Q::exps(Q::n('job_title')));
// SELECT department, job_title, sum(salary) FROM employees
// GROUP BY ROLLUP (department, job_title)

// MySQL / MariaDB: GROUP BY ... WITH ROLLUP
$q = Q::select(Q::n('department'), Q::n('job_title'), Q\Func::sum(Q::n('salary')))
    ->from(Q::n('employees'))
    ->groupBy(Q::n('department'), Q::n('job_title'))->withRollup();
// SELECT department, job_title, SUM(salary) FROM employees
// GROUP BY department, job_title WITH ROLLUP


$q = Q::select(
    Q::n('name'),
    Q::n('salary'),
    Q\Func::rowNumber()->over()->partitionBy(Q::n('department'))->orderBy(Q::n('salary'))->desc(),
)->from(Q::n('employees'));


$q = Q::select(
    Q\Func::sum(Q::n('salary'))->over('w'),
    Q\Func::avg(Q::n('salary'))->over('w'),
)
    ->from(Q::n('empsalary'))
    ->window('w')->as()->partitionBy(Q::n('depname'))->orderBy(Q::n('salary'))->desc();


use Flowpack\QueryObjectBuilder\MySQL\Q;

// Running total: ROWS UNBOUNDED PRECEDING
$q = Q::select(
    Q\Func::sum(Q::n('val'))->over()
        ->partitionBy(Q::n('subject'))->orderBy(Q::n('time'))
        ->rows(Q::unboundedPreceding()),
)->from(Q::n('observations'));
// SELECT SUM(val) OVER (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING) FROM observations

// Moving average: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
$q = Q::select(
    Q\Func::avg(Q::n('val'))->over()
        ->partitionBy(Q::n('subject'))->orderBy(Q::n('time'))
        ->rows(Q::preceding(Q::int(1)), Q::following(Q::int(1))),
)->from(Q::n('observations'));
// SELECT AVG(val) OVER (PARTITION BY subject ORDER BY time ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM observations


// PostgreSQL: json_build_object()
$q = Q::select(
    Q\Func::jsonBuildObject()
        ->prop('id', Q::n('id'))
        ->prop('name', Q::n('name')),
)->from(Q::n('users'));
// SELECT json_build_object('id', id, 'name', name) FROM users

// MySQL / MariaDB: JSON_OBJECT()
$q = Q::select(
    Q\Func::jsonObject()
        ->prop('id', Q::n('id'))
        ->prop('name', Q::n('name')),
)->from(Q::n('users'));
// SELECT JSON_OBJECT('id', id, 'name', name) FROM users


$obj = Q\Func::jsonObject()
    ->prop('id', Q::n('id'))
    ->propIf($


$q = Q::selectJson(
    // PostgreSQL: Q\Func::jsonBuildObject() — MySQL / MariaDB: Q\Func::jsonObject()
    Q\Func::jsonObject()
        ->prop('id', Q::n('authors.author_id'))
        ->prop('name', Q::n('authors.name')),
)
    ->from(Q::n('authors'))
    ->where(Q::n('authors.author_id')->eq(Q::arg(123)));

// The builder is a blueprint — add to the JSON selection later:
$q = $q->applySelectJson(fn ($obj) => $obj->prop('postCount', Q\Func::count(Q::n('posts'))));


// PostgreSQL: json_agg(...)
$q = Q::select(
    Q::n('department'),
    Q\Func::jsonAgg(
        Q\Func::jsonBuildObject()->prop('name', Q::n('name'))->prop('salary', Q::n('salary')),
    )->orderBy(Q::n('name')),
)->from(Q::n('employees'))->groupBy(Q::n('department'));
// SELECT department, json_agg(json_build_object('name', name, 'salary', salary) ORDER BY name)
// FROM employees GROUP BY department

// MySQL / MariaDB: JSON_ARRAYAGG(...); COALESCE with JSON_ARRAY() to avoid NULL on empty sets
$q = Q::select(
    Q::n('department'),
    Q::coalesce(
        Q\Func::jsonArrayAgg(
            Q\Func::jsonObject()->prop('name', Q::n('name'))->prop('salary', Q::n('salary')),
        ),
        Q\Func::jsonArray(),
    ),
)->from(Q::n('employees'))->groupBy(Q::n('department'));
// SELECT department, COALESCE(JSON_ARRAYAGG(JSON_OBJECT('name', name, 'salary', salary)), JSON_ARRAY())
// FROM employees GROUP BY department


use Flowpack\QueryObjectBuilder\MySQL\Q;

// MySQL: the -> and ->> operators
$q = Q::select(Q::n('doc')->jsonExtract(Q::string('$.name')))->from(Q::n('t'));
// SELECT doc -> '$.name' FROM t

// MariaDB: the function form (also works on MySQL)
$q = Q::select(Q\Func::jsonExtract(Q::n('doc'), Q::string('$.name')))->from(Q::n('t'));
// SELECT JSON_EXTRACT(doc, '$.name') FROM t


$q = Q::select(Q::n('jt.id'), Q::n('jt.tag'))
    ->from(Q::n('t'))
    ->from(
        Q::jsonTable(Q::n('t.doc'), '$[*]')->columns(fn ($c) => $c
            ->column('id', 'INT')->path('$.id')
            ->column('ord')->forOrdinality()
            ->nested()->path('$.tags[*]')->columns(fn ($tags) => $tags
                ->column('tag', 'VARCHAR(50)')->path('$'))),
    )->as('jt');


use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$q = Q::select(
    Q\Func::arrayAppend(Q::array(Q::int(1), Q::int(2)), Q::int(3)),
    Q\Func::arrayLength(Q::array(Q::int(1), Q::int(2), Q::int(3)), Q::int(1)),
);
// SELECT array_append(ARRAY[1,2], 3), array_length(ARRAY[1,2,3], 1)

$q = Q::select(Q::n('*'))
    ->from(Q\Func::unnest(Q::array(Q::string('a'), Q::string('b'))))
    ->as('t')->columnAliases('value');
// SELECT * FROM unnest(ARRAY['a','b']) AS t (value)


$q = Q::select(Q::n('name'))
    ->from(Q::n('users'))
    ->where(Q::exists(
        Q::select(Q::int(1))
            ->from(Q::n('posts'))
            ->where(Q::n('posts.user_id')->eq(Q::n('users.id'))),
    ));


$ids = [1, 2, 3];

$q = Q::select(Q::n('username'))
    ->from(Q::n('accounts'))
    ->where(Q::n('id')->in(Q::args(...$ids)));


$q = Q::select(Q::n('id'))->from(Q::n('users'))
    ->where(Q::n('id')->eq(Q::any(
        Q::select(Q::n('user_id'))->from(Q::n('orders')),
    )));


$q = Q::with('recent_orders')->as(
    Q::select(Q::n('*'))
        ->from(Q::n('orders'))
        ->where(Q::n('created_at')->gt(Q::arg('2023-01-01'))),
)
    ->select(Q::n('customer_name'), Q\Func::count(Q::n('*')))
    ->from(Q::n('recent_orders'))
    ->groupBy(Q::n('customer_name'));


use Flowpack\QueryObjectBuilder\MySQL\Q;

$q = Q::with('stale')->as(Q::select(Q::n('id'))->from(Q::n('sessions'))->where(Q::n('expired')->eq(Q::int(1))))
    ->deleteFrom(Q::n('users'))->where(Q::n('id')->in(Q::select(Q::n('id'))->from(Q::n('stale'))));
// WITH stale AS (SELECT id FROM sessions WHERE expired = 1) DELETE FROM users WHERE id IN (SELECT id FROM stale)
// ok against Target::mysql() and Target::mariaDb('12.3'); reported against Target::mariaDb('11.4')


$q = Q::insertInto(Q::n('users'))
    ->columnNames('name', 'email')
    ->values(Q::arg('Jane Doe'), Q::arg('[email protected]'));


// PostgreSQL: INSERT ... ON CONFLICT ... DO UPDATE
use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$q = Q::insertInto(Q::n('distributors'))
    ->columnNames('did', 'dname')
    ->values(Q::int(5), Q::string('Gizmo Transglobal'))
    ->onConflict(Q::n('did'))->doUpdate()
    ->set('dname', Q::n('EXCLUDED.dname'));
// INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal')
// ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname


// MySQL / MariaDB: INSERT ... ON DUPLICATE KEY UPDATE
use Flowpack\QueryObjectBuilder\MySQL\Q;

// MySQL: alias the proposed row with AS new, reference it as new.col
$q = Q::insertInto(Q::n('t'))
    ->columnNames('id', 'hits')->values(Q::arg(1), Q::arg(10))->as('new')
    ->onDuplicateKeyUpdate()->set('hits', Q::n('new.hits'));
// INSERT INTO t (id,hits) VALUES (?,?) AS new ON DUPLICATE KEY UPDATE hits = new.hits

// Portable: the VALUES(col) function works on both engines
$q = Q::insertInto(Q::n('t'))
    ->columnNames('id', 'hits')->values(Q::arg(1), Q::arg(10))
    ->onDuplicateKeyUpdate()->set('hits', Q::values('hits'));
// INSERT INTO t (id,hits) VALUES (?,?) ON DUPLICATE KEY UPDATE hits = VALUES(hits)


// PostgreSQL
use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$q = Q::insertInto(Q::n('users'))
    ->columnNames('name')->values(Q::arg('Jane'))
    ->returning(Q::n('id'), Q::n('created_at'));
// INSERT INTO users (name) VALUES ($1) RETURNING id, created_at


// MariaDB (INSERT / REPLACE / single-table DELETE) — reported against Target::mysql()
use Flowpack\QueryObjectBuilder\MySQL\Q;

$q = Q::insertInto(Q::n('t'))->columnNames('a')->values(Q::arg(1))
    ->returning(Q::n('id'))->as('new_id');
// INSERT INTO t (a) VALUES (?) RETURNING id AS new_id


$q = Q::update(Q::n('films'))
    ->set('kind', Q::arg('Dramatic'))
    ->where(Q::n('kind')->eq(Q::arg('Drama')));


// PostgreSQL
$q = Q::update(Q::n('employees'))->as('e')
    ->set('department_name', Q::n('d.name'))
    ->from(Q::n('departments'))->as('d')
    ->where(Q::n('e.department_id')->eq(Q::n('d.id')));
// UPDATE employees AS e SET department_name = d.name FROM departments AS d WHERE e.department_id = d.id

// MySQL / MariaDB
$q = Q::update(Q::n('t1'))
    ->leftJoin(Q::n('t2'))->on(Q::n('t1.id')->eq(Q::n('t2.id')))
    ->set('t1.col1', Q::n('t2.col1'))
    ->where(Q::n('t2.col2')->isNull());
// UPDATE t1 LEFT JOIN t2 ON t1.id = t2.id SET t1.col1 = t2.col1 WHERE t2.col2 IS NULL


$q = Q::deleteFrom(Q::n('films'))
    ->where(Q::n('kind')->neq(Q::arg('Musical')));


// PostgreSQL
$q = Q::deleteFrom(Q::n('films'))
    ->using(Q::n('producers'))
    ->where(Q::n('producer_id')->eq(Q::n('producers.id')));
// DELETE FROM films USING producers WHERE producer_id = producers.id

// MySQL / MariaDB
$q = Q::deleteFrom(Q::n('t1'))
    ->leftJoin(Q::n('t2'))->on(Q::n('t1.id')->eq(Q::n('t2.id')))
    ->where(Q::n('t2.id')->isNull());
// DELETE t1.* FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL


$q = Q::select(
    Q::n('name'),
    Q::case()
        ->when(Q::n('salary')->lt(Q::int(30000)))->then(Q::string('Low'))
        ->when(Q::n('salary')->lt(Q::int(70000)))->then(Q::string('Medium'))
        ->else(Q::string('High'))
        ->end(),
)->from(Q::n('employees'));


// PostgreSQL: the :: operator via ->cast()
$q = Q::select(Q::n('articles.content')->cast('text'))->from(Q::n('articles'));
// SELECT articles.content::text FROM articles

// MySQL / MariaDB: CAST / CONVERT through the facade
$q = Q::select(Q::cast(Q::n('a'), 'UNSIGNED'), Q::convert(Q::n('a'), 'DECIMAL(10,2)'));
// SELECT CAST(a AS UNSIGNED), CONVERT(a, DECIMAL(10,2))


// PostgreSQL
$q = Q::select(Q\Func::upper(Q::n('name')), Q\Func::extract('year', Q::n('created_at')))
    ->from(Q::n('users'));
// SELECT upper(name), EXTRACT(year FROM created_at) FROM users

// MySQL / MariaDB
$q = Q::select(Q\Func::upper(Q::n('name')), Q\Func::dateAdd(Q::n('created'), Q::interval(Q::int(1), 'DAY')))
    ->from(Q::n('users'));
// SELECT UPPER(name), DATE_ADD(created, INTERVAL 1 DAY) FROM users


use Flowpack\QueryObjectBuilder\MySQL\Q;

// MySQL: FOR SHARE (+ of() / nowait() / skipLocked())
$q = Q::select(Q::n('id'))->from(Q::n('t'))->forShare()->of('t')->nowait();
// SELECT id FROM t FOR SHARE OF t NOWAIT

// MariaDB: LOCK IN SHARE MODE
$q = Q::select(Q::n('id'))->from(Q::n('t'))->lockInShareMode();
// SELECT id FROM t LOCK IN SHARE MODE


$q = Q::select(Q::n('*'))
    ->from(Q::n('users'))
    ->where(Q::and(
        Q::n('name')->like(Q::arg('John%')),
        Q::n('active')->eq(Q::arg(true)),
    ));

[$sql, $args] = Q::build($q)->toSql();
// PostgreSQL: SELECT * FROM users WHERE name LIKE $1 AND active = $2   args: ['John%', true]
// MySQL:      SELECT * FROM users WHERE name LIKE ? AND active = ?     args: ['John%', true]


$q = Q::select(Q::n('*'))
    ->from(Q::n('users'))
    ->where(Q::n('name')->like(Q::bind('search')));

[$sql, $args] = Q::build($q)->withNamedArgs(['search' => 'John%'])->toSql();


  Q::build(Q::n('foo bar'))->toSql();                       // throws: identifier: invalid: foo bar
  [$sql] = Q::build(Q::n('foo bar'))->withoutValidation()->toSql();  // 'foo bar'


use Flowpack\QueryObjectBuilder\PostgreSQL\Q;

$conn = pg_connect('host=localhost dbname=app user=app');

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg(true)));

[$sql, $args] = Q::build($q)->toSql();

$result = pg_query_params($conn, $sql, $args);
while ($row = pg_fetch_assoc($result)) {
    printf("Name: %s, Email: %s\n", $row['name'], $row['email']);
}


use Flowpack\QueryObjectBuilder\MySQL\Q;

$pdo = new PDO('mysql:host=localhost;dbname=app', 'app', 'secret');

$q = Q::select(Q::n('name'), Q::n('email'))
    ->from(Q::n('users'))
    ->where(Q::n('active')->eq(Q::arg(true)));

[$sql, $args] = Q::build($q)->toSql();

$stmt = $pdo->prepare($sql);
$stmt->execute($args);
foreach ($stmt as $row) {
    printf("Name: %s, Email: %s\n", $row['name'], $row['email']);
}


$userName  = Q::n('users.name');
$userEmail = Q::n('users.email');

$q = Q::select($userName, $userEmail)->from(Q::n('users'));


$q = Q::update(Q::n('films'))
    ->set('kind', Q::arg('Dramatic'))
    ->where(Q::n('kind')->eq(Q::arg('Drama')))
    ->applyIf($onlyActive, fn ($q) => $q->where(Q::n('archived')->eq(Q::bool(false))));

sql
-- PostgreSQL
SELECT * FROM employees
WHERE (firstname LIKE $1 OR lastname LIKE $2) AND active = true
-- MySQL / MariaDB
SELECT * FROM employees
WHERE (firstname LIKE ? OR lastname LIKE ?) AND active = TRUE

sql
-- PostgreSQL
SELECT department, count(*) AS n FROM employees
GROUP BY department HAVING count(*) > 5
-- MySQL / MariaDB
SELECT department, COUNT(*) AS n FROM employees
GROUP BY department HAVING COUNT(*) > 5