ridaamirini/phpquerybuilder

An fast, small SQL Builder based on FluentPDO

0.1.2 2017-08-31 09:14 UTC

Requires

• php: ^7.0
• fpdo/fluentpdo: 1.1.*
• mockery/mockery: 0.9.9
• symfony/console: 3.3.8

Requires (Dev)

• doctrine/instantiator: ^1.0.2
• phpunit/phpunit: ^6.3

MIT

• Rida Amirini

README

An fast, small SQL Builder based on FluentPDO

Features

• Simply create queries step by step
• Smart join builder
• Build SELECT, INSERT, UPDATE & DELETE queries
• Small and fast
• Type hinting with code completion in smart IDEs
• Requires PHP 7.0+
• PHP 5.6+ compatibility coming soon

Install

Composer

Add in your composer.json:

"require": {
	...
	"ridaamirini/phpquerybuilder": "^0.1.1"
}

then update your dependencies with composer update.

OR

For the latest dev version

composer require ridaamirini/phpquerybuilder:dev-master

Start usage

PHPQueryBuilder is easy to use:

QueryBuilder::create()->select('article')
                      ->where('published_at > ?', 1)
		      ->orderBy('published_at DESC')
		      ->limit(5)
		      ->getQuery();

output query is:

SELECT article.* FROM article WHERE published_at > 1 ORDER BY published_at DESC LIMIT 5

Smart join builder (how to build queries)

If you want to join table you can use full sql join syntax. For example we would like to show list of articles with author name:

$query = QueryBuilder::create()->select('article')
                               ->leftJoin('user ON user.id = article.user_id')
                               ->select('user.name')
                               ->getQuery();

It was not so much smart, was it? ;-) If your database uses convention for primary and foreign key names, you can write only:

$query = QueryBuilder::create()->select('article')->leftJoin('user')->select('user.name')->getQuery();

Smarter? May be. but best practice how to write joins is not to write any joins ;-)

$query = QueryBuilder::create()->select('article')->select('user.name')->getQuery();

All three commands create same query:

SELECT article.*, user.name FROM article LEFT JOIN user ON user.id = article.user_id

Simple CRUD Query Examples

SELECT

$query = QueryCollection::create()->select('article')->where('id', 1)->getQuery();
// or shortly if you select one row by primary key
$query = QueryCollection::create()->from('user', 1)->getQuery();

INSERT

$values = ['title' => 'article 1', 'content' => 'content 1'];
$query = QueryCollection::create()->insert('article')->values($values)->getQuery();
// or shortly
$query = QueryCollection::create()->insert('article', $values)->getQuery();

UPDATE

$set = ['published_at' => new QueryBuilderLiteral('NOW()')];
$query = QueryCollection::create()->update('article')->set($set)->where('id', 1)->getQuery();
// or shortly if you update one row by primary key
$query = QueryCollection::create()->update('article', $set, 1)->getQuery();

DELETE

$query = QueryCollection::create()->delete('article')->where('id', 1)->getQuery();
// or shortly if you delete one row by primary key
$query = QueryCollection::create()->delete('article', 1)->getQuery();

CRUD QueryCollection Examples and PHPQueryBuilder CLI

Init config file

$ vendor/bin/phpqb init

Insert your values (phpqb.json)

{
  "folder": [
    {
      "from": "./test",
      "to": "./db"
    }
  ],
  "files": [
    {
      "from": "./test.php",
      "to": "./db_2"
    }
  ],
  "excludes": [
    {
      "path": "./test/toExclude.php"
    },
    {
      "path": "./test/test_exclude"
    }
  ],
  "defaultDestination": "./path/to/default"
}

Run with config file

$ vendor/bin/phpqb dump
	OR
$ vendor/bin/phpqb dump --config

Run with path

$ vendor/bin/phpqb dump --collection /path/to/collection.php > collection.sql
	OR
$ vendor/bin/phpqb dump -c /path/to/collection.php --filename /path/to/output/output.sql

Example File

<?php

use App\Builder\QueryBuilder;
use App\Schema\QueryCollection;

$collection = new QueryCollection();

//SELECT
QueryCollection::create()->select('article')->where('id', 1)->collect($collection);

//INSERT
$values = ['title' => 'article 1', 'content' => 'content 1'];
$query = QueryCollection::create()->insert('article')->values($values)->collect($collection);

//UPDATE
$set = ['published_at' => 'yesterday'];
$query = QueryCollection::create()->update('article')->set($set)->where('id', 1)->getQuery();

//DELETE
QueryCollection::create()->delete('article')->where('id', 1)->collect($collection);

return $collection;

TODO

• Standalone QueryBuilder wihtout FluentPDO
• PHP 5.6+ compatibility
• phpqb.json JSON lint
• Command phpqb dump -c ... -f allow directory
• Add Unit Tests
• First release

Licence