PHPackages mouf/magic-query - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. mouf/magic-query ActiveLibrary[Database & ORM](/categories/database) 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! v2.0.0(1y ago)20268.5k↓37.7%14[3 issues](https://github.com/thecodingmachine/magic-query/issues)[6 PRs](https://github.com/thecodingmachine/magic-query/pulls)3MITPHPPHP ^7.4 || ^8.0CI failing Since Sep 17Pushed 1y ago9 watchersCompare [ Source](https://github.com/thecodingmachine/magic-query)[ Packagist](https://packagist.org/packages/mouf/magic-query)[ Docs](http://mouf-php.com/packages/mouf/magic-query)[ RSS](/packages/mouf-magic-query/feed)WikiDiscussions 2.0 Synced 3d ago READMEChangelog (10)Dependencies (12)Versions (44)Used By (3) [![Latest Stable Version](https://camo.githubusercontent.com/bcc9147019a40f84b8286cc8538d6bb055ab3905bc59091ca817fae7ddad5871/68747470733a2f2f706f7365722e707567782e6f72672f6d6f75662f6d616769632d71756572792f762f737461626c65)](https://packagist.org/packages/mouf/magic-query)[![Latest Unstable Version](https://camo.githubusercontent.com/315b0e2fa548e8e6233ddd5908e2d29a9daf7debe6e185e54f16d128aeb800d6/68747470733a2f2f706f7365722e707567782e6f72672f6d6f75662f6d616769632d71756572792f762f756e737461626c65)](https://packagist.org/packages/mouf/magic-query)[![License](https://camo.githubusercontent.com/64729b4c6c40fbf5d330f93d5ef266ce2bd11e95de0f2eafd60201608962fff9/68747470733a2f2f706f7365722e707567782e6f72672f6d6f75662f6d616769632d71756572792f6c6963656e7365)](https://packagist.org/packages/mouf/magic-query)[![Scrutinizer Code Quality](https://camo.githubusercontent.com/d52e5b4395fe0e2393bde14dae38ad1396e263e3c919f6a70118a498e791f4c9/68747470733a2f2f7363727574696e697a65722d63692e636f6d2f672f746865636f64696e676d616368696e652f6d616769632d71756572792f6261646765732f7175616c6974792d73636f72652e706e673f623d312e32)](https://scrutinizer-ci.com/g/thecodingmachine/magic-query/?branch=1.2)[![Build Status](https://camo.githubusercontent.com/60d502574f9394c3dc8fdba9e937aaaf92f5d9b2c21755451c8575c1227c42cd/68747470733a2f2f7472617669732d63692e6f72672f746865636f64696e676d616368696e652f6d616769632d71756572792e7376673f6272616e63683d312e32)](https://travis-ci.org/thecodingmachine/magic-query)[![Coverage Status](https://camo.githubusercontent.com/aa8993730ae57ceec1dcf14a817fcbcbb3e7d85f126f6335952e0d93bba4fe19/68747470733a2f2f636f766572616c6c732e696f2f7265706f732f746865636f64696e676d616368696e652f6d616769632d71756572792f62616467652e7376673f6272616e63683d312e32)](https://coveralls.io/r/thecodingmachine/magic-query?branch=1.2) What is Magic-query? ==================== [](#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.](#parameters) - [**MagicJoin**: it writes JOINs for you!](#joins) - [**MagicTwig**: use Twig templating in your SQL queries](#twig) Installation ------------ [](#installation) Simply use the composer package: ``` { "require": { "mouf/magic-query": "^1.2" }, "minimum-stability": "dev", "prefer-stable": true } ``` Automatically discard unused parameters with MagicParameters -------------------------------------------------------------------------------------------------- [](#automatically-discard-unused-parameters-with-magicparameters) Just write the query with all possible parameters. ``` 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!](doc/discard_unused_parameters.md) Automatically guess JOINs with MagicJoin! -------------------------------------------------------------------------- [](#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](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/)** 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: [![Sample database schema](doc/images/schema1.png)](doc/images/schema1.png) Using MagicJoin, you can write this SQL query: ``` SELECT users.* FROM MAGICJOIN(users) WHERE groups.name = 'Admins' AND country.name='France'; ``` and it will automatically be transformed into this: ``` 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! ``` 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!](doc/magic_join.md) Use Twig templating in your SQL queries! ------------------------------------------------------------------------ [](#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. ``` 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. ``` **Heads up!** The Twig integration cannot be used to insert parameters into the SQL query. You should use classic SQL parameters for this. This means that instead if writing `{{ id }}`, you should write `:id`. Want to know more? [Check out the MagicTwig guide!](doc/magic_twig.md) Is it a MySQL only tool? ------------------------ [](#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](http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/). 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: ``` $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: ``` $magicQuery->setOutputDialect(new \Doctrine\DBAL\Platforms\PostgreSqlPlatform()); $magicQuery = new \Mouf\Database\MagicQuery(); ``` What about performances? ------------------------ [](#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. ``` use Mouf\Database\MagicQuery; use Doctrine\Common\Cache\ApcCache; // $conn is a Doctrine connection $magicQuery = new MagicQuery($conn, new ApcCache()); ``` Any problem? ------------ [](#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](https://github.com/thecodingmachine/magic-query/issues) and we'll try to fix it. ### Health Score 53 — FairBetter than 96% of packages Maintenance44 Moderate activity, may be stable Popularity45 Moderate usage in the ecosystem Community29 Small or concentrated contributor base Maturity81 Battle-tested with a long release history Bus Factor1 Top contributor holds 80.7% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Every ~82 days Recently: every ~116 days Total 44 Last Release 391d ago Major Versions v1.5.0 → v2.0.0-beta.12023-01-12 v1.5.2 → v2.0.0-beta.22024-06-08 PHP version history (3 changes)1.0.x-devPHP >=5.3.0 v1.3.0PHP >=7.1.0 v1.5.0PHP ^7.4 || ^8.0 ### Community Maintainers ![](https://avatars.githubusercontent.com/u/1104771?v=4)[mouf](/maintainers/mouf)[@Mouf](https://github.com/Mouf) --- Top Contributors [![moufmouf](https://avatars.githubusercontent.com/u/1290952?v=4)](https://github.com/moufmouf "moufmouf (196 commits)")[![homersimpsons](https://avatars.githubusercontent.com/u/16977446?v=4)](https://github.com/homersimpsons "homersimpsons (19 commits)")[![cbalda](https://avatars.githubusercontent.com/u/6704490?v=4)](https://github.com/cbalda "cbalda (7 commits)")[![TheoBiron](https://avatars.githubusercontent.com/u/7087053?v=4)](https://github.com/TheoBiron "TheoBiron (5 commits)")[![dsavina](https://avatars.githubusercontent.com/u/22031211?v=4)](https://github.com/dsavina "dsavina (4 commits)")[![dalibert42](https://avatars.githubusercontent.com/u/19168056?v=4)](https://github.com/dalibert42 "dalibert42 (3 commits)")[![xhuberty](https://avatars.githubusercontent.com/u/8350192?v=4)](https://github.com/xhuberty "xhuberty (2 commits)")[![nguyenk](https://avatars.githubusercontent.com/u/2227554?v=4)](https://github.com/nguyenk "nguyenk (2 commits)")[![Zheness](https://avatars.githubusercontent.com/u/5830103?v=4)](https://github.com/Zheness "Zheness (1 commits)")[![Denis74RUS](https://avatars.githubusercontent.com/u/4632718?v=4)](https://github.com/Denis74RUS "Denis74RUS (1 commits)")[![HugoAverty](https://avatars.githubusercontent.com/u/1681508?v=4)](https://github.com/HugoAverty "HugoAverty (1 commits)")[![mhtghn](https://avatars.githubusercontent.com/u/19688842?v=4)](https://github.com/mhtghn "mhtghn (1 commits)")[![aszenz](https://avatars.githubusercontent.com/u/25319264?v=4)](https://github.com/aszenz "aszenz (1 commits)") --- Tags databasequerymouf ### Code Quality TestsPHPUnit Static AnalysisPHPStan Type Coverage Yes ### Embed Badge ![Health badge](/badges/mouf-magic-query/health.svg) ``` [![Health](https://phpackages.com/badges/mouf-magic-query/health.svg)](https://phpackages.com/packages/mouf-magic-query) ``` ### Alternatives [prestashop/prestashop PrestaShop is an Open Source e-commerce platform, committed to providing the best shopping cart experience for both merchants and customers. 9.1k17.8k](/packages/prestashop-prestashop)[thecodingmachine/tdbm The Database Machine is a PHP ORM that requires no configuration. The object model is deduced from the database model. 123179.3k7](/packages/thecodingmachine-tdbm)[contao-community-alliance/dc-general Universal data container for Contao 1680.8k92](/packages/contao-community-alliance-dc-general) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)