30行で作るクエリービルダー

# 環境

* PHP: 7.1.15
* MySQL: 5.5.56 

# 使い方

## SELECT

```php:select.php
<?php
$query = (new QB)->select([
		'u.id',
		'u.name',
		'u.created',
		'p.gender',
		'p.birthday',
		'c2.comment_counts',
		(new QB(
			(new QB)->select([
					'count(*)'
				])
				->from('follows')->as('f')
				->where('f.follow_user_id = u.id')
		))->as('follow_counts')->build()
	])
	->from('users')->as('u')
	->join('profiles')->as('p')
		->on('p.user_id = u.id')
	->left_join(
		(new QB)->select([
			'c.user_id',
			'count(*) as comment_counts',
		])
		->from('comments')->as('c')
		->group_by('user_id')
	)->as('c2')
		->on('c2.user_id = u.id')
	->where('u.id')->in(new QB([1, 2, 3, 4, 5]))
		->or(
			(new QB('u.created < now()'))
				->and('u.name')->like('"a%"')
		)
	->order_by([
		'p.gender',
		'u.id desc',
	])
	->limit([10, 10])
	->build();

echo $query; // SELECT u.id,u.name,u.created,p.gender,p.birthday,c2.comment_counts,(SELECT count(*) FROM follows AS f WHERE f.follow_user_id = u.id) AS follow_counts FROM users AS u JOIN profiles AS p ON p.user_id = u.id LEFT JOIN (SELECT c.user_id,count(*) as comment_counts FROM comments AS c GROUP BY user_id) AS c2 ON c2.user_id = u.id WHERE u.id IN (1,2,3,4,5) OR (u.created < now() AND u.name LIKE "a%") ORDER BY p.gender,u.id desc LIMIT 10,10
```

### フォーマット

```
SELECT
   u.id,
   u.name,
   u.created,
   p.gender,
   p.birthday,
   c2.comment_counts,
   (
      SELECT
         count(*) 
      FROM
         follows AS f 
      WHERE
         f.follow_user_id = u.id
   ) AS follow_counts 
FROM
   users AS u 
   JOIN
      profiles AS p 
      ON p.user_id = u.id 
   LEFT JOIN
      (
         SELECT
            c.user_id,
            count(*) as comment_counts 
         FROM
            comments AS c 
         GROUP BY
            user_id
      ) AS c2 
      ON c2.user_id = u.id 
WHERE
   u.id IN ( 1, 2, 3, 4, 5 )
   OR 
   (
      u.created < now() 
      AND u.name LIKE "a%"
   )
ORDER BY
   p.gender,
   u.id desc
LIMIT
   10,10
```

## INSERT

```php:insert.php
$query = (new QB)->insert_into(
	'users',
	new QB([
		'id',
		'name',
		'created',
	]))
	->values([
		new QB([1, '"1ban"', '"2018-03-31 10:00:01"']),
		new QB([2, '"2ban"', '"2018-03-31 10:00:02"']),
		new QB([3, '"3ban"', '"2018-03-31 10:00:03"']),
	])
	->build();

echo $query; // INSERT INTO users (id,name,created) VALUES (1,"1ban","2018-03-31 10:00:01"),(2,"2ban","2018-03-31 10:00:02"),(3,"3ban","2018-03-31 10:00:03")
```

### フォーマット

```sql
INSERT INTO users
	(id,name,created)
VALUES
	(1,"1ban","2018-03-31 10:00:01"),
	(2,"2ban","2018-03-31 10:00:02"),
	(3,"3ban","2018-03-31 10:00:03")
```

## UPDATE

```php:update.php
<?php
$query = (new QB)->update('users')
	->set([
		'name = :name',
		'created = now()',
	])
	->where('id = :id')
	->build();

echo $query; // UPDATE users SET name = :name,created = now() WHERE id = :id
```

### フォーマット

```sql
UPDATE users
SET 
	name = :name,
	created = now()
WHERE 
	id = :id
```

# クエリービルダー

```php:QB.php
<?php
class QB {
	private $params = [];
	
	public function __construct() {
		$this->params = $this->parse(func_get_args());
	}
	
	public function __call(string $name, array $arguments): QB {
		$this->params = array_merge($this->params, $this->parse(array_merge(array_map('strtoupper', explode('_', $name)), $arguments)));
		return $this;
	}
	
	private function parse(array $arguments): array {
		$params = [];
		foreach ($arguments as $arg) {
			if (is_array($arg)) {
				$params[] = implode(',', $this->parse($arg));
			} else if (is_object($arg) && get_class($arg) === get_class($this)) {
				$params[] = sprintf('(%s)', $arg->build());
			} else {
				$params[] = $arg;
			}
		}
		return $params;
	}
	
	public function build() {
		return implode(' ', $this->params);
	}
}
```

# まとめ

* 関数名は大文字に置換される。また`_`で分解され、最終的にスペースに置換される
* 引数に可変長引数を渡すとスペース区切りで展開される
* 引数に配列を渡すと`,`区切りで展開される
* 引数にクエリービルダーを渡すと自動で展開され`()`で囲われる
* シンタックスチェックしない
* エスケープしない
* 文字列は`''` or `""`で囲う必要がある
* バインドは別でやってね(`PDO`とか)
* `->`,`()`,`''`を書く影響で、ヒアドキュメントよりも記述量が増える
* 実は31行

# 動機とか

Phalconのクエリービルダーで文法的に正しいにも関わらずシンタックスエラーになり、
結局べたに文字連結してクエリーを作る羽目になったことが作ってみようと思った動機
※`LIKE`の手前で`COLLATE`を使うとエラーになる(ver3.1.2)

文字連結に拒絶反応を覚える人以外にはお勧めしない

## 追記

Phalconでも拡張クラスを使えば独自にパース出来ると指摘頂いたので、Phalconで同じ悩みを抱いている人は大人しくそちらを使おう。（エスケープとか面倒みてくれるので）
ドキュメント大事。

* https://docs.phalconphp.com/3.4/en/db-layer

Implementing your own dialectsの項を参照。