php-objects/query-builder

Query Builder - Making SQL composing easier

Installs: 146

Dependents: 1

Stars: 7

Watchers: 3

Forks: 3

Open Issues: 1

Language: PHP

0.9.4 2014-07-23 01:28 UTC

README

Query Builder for easing the SQL composing

Important notice: Deprecated in favor of Koine/QueryBuilder

This project was deprecated in favor of Koine/QueryBuilder

Build Status Coverage Status Code Climate Scrutinizer Code Quality Latest Stable Version Total Downloads Latest Unstable Version License

Installing

Installing via Composer

Append the lib to your requirements key in your composer.json.

{
    // composer.json
    // [..]
    require: {
        // append this line to your requirements
        "php-objects/query-builder": "dev-master"
    }
}

Alternative install

Usage

SELECT

This is an example of select query.

$fields = array('u.name AS name', 'r.name AS role');

// Selecting via factory
$select = PO\QueryBuilder::factorySelect($fields);

// Selecting via the select method
$select = PO\QueryBuilder::factorySelect()
    ->select($fields);

// or alternatively
$select = new PO\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

// Using the factory
$insert = PO\QueryBuilder::insert();

// Or alternatively
$insert = new PO\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

$update = PO\QueryBuilder::update('users');

// or
$update = new PO\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@abc.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.

  1. Fork it
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'Add some feature')
  4. Push to the branch (git push origin my-new-feature)
  5. 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

Lincense

MIT

Authors