dynamonet/query-builder

Fluent, flexible and lightweight SQL Query Builder for PHP 7.1 (or later)

v1.0.1 2020-03-23 23:16 UTC

Requires

• php: ^7.1
• ext-ds: ^1.2
• ext-pdo: ^7.1
• php-ds/php-ds: ^1.2

Requires (Dev)

• phpunit/phpunit: ^7.5

MIT

• Eduardo Rodríguez

README

Fluent and flexible SQL-query-builder, merging in the best ideas from all query-builders out there.

Quick Overview

use Dynamo\QueryBuilder\SqlQueryBuilder as Query;

$users = (new Query)
  ->select('*') // This is the default select
  ->from('users u')
  ->leftJoin('posts p',[
    'p.user_id = u.id',
    'p.trashed' => null
  ]) // translates to: LEFT JOIN posts p ON p.user_id = u.id AND p.trashed IS NULL
  ->where([
    'role' => 'ADMIN', // translates to "role = ?", where "?" will be securely replaced by the PDO layer
    "age > $minAge", // insecure! $minAge will not be prepared! However, we allow this form for convenience
    'age >' => $minAge, // better, and prepareable
    [ 'age', '<=', $maxAge ], // field-operator-value array if you prefer
    'age BETWEEN' => [ $minAge, $maxAge ], // even better
  ], false) // false "OR's" all the previous conditions. Default is true, which will "AND" all the conditions 
  ->fetchAll(); // Fetches all the results

A warning on JOINs with array conditions

Notice that in the previous example, inside the join method, the first condition is 'p.user_id = u.id' instead of the more beatiful form 'p.user_id' => 'u.id', this is because the latter would have be translated to "p.user_id = 'u.id'", matching the posts where user_id is equal to the literal string "u.id" (not what we want).

Working with PDO connections

Create a new PDO instance, and pass it to the SqlQueryBuilder constructor:

use Dynamo\QueryBuilder\SqlQueryBuilder as Query;
....
$pdo = new \PDO('mysql:dbname=mydb;host=localhost;charset=utf8', 'myuser', 'mypass');
$query = (new Query($pdo))

or better yet, set the PDO globally:

Query::setPdo($pdo);
....
$query = (new Query()) // this will use the static PDO instance.

Install

composer require dynamonet/query-builder