SQL Builder

The library bases on two ideas:

• an SQL statement expressed with this library should be similar to the statement itself;
• flexible combination of statement components.

Thus, using this library you get simple but, at the same time, powerful tool to build SQL statements in flexible and intuitive way.

useAlephTools\SqlBuilder\PostgreSql\SelectStatement;
useAlephTools\SqlBuilder\Sql\Expression\ConditionalExpression;
$st = (newSelectStatement())
->select([
'u.id',
'u.name',
'company' => 'c.name',
'unreadMessageCount' => (newSelectStatement())
->select('COUNT(*)')
->from('user_messages', 'm')
->where('m.user_id = u.id')
->andWhere('m.read_at IS NULL')
])
->from('users u')
->innerJoin('companies c', 'c.id = u.company_id')
->where('u.deleted_at IS NULL')
->andWhere((newConditionalExpression())
->where('u.roles', 'IN', ['ADMIN', 'RESELLER'])
->orWhere(
(newSelectStatement())
->select('COUNT(*)')
->from('user_contacts uc')
->where('uc.user_id = u.id'),
'>',
5
)
);
// Outputs: // SELECT// u.id, u.name, c.name company,// (SELECT COUNT(*) FROM user_messages m WHERE m.user_id = u.id AND m.read_at IS NULL) unreadMessageCount// FROM users u// INNER JOIN companies c ON c.id = u.company_id// WHERE// u.deleted_at IS NULL AND (// u.roles IN (:p1, :p2) OR // (SELECT COUNT(*) FROM user_contacts uc WHERE uc.user_id = u.id) > :p3// )echo$st->toSql();
// Outputs:// ['p1' => 'ADMIN', 'p2' => 'RESELLER', 'p3' => 5]print_r($st->getParams());
// Executes statement if StatementExecutor is defined, otherwise an exception is thrown$rows = $st->rows();

Installation

composer require alephtools/sqlbuilder