swouters / postgresql-tools
PHP package containing a set of tools to build SQL queries
1.0.3
2024-04-12 13:01 UTC
Requires
- php: >=8.3
Requires (Dev)
- phpunit/phpunit: ^11.0
README
A PHP library to help you to build complexes SQL queries for PostgreSQL.
Installation
composer require swouters/postgresql-tools
Usage
Condition Builder
A tool to build easily complex conditions for SQL queries.
Signature
// Array example $array = [ 'age' => 30, 'name' => ['John', 'Doe'], ]; // Signature public function build( array $array, // The array of conditions string $firstLevelOperator = 'AND', // The operator used between 1st level values (age=30 and ...) string $secondLevelOperator = 'OR', // The operator used if a value is an array (name=John OR name=Doe) array $comparisonOperators = [], // Default is '='. You can set a specific operator for a key array $replaces = [] // Replace the key used for the SQL column ): array
Build condition exemple
use SWouters\PostgresqlTools\ConditionBuilder; // name = "John Doe" PostgreSQLConditionBuilder::buildCondition([ 'name' => 'John Doe', ]); // Firstname=John AND Lastname=Doe PostgreSQLConditionBuilder::buildCondition([ 'firstname' => 'John', 'lastname' => 'Doe', ]); // Firstname=John OR Lastname=Doe PostgreSQLConditionBuilder::buildCondition([ 'firstname' => 'John', 'lastname' => 'Doe', ], 'OR'); // Name=John OR Name=Doe PostgreSQLConditionBuilder::buildCondition([ 'name' => ['John', 'Doe'], ]); // (Name=John OR Name=Doe) AND Age=30 PostgreSQLConditionBuilder::buildCondition([ 'name' => ['John', 'Doe'], 'age' => 30, ]); // status != 'deleted' PostgreSQLConditionBuilder::buildCondition([ 'status' => 'deleted', ], 'AND', 'OR', [ 'status' => '!=' ]); // age=30 AND (name!=John AND name!=Doe) PostgreSQLConditionBuilder::buildCondition([ 'age' => 30, 'name' => ['John', 'Doe'], ], 'AND', 'AND', [ 'name' => '!=' ]); // user.name=John PostgreSQLConditionBuilder::buildCondition([ 'name' => 'John', ], 'AND', 'OR', [], [ 'name' => 'user.name' ]); // created_at > '2024-03-01' AND created_at < '2024-03-02' PostgreSQLConditionBuilder::buildCondition([ 'date_min' => '2024-03-01', 'date_max' => '2024-03-02', ], 'AND', 'OR', [ 'date_min' => '>', 'date_max' => '<', ], [ 'date_min' => 'created_at', 'date_max' => 'created_at', ]);
Execute query (example with Doctrine DBAL)
[$cond, $params] = PostgreSQLConditionBuilder::buildCondition([ 'name' => 'John Doe', ]); $connexion->executeQuery("select * from users WHERE $cond", $params);