Query Scribe

Provides a convenient object syntax to build SQL queries. Compiles the queries to an SQL text with values for binding. Doesn't perform queries to database.

$query = (newQuery)
->from('posts')
->where('level', '>', 3)
->whereIn('category_id', function ($query) {
$query
->addSelect('id')
->from('categories')
->where('categories.name', 'Interesting');
})
->where(newRaw('MONTH(date)'), 4)
->orderBy('date', 'desc')
->limit(10);
$prefixer = newTablePrefixer('demo_');
$grammar = newMySQLGrammar();
$compiled = $grammar->compile($query->apply($prefixer));
echo$compiled->getSQL();
/* SELECT * FROM `demo_posts` WHERE `level` > ? AND `category_id` IN ( SELECT `id` FROM `demo_categories` WHERE `demo_categories`.`name` = ? ) AND (MONTH(date)) = ? ORDER BY `date` DESC LIMIT ? */echo$compiled->getBindings();
/* [3, 'Interesting', 4, 10] */

To perform compiled queries to a database, use a database connector like PDO, MicroDB or DBAL or use a ready database abstraction like MiniDB or Wired.

Key features:

• The builder has a single responsibility: build SQL.
• Designed for further extension. You may build a database tool or an ORM on top of it without major problems. Examples will come soon.
• Very flexible. You can pass a raw SQL or a subquery almost everywhere (see the PHPDoc comments in the code to know where you can pass them).
• Smart table prefixes which consider table aliases (don't work in raw expressions).
• All the values go to bindings, even from subqueries.
• No dependencies. Requires only PHP ≥ 7.

Supported SQL dialects:

• MySQL
• SQLite
• SQL Server (not fully supported)
• Maybe any other, didn't test it

If you need a dialect support please extend the CommonGrammar class and make a pull request.

Documentation

The documentation is available at queryscribe.readthedocs.io.

Also all the classes, methods and properties has a PHPDoc comment in the code.

Versions compatibility

The project follows the Semantic Versioning.

License

MIT. See the LICENSE file for details.