rog-works/qb

simple query builder

dev-master 2018-04-01 07:37 UTC

Requires (Dev)

• phpunit/phpunit: *

MIT

• rog-works

README

使い方

SELECT

<?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

$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")

フォーマット

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
$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

フォーマット

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

ビルドフィルタリング

<?php
$conditions = [];
$query = (new QB)->select([
		'u.id',
		'u.name',
		'u.created',
	])
	->from('users')->as('u')
	->join('profiles')->as('p')
		->on('p.user_id = u.id')
	->where('u.deleted = 0')
		->and('u.name LIKE ":keyword"')
		->and('p.gender = :gender')
	->build([
		'where.and' => isset($conditions['keyword'])
	]);

echo $query; // SELECT u.id,u.name,u.created FROM users AS u JOIN profiles AS p ON p.user_id = u.id WHERE u.deleted = 0 AND p.gender = :gender

フォーマット

SELECT
   u.id,
   u.name,
   u.created 
FROM
   users AS u 
   JOIN
      profiles AS p 
      ON p.user_id = u.id 
WHERE
   u.deleted = 0 
   AND p.gender = :gender

まとめ

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

テスト

事前作業

$ cd /path/to/workspace

# composerインストール
$ curl -sS https://getcomposer.org/installer | php

# composerに実行権限を付与
$ mv composer.phar /usr/local/bin/composer
$ chmod +x /usr/local/bin/composer

# インストール確認
$ composer -V

# 依存パッケージをインストール
$ composer install --dev

テスト実行

$ vendor/bin/phpunit