PHP download

PHP code example of qstart-soft / query-builder

1. Go to this page and download the library: Download qstart-soft/query-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/ */

qstart-soft / query-builder example snippets


use Qstart\Db\QueryBuilder\Query;

$select = Query::select();
$insert = Query::insert();
$update = Query::update();
$delete = Query::delete();


use Qstart\Db\QueryBuilder\Query;

$query = Query::select();
// ...
$builder = $query->getQueryBuilder()
// If necessary, you can set the dialect for the query builder
$builder->setDialect(DialectSQL::POSTGRESQL);

$expr = $builder->build();
// Binding parameters
$params = $expr->getParams();
// Sql string
$sql = $expr->getExpression();


use Qstart\Db\QueryBuilder\DML\Expression\Expr;

$expr = new Expr('created_at > now()');

// This is the sql expression
$expression = $expr->getExpression();
// This is the binding params
$params = $expr->getParams();


use Qstart\Db\QueryBuilder\DML\Expression\BetweenExpr;
use Qstart\Db\QueryBuilder\DML\Expression\CompareExpr;
use Qstart\Db\QueryBuilder\DML\Expression\InExpr;

$expr = new CompareExpr('!=', 'id', 20);
$expression = $expr->getExpression();
// id != :v1
$params = $expr->getParams();
// ['v1' => 20]

$expr = new BetweenExpr('id', 10, 20);
$expression = $expr->getExpression();
// id BETWEEN :v1 AND :v2
$params = $expr->getParams();
// ['v1' => 10, 'v2' => 20]

$expr = new InExpr('id', [10, 20], true);
$expression = $expr->getExpression();
// id NOT IN (:v3, :v4)
$params = $expr->getParams();
// ['v3' => 10, 'v4' => 20]


use Qstart\Db\QueryBuilder\DML\Expression\Expr;
use Qstart\Db\QueryBuilder\Helper\DialectSQL;

$expr = new Expr('created_at > now()');

$expressionCh = $expr->getExpression(DialectSQL::CLICKHOUSE);
$expressionPg = $expr->getExpression(DialectSQL::POSTGRESQL);
$params = $expr->getParams();



use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\Expr;

$query = Query::select();

// Table name only
$query->from('user');
$query->from(['user']);
// Table name with alias
$query->from('user u');
$query->from(['u' => 'user']);

// You can also pass an expression or another query instead of the table name
$query->from(['u' => Query::select()->from('user')]);
// Result: SELECT * FROM (SELECT * FROM user) AS u
$query->from(['u' => new Expr("(SELECT * FROM user)")]);
// Result: SELECT * FROM (SELECT * FROM user) AS u
// Same thing without alias
$query->from(Query::select()->from('user'));
$query->from(new Expr("SELECT * FROM user"));

// You can also set several tables in any available format
$query->from(['u' => 'user', 's' => 'session']);
// Result: SELECT * FROM user AS u, session AS s


use Qstart\Db\QueryBuilder\Query;

$query = Query::select()->from(['user'])->alias('u');
// Result: SELECT * FROM user AS u

$query = Query::select()->from(['u' => 'user'])->alias('t');
// Result: SELECT * FROM user AS t

$query = Query::select()->from(['u' => 'user', "s" => "session"])->alias('t');
// Result: SELECT * FROM user AS t, session AS s


use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\Expr;

$conditions = ['user_id' => 10, 'session_id' => 101];
// user_id = 10 AND session_id = 101
$conditions = ['user_id' => [10, 20], 'session_id' => 101];
// user_id IN (10, 20) AND session_id = 101
$conditions = ['user_id' => Query::select()->select('id')->from('user'), 'session_id' => 101];
// user_id IN (SELECT id FROM user) AND session_id = 101
$conditions = ['user_id' => new Expr("LEAST(10, 20)"), 'session_id' => 101];
// user_id = LEAST(10, 20) AND session_id = 101

Query::select()->where($conditions);


use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\Expr;
use Qstart\Db\QueryBuilder\DML\Expression\InExpr;

$conditions = new Expr('created_at >= now()');
// created_at >= now()
$conditions = new InExpr('id', [10, 20], true);
// id NOT IN (10, 20)

Query::select()->where($conditions);


use Qstart\Db\QueryBuilder\Query;

$conditions = 'created_at >= now()'
Query::select()->from('user')->where($conditions);
// SELECT * FROM user WHERE created_at >= now()

