Download the PHP package mouf/magic-query without Composer

What is Magic-query?

Magic-query is a PHP library that helps you work with complex SQL queries.

It comes with 3 great features:

• 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:

Automatically discard unused parameters with MagicParameters

Just write the query with all possible parameters.

Curious to know how this work? Check out the parameters guide!

Automatically guess JOINs with MagicJoin!

Fed up of writing joins in SQL? Let MagicQuery do the work for you!

Seriously? Yes! All you have to do is:

• 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:

Using MagicJoin, you can write this SQL query:

and it will automatically be transformed into this:

And the code is so simple!

Want to know more? Check out the MagicJoin guide!

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!

Using Twig integration, you can directly add Twig conditions right into your SQL.

Want to know more? Check out the MagicTwig guide!

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.

For instance:

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:

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.

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.