Independent query builders for MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.

Last update: Aug 3, 2022

Aura.SqlQuery

Provides query builders for MySQL, Postgres, SQLite, and Microsoft SQL Server. These builders are independent of any particular database connection library, although PDO in general is recommended.

Installation and Autoloading

This package is installable and PSR-4 autoloadable via Composer as aura/sqlquery.

Alternatively, download a release, or clone this repository, then map the Aura\SqlQuery\ namespace to the package src/ directory.

Dependencies

This package requires PHP 5.6 or later; it has been tested on PHP 5.6, PHP 7, and HHVM. We recommend using the latest available version of PHP as a matter of principle.

Aura library packages may sometimes depend on external interfaces, but never on external implementations. This allows compliance with community standards without compromising flexibility. For specifics, please examine the package composer.json file.

Quality

Scrutinizer Code Quality Code Coverage Build Status

This project adheres to Semantic Versioning.

To run the unit tests at the command line, issue composer install and then ./vendor/bin/phpunit at the package root. This requires Composer to be available as composer.

This package attempts to comply with PSR-1, PSR-2, and PSR-4. If you notice compliance oversights, please send a patch via pull request.

Community

To ask questions, provide feedback, or otherwise communicate with other Aura users, please join our Google Group, follow @auraphp, or chat with us on Freenode in the #auraphp channel.

Documentation

This package is fully documented here.

GitHub

