koine / query-builder
Query Builder - Making SQL composing easier
0.9.7
2014-09-15 14:43 UTC
Requires (Dev)
- phpunit/phpunit: 3.7.*
- satooshi/php-coveralls: dev-master
This package is not auto-updated.
Last update: 2024-10-26 16:31:10 UTC
README
Query Builder for easing the SQL composing
Code information:
Package information:
Installing
Installing via Composer
Append the lib to your requirements key in your composer.json.
{ // composer.json // [..] require: { // append this line to your requirements "koine/query-builder": "dev-master" } }
Alternative install
- Learn composer. You should not be looking for an alternative install. It is worth the time. Trust me ;-)
- Follow this set of instructions
Usage
SELECT
This is an example of select query.
- Applies limit
- Applies where
- Applies orderBy
- Applies groupBy
- Applies placeholders
$fields = array('u.name AS name', 'r.name AS role'); // Selecting via factory $select = Koine\QueryBuilder::factorySelect($fields); // Selecting via the select method $select = Koine\QueryBuilder::factorySelect() ->select($fields); // or alternatively $select = new Koine\QueryBuilder\Statements\Select(); $select->select($fields); // From $select->from('users u'); // Adding joins $select->innerJoin('roles r', 'u.id = r.user_id'); $select->toSql(); // SELECT u.name AS name, r.name AS role // FROM users u INNER JOIN roles r ON u.idi = r.user_id
INSERT
- Applies placeholders
// Using the factory $insert = Koine\QueryBuilder::insert(); // Or alternatively $insert = new Koine\QueryBuilder\Statements\Insert(); $insert->into('users')->values(array( 'name' => 'Jon Doe', 'email' => 'jon@doe.com' )); $insert->toSql(); // INSERT INTO users (name, email) VALUES ('Jon Doe', 'jon@doe.com');
UPDATE
- Applies limit
- Applies where
- Applies orderBy
- Applies groupBy
- Applies placeholders
$update = Koine\QueryBuilder::update('users'); // or $update = new Koine\QueryBuilder\Statements\Update; $update->table('users'); // setting values and conditions $update->set(array( 'enabled' => 1 ))->where('email', ':email'); $update->toSql(array( 'email' => 'admin@email.com' )); // UPDATE users SET enabled = 1 WHERE email = 'admin@email.com'
DELETE
TODO: Implement
WHERE
Every time a where()
method is called, the condition is added to the query.
// method signature $query->where($field, $value, $operator); // or $query->where($condition); // or $query->where(array( array($field, $value, $operator), array($condition), )); // Below some valid examples: $query->where('email', 'admin@abc.com'); // WHERE email = 'admin@abc.com' $query->where('email', 'admin@abc.com', '<>'); // WHERE email <> "admin@abc.com" $query->where('email', '%@google.com', 'LIKE'); // WHERE email LIKE "%@google.com" $query->where('age', 20); // WHERE age = 20 $query->where('code', 001); // WHERE code = 001 $query->where('code', array('value' => '001')); // WHERE code = '001' $query->where('(code = 1 OR code = 2)')); // WHERE (code = 1 OR code = 2) // multiple conditioins, one method call $query->where(array( array('email', 'admin@abc.com', '<>'), array('email', '%@google.com', 'LIKE'), array('age', 20), array('(code = 1 OR code = 2)'), array('hash', array('value' => 'SOMEFUNCTION()')), )); // WHERE condition 1 AND condition 2..
ORDER BY
$query->orderBy('name DESC'); // or $query->orderBy(array('name DESC', 'age ASC'));
GROUP BY
$query->groupBy('a, b, c'); // or $query->groupBy(array('a', 'b', 'b'));
LIMIT
$query->limit(2); $query->limit(2, 1);
Using placeholders
Placeholders are a good way for building your queries when you don't know what values are going to be used (because they depend on the result of a query yet to be executed, for instance).
$insert->into('users')->values(array( 'name' => ':name', 'email' => ':email' )); $insert->toSql(array( 'name' => 'Jon Doe', 'email' => 'jon@doe.com' )); // INSERT INTO users (name, email) VALUES ('Jon Doe', 'jon@doe.com');
Issues/Features proposals
Here is the issue tracker.
Contributing
Only TDD code will be accepted. Please follow the PSR-2 code standard.
- Fork it
- Create your feature branch (
git checkout -b my-new-feature
) - Commit your changes (
git commit -am 'Add some feature'
) - Push to the branch (
git push origin my-new-feature
) - Create new Pull Request
How to run the tests:
phpunit --configuration tests/phpunit.xml
To check the code standard run:
phpcs --standard=PSR2 lib
phpcs --standard=PSR2 tests
# alternatively
./bin/travis/run_phpcs.sh