$conditions = [
    'and',
    'created_at >= now()',
    ['id' => 2]
]
Query::select()->from('user')->where($conditions);
// SELECT * FROM user WHERE (created_at >= now()) AND (id = 2)


use Qstart\Db\QueryBuilder\DML\Expression\Expr;
use Qstart\Db\QueryBuilder\DML\Expression\InExpr;

$conditions = ['and', ['user_id' => 10, 'session_id' => 101], new Expr("id = LEAST(10, 20)")];
// (user_id = 10 AND session_id = 101) AND (id = LEAST(10, 20))
$conditions = ['or', ['user_id' => 10, 'session_id' => 101], new Expr("id = LEAST(10, 20)")];
// (user_id = 10 AND session_id = 101) OR (id = LEAST(10, 20))
$conditions = ['not', ['user_id' => 10, 'session_id' => 101], new Expr("id = LEAST(10, 20)")];
// NOT ((user_id = 10 AND session_id = 101) AND (id = LEAST(10, 20)))

// Lets combine it
$conditions = [
    'and',
    ['or', ['id' => 2], ['id' => 3]],
    ['not', ['session_id' => 10]]
];
// ((id = 2) OR (id = 3)) AND (NOT (session_id = 10))


use Qstart\Db\QueryBuilder\Query;

$query = Query::select()->where(['and', $condition1, $condition2]);
// Its equal with:
$query = Query::select()->where($condition1)->andWhere($condition2);

$query = Query::select()->where(['or', $condition1, $condition2]);
// Its equal with:
$query = Query::select()->where($condition1)->orWhere($condition2);


use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\Expr;

// 1. String format
Query::select()->select('id, name, surname');

// 2. Array alias-value format
Query::select()
    ->select([
        'id',
        'name' => "name || ' ' || surname",
        new Expr('created_at::DATE as date'),
        'cnt' => Query::select()->select('COUNT(*)')->from('user')
    ]);
// Result: SELECT id, name || ' ' || surname AS name, created_at::DATE as date, (SELECT COUNT(*) FROM user) AS cnt

// 3. Add values
Query::select()->select('id, name')->addSelect(new Expr('created_at::DATE as date'));
// Result: SELECT id, name, created_at::DATE as date

// 4. Reset values
Query::select()->select(null);

// 4. Distinct
Query::select()->select('id, name')->distinct(true);
// // Result: SELECT DISTINCT id, name


use Qstart\Db\QueryBuilder\Query;

Query::select()->where(['id' => 2])->andWhere(['user_id' => 3]);


use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\CompareExpr;

Query::select()->filterWhere(['id' => null])->andFilterWhere(new CompareExpr('>=', 'id', null));
// Result will be without WHERE clause


use Qstart\Db\QueryBuilder\Query;

// 1. String format
Query::select()->from('user')->groupBy('id, name');
// SELECT * FROM user GROUP BY id, name

// 2. Array format
Query::select()->from('user')->groupBy(['id', 'name']);
// SELECT * FROM user GROUP BY id, name

// 3. Expression/Query format
Query::select()->from('user')->groupBy(new Expr('id, name'));
// SELECT * FROM user GROUP BY id, name

// 4. Add values
Query::select()->from('user')->groupBy('id, name')->addGroupBy(new Expr('created_at::DATE'));
// Result: SELECT * FROM user GROUP BY  id, name, created_at::DATE

// 5. Reset values
Query::select()->groupBy(null);


use Qstart\Db\QueryBuilder\Query;

// 1. String format
Query::select()->from('user')->orderBy('id, name');
// SELECT * FROM user ORDER BY id, name

// 2. Array format
Query::select()->from('user')->orderBy(['id' => SORT_ASC, 'name' => SORT_DESC]);
// SELECT * FROM user ORDER BY id ASC, name DESC

// 3. Expression/Query format
Query::select()->from('user')->orderBy(new Expr('id ASC, name DESC'));
// SELECT * FROM user ORDER BY id ASC, name DESC

// 4. Mix format
Query::select()->from('user')->orderBy(['id' => SORT_ASC, 'name' => SORT_DESC, new Expr('created_at::DATE DESC')]);
// SELECT * FROM user ORDER BY id ASC, name DESC, created_at::DATE DESC

// 5. Add values
Query::select()->from('user')->orderBy('id DESC')->addOrderBy(new Expr('created_at::DATE DESC'));
// Result: SELECT * FROM user ORDER BY id DESC, created_at::DATE DESC

// 6. Reset values
Query::select()->orderBy(null);


use Qstart\Db\QueryBuilder\Query;