https://github.com/auraphp/Aura.SqlQuery
Comments
  • 1. Query generation in Aura\Sql

    Guys, hold the press for a minute - I think I may have just identified a misplaced feature (or a feature overlap) between Aura\Sql and Aura\Sql_Query.

    From the Aura\Sql README:

    named placeholders in prepared statements that are bound to array values will be replaced with comma-separated quoted values. This means you can bind an array of values to a placeholder used with an IN (...) condition

    This is great, but it has nothing to with "prepared statements" in PDO terms, and it's not strictly an "extension" to PDO - strictly speaking, it's query construction, which is supposed to to be Aura\Sql_Query's domain.

    According to the PHP manual, prepared statements:

    can be executed multiple times with the same or different parameters. When the query is prepared, the database will analyze, compile and optimize its plan for executing the query

    By throwing query construction for arrays into the mix, this is no longer true - as well as:

    By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle

    This aspect of PDO is now no longer a given in ExtendedPdo - as you know, PDO does not support binding arrays to placeholders, but that is a shortcoming which cannot be addressed by extending the PDO class, since you are then no longer using prepared statements.

    While ExtendedPdo arguably "prepares statements", it does not extend the concept of "prepared statements" as defined by PDO. As such, support for binding arrays to statements can't be implemented in userland, because true support for prepared statements is something that requires PDO driver-level support; you don't have any control over the "analyze/compile/optimize cycle", which is an internal feature of PDO.

    An array of strings string[] or numbers int[] is a type, and what you're actually doing, is constructing SQL representations of values of those types, for use in SQL queries, which is query construction.

    You would not be doing that (and it wouldn't work) for any other types, such as date/time or boolean values. The reason it's even possible at all, without a driver abstraction (which ExtendedPdo does not have, and Sql_Query does) is that the syntax happens to be the same in every supported SQL engine - but that is not a given for any other type.

    In other words, this is a query construction concern, not preparation - and I would argue that this has no place in ExtendedPdo; query construction belongs in Aura\Sql_Query, which has a framework to support and handle driver-specific details of SQL generation.

    While Aura\Sql_Query is not currently equipped with any driver-specific value-to-SQL or SQL-to-value facilities, this is clearly something you found useful and necessary enough to support for arrays - so a more complete type/value handling facility is probably worth considering?

    PDO itself has very bare bones type management - only what is strictly required and/or supported by drivers, e.g. strings, numbers and NULL values. ExtendedPdo tacks on support specifically for arrays of strings, numbers and NULL values, and the end result is a little better, but still isn't by any means a generic framework for all types of values.

    Moving support for arrays out of the PDO extension into Sql_Query might be a logical first step towards a more complete (and more general) value formatting facility.

    What do you think?

    Reviewed by mindplay-dk at 2014-02-03 21:22
  • 2. Why the 5.4 dependency?

    Why the dependency on PHP 5.4?

    The new Aura\Sql is compatible with 5.3 - why the higher requirements for this component?

    I see you're using traits, and maybe that's why - but the problem you're solving with traits in this case can be solved just as well using composition rather than inheritance.

    Would you accept a pull-request, refactoring for PHP 5.3 compatibility (passing all unit-tests, keeping API identical to current API) or do I have to maintain my own fork?

    Reviewed by mindplay-dk at 2014-01-27 19:32
  • 3. Version 3: Update interfaces and nothing else

    There have been several questions lately regarding the fact that the interfaces do not match the classes. The disconnect is because the classes have had features added, but adding the relevant methods to the interface would be a BC break.

    Perhaps the time has come to update the interfaces, and in so doing move to version 3, with no other functionality changes.

    Thoughts?

    Reviewed by pmjones at 2016-09-20 19:22
  • 4. Should Queries Keep Track of Joined Tables?

    I'm building an application that uses a lot of other objects to modify an Aura query builder object.

    One issue I ran into is that the query builders don't keep track of joined tables. So something like this:

    use Aura\SqlQuery\QueryFactory;
    
    $factory = new QueryFactory('mysql');
    
    $select = $factory->newSelect();
    
    $select->cols([
        'SUM(clicks)' => 'clicks',
    ]);
    $select->from('some_table');
    $select->join('other', 'some_table.id = other.id');
    
    // some other object does it again
    $select->join('other', 'some_table.id = other.id');
    
    echo $select, PHP_EOL;
    

    I would expect the join to only be generated a single time, but that's not the case:

    SELECT
        SUM(clicks) AS `clicks`
    FROM
        `some_table`
    OTHER JOIN `some_table`.`id` `=` `other.id`
    OTHER JOIN `some_table`.`id` `=` `other.id`
    

    It's easy enough to solve with client code, but it seems like it might be a nice feature for the query builders in general.

    Reviewed by chrisguitarguy at 2014-08-19 21:49
  • 5. Group by [Feature]

    Hi,

    One of the missing feature I think about the library is creating grouping statements. I have been looking at https://github.com/paragonie/easydb#grouping-of-conditions .

    An example taken :

    $statement = EasyStatement::open()
        ->group()
            ->with('subtotal > ?')
            ->andWith('taxes > ?')
        ->end()
        ->orGroup()
            ->with('cost > ?')
            ->andWith('cancelled = 1')
        ->end();
    
    echo $statement; /* (subtotal > ? AND taxes > ?) OR (cost > ? AND cancelled = 1) */
    

    Does anyone like this feature ?

    Reviewed by harikt at 2017-01-12 15:49
  • 6. Add reset functionality on the Select class

    @yespire @golgote @gauthier @harikt --

    This PR is based on #84 from @yespire. It combines the resetFrom(), resetFromKey(), and resetTableRefs() into a single resetTables() method, and adds resetBindValues() and resetUnions(). It removes resetLimit(), resetOffset(), resetPage(), because those values are accessible through existing limit(), offset(), and page() methods. Finally, the resetFlags() method is made public instead of protected, and now returns $this.

    In summary, the newly-public reset methods are:

    • resetBindValues()
    • resetFlags()
    • resetCols()
    • resetTables()
    • resetWhere()
    • resetGroupBy()
    • resetHaving
    • resetOrderBy()
    • resetUnions()

    Thoughts?

    Reviewed by pmjones at 2016-05-19 17:03
  • 7. Subquery should check for interface not extending abstractquery

    The fix for using a subselect as a where item breaks when used with sqlmapper_bundle and anything else that might use composition instead of direct inheritance for a query builder class

    https://github.com/auraphp/Aura.SqlQuery/commit/bc4a1fb2a2d25a78d0b64432dddab5ac1f7d583c#diff-99f68aad8e6850c09d918a732ff868e3R333

    It fails with cannot bind value of type object to placeholder - in fact a nice feature might be to check for stringability in ANY object bound into a where

    In any case the current code is checking for direct inheritance of abstractquery, should probably use a different kind of check - or change sqlmapper_bundle to use more direct inheritance for it's query builder classes so things don't break

    Reviewed by auroraeosrose at 2015-05-07 19:13
  • 8. Postgre jsonb operators and placeholders

    Hello!

    I have a problem with making select query with operator ?| that is used in postgre to check if two jsonb items have common keys. My query, written by hand and simplified for the example, is:

    select * 
    from table_name
    where phones::jsonb ?| ARRAY['71111111111', '71234567890']
    

    I used query builder like this:

    $queryFactory = new QueryFactory('pgsql');
    $select = $queryFactory->newSelect();
    
    $select->cols(['*'])
    ->from('table_name')
    ->where("phones::jsonb ?| ARRAY['71111111111', '71234567890']");
    
    $statement = $select->getStatement();
    

    but I got that (notice how "?" has been replaced. Builder supposes that it is placeholder for parameter):

    select * 
    from table_name
    where phones::jsonb :_1_| ARRAY['71111111111', '71234567890']
    

    So, what can I do to get rid off of this?

    Reviewed by taxp at 2017-03-03 09:57
  • 9. Insert multiple rows with a single query

    Is there a way to build a query that inserts multiple value sets?

    INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

    If not, do you think it makes sense to add that functionality?

    Reviewed by jakeasmith at 2014-04-01 22:11
  • 10. Allow reset for some query parts

    What about: public function orderBy(array $spec, $replace = false) instead of : public function orderBy(array $spec)

    This can be added to other clauses as well. I personally need this because my query is built by different objects and each adds some clauses that might later need to be replaced by others.

    Reviewed by golgote at 2016-02-23 14:05
  • 11. getStatement not reconized in IDE

    When we build a select query :

        $factory= new QueryFactory("mysql");
        $query = $factory->newSelect(); 
        $query->getStatement();
    

    the method getStatement() is not reconized in the ide and that leads to confusion and maybe is it highlighting a mistake in the library design ?

    Reviewed by gsouf at 2015-08-27 13:28
  • 12. Named placeholder don't work in where()

    The documentation in examles allow to used named placeholders in where clause like this:

    // bind 'zim_val' to the :zim placeholder
    ->where('zim = :zim', ['zim' => 'zim_val'])
    

    But this will not work since named placeholders don't update bind_values internal property. The ->where() method only works with ?-placeholders which actually not documented at all. I've lost 2 hours trying to figure it out.

    $select = $queryFactory->newSelect()->cols(['*'])->from('table1')->where('col1 = :col1', ['col1' => 'value']);
    var_dump($select->getStatement());
    var_dump($select->getBindValues());
    

    This will print SQL query and empty array of bind values.

    Possible workaround could be using of additional bindValues():

    $select = $queryFactory->newSelect()->cols(['*'])->from('table1')->where('col1 = :col1')->bindValues(['col1' => 'value']);
    var_dump($select->getStatement());
    var_dump($select->getBindValues());
    

    or using ?-placeholder:

    $select = $queryFactory->newSelect()->cols(['*'])->from('table1')->where('col1 = ?', 'value');
    var_dump($select->getStatement());
    var_dump($select->getBindValues());
    

    But both variants looks dirty and not match documentation.

    P.S. Is this repo abandoned?

    Reviewed by gugglegum at 2022-01-08 03:29
  • 13. Allow passing query to `union()`

    It would be convenient to be able to pass a query directly to union() instead of rerunning the same commands on the original query.

    Here's what I thought I would be able to do. I've simplified the queries from my RL example to make them easier to read. The more complicated the similar union()ed queries are, the more appreciated the requested change would be.

    $txns_by_owner = $this->queryBuilder->newSelect()
        ->cols($this->getBalanceFields())
        ->from('transactions AS t');
    
    // the two subqueries are mostly the same. Clone them now, add differing elements after
    $txns_by_property = clone $txns_by_owner;
    $txns_by_owner->where('t.owner_id = :owner_id');
    
    $txns_by_property
        ->join('INNER', 'properties AS p', 'p.property_id = t.property_id')
        ->where('p.owner_id = :owner_id')
    ;
    
    return $this->queryBuilder->newSelect()
        ->cols(['SUM(amount) AS amount'])
        ->fromSubSelect($txns_by_owner->union($txns_by_property));
    

    It seems what I actually have to do is this

    $subquery = $this->queryBuilder->newSelect();
    
    $subquery->cols($this->getBalanceFields())
        ->from('transactions AS t')
        ->where('t.owner_id = :owner_id')
    ;
    
    $subquery->union()
        ->cols($this->getBalanceFields())
        ->from('transactions AS t')
        ->join('INNER', 'properties AS p', 'p.property_id = t.property_id')
        ->where('p.owner_id = :owner_id')
    ;
    
    return $this->queryBuilder->newSelect()
        ->cols(['SUM(amount) AS amount'])
        ->fromSubSelect($subquery, 't1')
    ;
    
    Reviewed by RentecTravis at 2021-09-01 20:20
  • 14. Where with parentheses

    Maybe I'm missing something... How would I replicate the following?

    where (
        find_in_set(tests.`compound.group`, :compound_groups) or
        find_in_set(tests.`compound.name`, :compound_names)
    )
    and (
        find_in_set(isolates.`species.genus`, :species_genera) or
        find_in_set(isolates.`species.name`, :species_names)
    )
    

    The parentheses are important in the above, and I can't figure out how I should do it with SQLQuery.

    Reviewed by designermonkey at 2021-03-24 15:10
  • 15. # in column name

    Working with an exsisting DB structure and they have # special char all over their column names in a DB2 database.

    Using ->orderBy( 'A.G01GL#' )

    results in the sql statement being

    ORDER BY "A"."G01GL"# ASC

    Any advice on how to get around this?

    Reviewed by otelconsulting at 2020-05-28 17:10
  • 16. Update '->where' method with binding params don't working

    Hi, everyone. Method 'where' with passed two params don't working for me. I do that ->where('gir = :gir', ['gir' => 'gir_val']) Also phpstorm shows warning with the text:

    Method call is provided 2 parameters, but the method signature uses 1 parameters.

    Screen Shot 2020-02-22 at 3 43 54 PM
    Reviewed by FAST-JE at 2020-02-22 12:44
A simple and extensible fixture loader for PHP 7.3+, supporting SQLite and MySQL

Flowder Flowder is a (really) simple fixture loader for PHP 7.3+, supporting SQLite and MySQL. Using Flowder in PHP 7.2 or below? Try version 1 instea

Jan 17, 2021
phpMyFAQ - Open Source FAQ web application for PHP and MySQL, PostgreSQL and other databases

phpMyFAQ 3.1 What is phpMyFAQ? phpMyFAQ is a multilingual, completely database-driven FAQ-system. It supports various databases to store all data, PHP

Jun 22, 2022
A SQL query builder with zero dependencies

Latitude Query Builder A SQL query builder with zero dependencies. Attempts to be PSR-1, PSR-2, and PSR-4 compliant. Install composer require latitude

Aug 4, 2022
SQL to Laravel Query Builder

Marwan - SQL To Laravel Builder SQL to Laravel Query Builder, A Converter written in PHP Features Converts SQL Queries to Laravel Query Builder. Assis

Aug 3, 2022
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )

