QueryBuilder

Requirements

• PHP 8+ (Should work with 7+, but haven't tested it).
• Psr\Container\ContainerInterface implementation like php-di/php-di.

Installation

composer.json

{
    ...
    "repositories": [
        {
            "type": "git",
            "url": "https://git.provm.cl/ProVM/query_builder.git"
        }
    ],
    "require": {
        ...
        "provm/query_builder": "^1.0",
        ...
    },
    ...
}

Setup

$container->set(ProVM\Concept\Database\Query\Select::class, function(Psr\Container\ContainerInterface $container) {
    return $container->get(ProVM\Database\Query\MySQL\Select::class);
});
$container->set(ProVM\Concept\Database\Query\Insert::class, function(Psr\Container\ContainerInterface $container) {
    return $container->get(ProVM\Database\Query\MySQL\Insert::class);
});
$container->set(ProVM\Concept\Database\Query\Update::class, function(Psr\Container\ContainerInterface $container) {
    return $container->get(ProVM\Database\Query\MySQL\Update::class);
});
$container->set(ProVM\Concept\Database\Query\Delete::class, function(Psr\Container\ContainerInterface $container) {
    return $container->get(ProVM\Database\Query\MySQL\Delete::class);
});

Usage

QueryBuilder

include_once 'vendor/autoload.php';
$qb = new QueryBuilder();

$query = $qb->select(<*columns>);
$query = $qb->insert(<table>);
$query = $qb->update(<table>);
$query = $qb->delete(<table>);

Queries

Select

SELECT <columns> FROM <table>[ JOIN <table2> ON <table>.<column_1> = <table2>.<column_2>][ WHERE <expr1>[ AND|OR <expr2>]][ GROUP BY <column1>[, <column2>]][ HAVING <expr1>[ AND|OR <expr2>]][ ORDER BY <column1> ASC|DESC[, <column2> ASC|DESC]]

Mysql also include [ LIMIT <limit>[ OFFSET <offset>]]

$query = $qb->select(); // use '*' for columns
or
$query = $qb->select()->select(['id', 'column1',]);
or
$query = $qb->select(['id', 'column1',]);

$query->from('table1');
$query->joins([['table2', 'table1.column1 = table2.column21'],]);
$query->where(['table2.column22 = 10',]);
$query->groupBy(['table1.column1',]);
$query->having(['table1.column1 < 10',]);
$query->orderBy(['table2.column22',]);
$query->limit(10, 10);

Insert

INSERT INTO <table>[ (<columns>)] with two options <select query> or VALUES (<values>)

$query = $qb->insert('table');

$query->columns(['column1',]);
$query->values(['value1',];
or
$query->select($select_query);

Update

UPDATE <table> SET <column1> = <value1>[, <column2> = <value2>][ WHERE <expr1>[ AND|OR <expr2>]]

$query = $qb->update('table');
$query->set([['column1', 10],]); or $query->set([['column' => 'column1', 'value' => 10],]);
$query->where(['column2 = 10', ]);

Delete

DELETE FROM <table> WHERE <expr1>[ AND|OR <expr2>]

$query = $qb->delete('table');
$query->where(['column1 = 10',]);

Pass the query to a string

$str = $query->build();
or
$str = "{$query}";

TODO

Implement other databases