// 1. Integer
Query::select()->from('user')->offset(10);
// SELECT * FROM user OFFSET 10

// 2. Expression
Query::select()->from('user')->offset(new Expr("length('SPARK')"));
// SELECT * FROM user OFFSET length('SPARK')

// 2. Reset value
Query::select()->from('user')->offset(null);


use Qstart\Db\QueryBuilder\Query;

// 1. Integer
Query::select()->from('user')->limit(10);
// SELECT * FROM user LIMIT 10

// 2. Expression
Query::select()->from('user')->limit(new Expr("length('SPARK')"));
// SELECT * FROM user LIMIT length('SPARK')

// 2. Reset value
Query::select()->from('user')->limit(null);


use Qstart\Db\QueryBuilder\Query;

Query::select()->from('user u')->leftJoin('session s', 'u.id = s.user_id');
// Result: SELECT * FROM user u LEFT JOIN session s ON u.id = s.user_id


use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\Expr;

$query = Query::select()
    ->from('table t')->where(['user_id' => 2])->orderBy('created_at')
    ->union(Query::select()->from('table2 t2')->where(['user_id' => 12])->orderBy('id'), true)
    ->union(Query::select()->from('table3 t3')->where(['user_id' => 22]))
    ->union(new Expr('SELECT * FROM table4 t4 WHERE user_id = :id', ['id' => 32]))
    ->union('SELECT * FROM table5 t5', true);

// Result:
<<<SQL
SELECT * FROM table t WHERE user_id = :v1
UNION ALL
SELECT * FROM table2 t2 WHERE user_id = :v2
UNION
SELECT * FROM table3 t3 WHERE user_id = :v3
UNION
SELECT * FROM table4 t4 WHERE user_id = :id
UNION ALL
SELECT * FROM table5 t5
ORDER BY created_at, id
SQL;


use Qstart\Db\QueryBuilder\Query;

$query = Query::insert()->into('user')->addValues(['name' => 'John', 'surname' => 'Jonson']);
// Result: INSERT INTO user (name, surname) VALUES (:v1, :v2)

$query->setStartOfQuery('INSERT IGNORE INTO')->setEndOfQuery('RETURNING id');
// Result: INSERT IGNORE INTO user (name, surname) VALUES (:v1, :v2) RETURNING id

$query = Query::insert()->into('user')->addMultipleValues([['name' => 'John', 'surname' => 'Jonson'], ['surname' => 'Nelson', 'name' => 'Mike']]);
// Result: INSERT INTO user (name, surname) VALUES (:v1, :v2), (:v4, :v3)


use Qstart\Db\QueryBuilder\Query;

$query = Query::update()->setTable('user');
// Result: UPDATE user


use Qstart\Db\QueryBuilder\Query;
use Qstart\Db\QueryBuilder\DML\Expression\Expr;

// 1. Format of attributes
$query = Query::update()
    ->setTable('"user"')
    ->set([
        'name' => 'John', // name=:v19,
        'age' => new Expr('18 + 10'), // age=18 + 10,
        'last_session_at' => Query::select()->from('session')->select('MAX(created_at)')->where(['user_id' => 123])
    ])
    ->addSet("status='active'") // status='active',
    ->addSet(new Expr('is_active = TRUE')); // is_active = TRUE,

// Result: UPDATE "user" SET name = :v1, age = 18 + 10, last_session_at = (SELECT MAX(created_at) FROM session WHERE user_id = :v2), status='active', is_active = TRUE

// 2. Reset SET clause
$query = Query::update()->setTable('"user"')->set(null);


use Qstart\Db\QueryBuilder\Query;

$query = Query::update()->setTable('user')->joinFrom('session');
// Result: UPDATE user FROM session


use Qstart\Db\QueryBuilder\Query;

$query = Query::update()
    ->setTable('user')
    ->setStartOfQuery('UPDATE ONLY')
    ->setEndOfQuery('RETURNING id');
// Result: UPDATE ONLY user RETURNING id


use Qstart\Db\QueryBuilder\Query;

$query = Query::delete()->from('user');
// Result: DELETE FROM user


use Qstart\Db\QueryBuilder\Query;

$query = Query::delete()->from('user')->using('session');
// Result: DELETE FROM user USING session


use Qstart\Db\QueryBuilder\Query;

$query = Query::delete()
    ->from('user')
    ->setStartOfQuery('DELETE FROM ONLY')
    ->setEndOfQuery('RETURNING id');
// Result: DELETE FROM ONLY user RETURNING id