Mysql Optimizer mysql optimizer also known as MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query

Feb 14, 2022
FlatsApp using Laravel 8 with SQLite

The FlatsApp This Application Stacks using Laravel 8 with MySQL. Installation To get up and running of this Application please make sure you already h

Apr 4, 2022
Feather - a highly performant SQLite Cache Driver for Kirby 3

?? Kirby3 SQLite Cache-Driver Feather - a highly performant SQLite Cache Driver for Kirby 3 Commerical Usage Support open source! This plugin is free

Dec 15, 2021
A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL.

grifart/tables A simple library to access and manipulate database records. Built on top of Dibi and hardwired for PostgreSQL. This library is develope

Apr 18, 2022
A validating SQL lexer and parser with a focus on MySQL dialect.

SQL Parser A validating SQL lexer and parser with a focus on MySQL dialect. Code status Installation Please use Composer to install: composer require

Aug 9, 2022
Tiny php mysql lib (PDO-based) with handy fetch/update functionality, supports both SQL and parametric queries

Micro PHP mysql lib (~ 200 lines of code) with ultra powerful CRUD for faster than ever development: parametric fetch/insert/update/delete (based on a

Feb 22, 2022
The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

Jan 7, 2022
PHP Object Model Manager for Postgresql

POMM: The PHP Object Model Manager for Postgresql Note This is the 1,x version of Pomm. This package is not maintained anymore, the stable Pomm 2.0 is

Mar 30, 2022
🔌 A Doctrine DBAL Driver implementation on top of Swoole Coroutine PostgreSQL extension

Swoole Coroutine PostgreSQL Doctrine DBAL Driver A Doctrine\DBAL\Driver implementation on top of Swoole\Coroutine\PostgreSQL. Getting started Install

Apr 1, 2022
Support for many missing PostgreSQL specific features
Support for many missing PostgreSQL specific features

Laravel supports many different databases and therefore has to limit itself to the lowest common denominator of all databases. PostgreSQL, however, of

Aug 8, 2022
Laravel Thermite is an extended PostgreSQL Laravel database driver to connect to a CockroachDB cluster.
Laravel Thermite is an extended PostgreSQL Laravel database driver to connect to a CockroachDB cluster.

Laravel Thermite Laravel Thermite is an extended PostgreSQL Laravel database driver to connect to a CockroachDB cluster. ?? Supporting If you are usin

Jul 10, 2022
PostgreSQL enhancements for Doctrine

PostgreSQL enhancements for Doctrine. Provides support for advanced data types (json, jssnb, arrays), text search, array operators and jsonb specific functions.

Aug 8, 2022
A simple program to query mysql data and display the queried data in JSON format

A simple program to query mysql data and display the queried data in JSON format. The data displayed in JSON format will change and update as the data in your mysql database changes.

Mar 7, 2022
Get MYSQL statement from query builder in laravel helper

Get MYSQL statement laravel This package allows to get mysql statement that query builder in laravel made it for debugging purposes. Basic usage Dump

Jul 15, 2022
Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Jul 14, 2022