mouf/magic-query

A very clever library to help you with SQL: generate prepared statements with a variable number of parameters, automatically writes joins... and much more!

, _(*1)

What is Magic-query?

Magic-query is a PHP library that helps you work with complex SQL queries., _(*2)

It comes with 3 great features:, _(*3)

• MagicParameters: it helps you work with SQL queries that require a variable number of parameters.
• MagicJoin: it writes JOINs for you!
• MagicTwig: use Twig templating in your SQL queries

Installation

Simply use the composer package:, _(*4)

{
    "require": {
        "mouf/magic-query": "^1.2"
    },
    "minimum-stability": "dev",
    "prefer-stable": true
}

, _(*5)

Automatically discard unused parameters with MagicParameters

Just write the query with all possible parameters., _(*6)

use Mouf\Database\MagicQuery;

$sql = "SELECT * FROM users WHERE name LIKE :name AND country LIKE :country";

// Get a MagicQuery object.
$magicQuery = new MagicQuery();

// Let's pass only the "name" parameter
$result = $magicQuery->build($sql, [ "name" => "%John%" ]);
// $result = SELECT * FROM users WHERE name LIKE '%John%'
// Did you notice how the bit about the country simply vanished?

// Let's pass no parameter at all!
$result2 = $magicQuery->build($sql, []);
// $result2 = SELECT * FROM users
// The whole WHERE condition disappeared because it is not needed anymore!

Curious to know how this work? Check out the parameters guide!, _(*7)

, _(*8)

Automatically guess JOINs with MagicJoin!

Fed up of writing joins in SQL? Let MagicQuery do the work for you!, _(*9)

Seriously? Yes! All you have to do is:, _(*10)

• Pass a Doctrine DBAL connection to MagicQuery's constructor. MagicQuery will analyze your schema.
• In your SQL query, replace the tables with magicjoin(start_table)
• For each column of your query, use the complete name ([table_name].[column_name] instead of [column_name] alone)

Let's assume your database schema is:, _(*11)

, _(*12)

Using MagicJoin, you can write this SQL query:, _(*13)

SELECT users.* FROM MAGICJOIN(users) WHERE groups.name = 'Admins' AND country.name='France';

and it will automatically be transformed into this:, _(*14)

SELECT users.* FROM users 
    LEFT JOIN users_groups ON users.user_id = users_groups.user_id
    LEFT JOIN groups ON groups.group_id = users_groups.group_id
    LEFT JOIN country ON country.country_id = users.country_id
WHERE groups.name = 'Admins' AND country.name='France';

And the code is so simple!, _(*15)

use Mouf\Database\MagicQuery;

$sql = "SELECT users.* FROM MAGICJOIN(users) WHERE groups.name = 'Admins' AND country.name='France'";

// Get a MagicQuery object.
// $conn is a Doctrine DBAL connection.
$magicQuery = new MagicQuery($conn);

$completeSql = $magicQuery->build($sql);
// $completeSql contains the complete SQL request, with all joins.

Want to know more? Check out the MagicJoin guide!, _(*16)

, _(*17)

Use Twig templating in your SQL queries!

Discarding unused parameters and auto-joining keys is not enough? You have very specific needs? Say hello to Twig integration!, _(*18)

Using Twig integration, you can directly add Twig conditions right into your SQL., _(*19)

use Mouf\Database\MagicQuery;

$sql = "SELECT users.* FROM users {% if isAdmin %} WHERE users.admin = 1 {% endif %}";

$magicQuery = new MagicQuery();
// By default, Twig integration is disabled. You need to enable it.
$magicQuery->setEnableTwig(true);

$completeSql = $magicQuery->build($sql, ['isAdmin' => true]);
// Parameters are passed to the Twig SQL query, and the SQL query is returned.

Want to know more? Check out the MagicTwig guide!, _(*20)

Is it a MySQL only tool?

No. By default, your SQL is parsed and then rewritten using the MySQL dialect, but you use any kind of dialect known by Doctrine DBAL. Magic-query optionally uses Doctrine DBAL. You can pass a Connection object as the first parameter of the MagicQuery constructor. Magic-query will then use the matching dialect., _(*21)

For instance:, _(*22)

$config = new \Doctrine\DBAL\Configuration();
$connectionParams = array(
    'url' => 'sqlite:///somedb.sqlite',
);
$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);

$magicQuery = new \Mouf\Database\MagicQuery($conn);

Also, if you have no connection to your database configured but you want to generate SQL in some specific dialect, you can instead set the DBAL database platform used:, _(*23)

$magicQuery->setOutputDialect(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform());
$magicQuery = new \Mouf\Database\MagicQuery();

What about performances?

MagicQuery does a lot to your query. It will parse it, render it internally as a tree of SQL nodes, etc... This processing is time consuming. So you should definitely consider using a cache system. MagicQuery is compatible with Doctrine Cache. You simply have to pass a Doctrine Cache instance has the second parameter of the constructor., _(*24)

use Mouf\Database\MagicQuery;
use Doctrine\Common\Cache\ApcCache;

// $conn is a Doctrine connection
$magicQuery = new MagicQuery($conn, new ApcCache());

Any problem?

With MagicQuery, a lot happens to your SQL query. In particular, it is parsed using a modified version of the php-sql-parser library. If you face any issues with a complex query, it is likely there is a bug in the parser. Please open an issue on Github and we'll try to fix it., _(*25)