auraphp/aura.sqlquery

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

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-8.1. 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

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.

aura.sqlquery's Issues

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

For example

$update->table('foo')
    ->cols(array('foo' => 'bar'))
    ->where('id = ?', 123);

'foo' = [:foo] => 'bar' (named parameter)
? = [1] = 123 (positional parameter)

If any named parameter exists,
all positional parameters including the question marks must be converted into named parameters.

AS should only be recognized as an alias if its the second to the last word

Currently, AS is parsed regardless of where it in the column string. It would seem that AS is only valid if its the second to the last word. i.e. SELECT foo AS some alias here FROM... is invalid syntax. This affect how quoting is done (in this case, the query will be quoted as SELECT fooASsome alias here FROM...) which would produce an invalid query.

I think it would be more correct to only recognize AS if its the second to the last word in a string?

binValuye doesn't return the current object

Hello,
in abstract query
public function bindValue
return void.

In the given example we see that there is a chain pattern
$update
->table('foo') // update this table
->cols([ // these cols as "SET bar = :bar"
'bar',
'baz',
])
->set('date', 'NOW()') // set this col to a raw value
->where('zim = :zim') // AND WHERE these conditions
->where('gir = ?', 'doom') // bind this value to the condition
->orWhere('gir = :gir') // OR WHERE these conditions
->bindValue('bar', 'bar_val') // bind one value to a placeholder
->bindValues([ // bind these values to the query
'baz' => 99,
'zim' => 'dib',
'gir' => 'doom',
]);

But this stuff doesn't work....

fix : add a return $this;

Hstore support for postgres

Any chance of doing some hstore to PHP array back and forth support? Similar to what is trying to be achieved here https://github.com/chanmix51/Pomm/blob/15cb95a4c0f1f91e4d5454de9614570ed5387e33/Pomm/Converter/PgHStore.php

Readme Select: Cols vs Set functionality is unclear

From this example its not obvious that set() is its own key-value pair unrelated to cols() and bind*(). Consider splitting set() into its own example.

$insert
    ->into('foo')                   // INTO this table
    ->cols([                        // insert these as "(col) VALUES (:col)"
        'bar',
        'baz',
    ])
    ->set('id', 'NULL')             // insert raw values for this column
    ->bindValue('foo', 'foo_val')   // bind one value to a placeholder
    ->bindValues([                  // bind these values
        'bar' => 'foo',
        'baz' => 'zim',
    ]);

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?

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

bc4a1fb#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

Add a public method to generate SQL query

Hi,

I'm using Aura.SqlQuery for a while - it's a really great package, making the job quite well. But I'm having some issues with more complicated queries, mostly implying subqueries:

since the only way to get the query actually built, as a string, is magic method __toString, we cannot get informations about exceptions likely to be thrown when generating the query.

PHP forbid __toString method to throw Exceptions, so in such a case, the execution ends up with a Fatal Error, and no data about the thrown exception.

Could you please add something like public function generateQuery() so that we can debug when something goes wrong?

Handle case of table extension in PostgreSQL (fetching last insert id)

Found an issue with query objects in cases you have one tables extending another one.

Consider the following table structure:

CREATE TABLE basic."table_A"
(
  id serial NOT NULL,
  field1 character varying(255),
  CONSTRAINT table_a_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE basic."table_A" OWNER TO app;

CREATE TABLE basic."table_B"
(
-- Inherited from table basic."table_A":  id integer NOT NULL DEFAULT nextval('basic."table_A_id_seq"'::regclass),
-- Inherited from table basic."table_A":  field1 character varying(255),
  field2 character varying(255)
)
INHERITS (basic."table_A")
WITH (OIDS=FALSE);
ALTER TABLE basic."table_B" OWNER TO app;

And the following method in: https://github.com/auraphp/Aura.SqlQuery/blob/develop-2/src/Pgsql/Insert.php#L33

public function getLastInsertIdName($col)
{
    return "{$this->into}_{$col}_seq";
}

It fails to fetch the last insert id when inserting into table_B. the only way it was possible to fetch the correct last insert id is this:

$this->db->lastInsertId('basic.table_A_id_seq');

Like you can see I'm required talking directly to the db instance instead of using the insert object. don't think it's very optimal. another option is extending the query object and this also means eventually the query builder, also not very optimal.

I expect this to be handled "automatically" just like in the normal case. or for the query object to provide a method to adjust the sequence name.

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.

Select should throw exception when cols are missing

According to the mysql documentation a select expression should always have at least one "select_expr" - or in Sql_Query terms, at least one column.

Given that, what do you think of having Select::buildCols() throw an exception rather than return an empty string when no cols are given. It would give you a very obvious error message instead of a generic "invalid query" message.

How can I use the Aura.SqlQuery build an update query like this

Hi, I want to use Aura.SqlQuery build an update query just like this: "Update table SET field=field+1
.but, use "set('field','field + 1')" does not work properly.

How can I do ?

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.

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 ?

Remove restriction requiring FROM specification before JOIN

Presently, when building a SELECT statement, from() must be called before join(). My understanding is that this is more for semantic (i.e. JOIN essentially being a sub-clause of FROM) than logical / logistical reasons. There are use cases that would be supported by not having this restriction.

While I may attempt implementing this myself at some point, I'm uncertain of how soon that will be. As such, I'm filing this issue in case anyone else runs into the same situation and/or wants to try to implement it themselves.

Fatal error: Can't inherit abstract function Aura\SqlQuery\Common\SubselectInterface::getBindValues() (previously declared abstract in Aura\SqlQuery\QueryInterface) ...

I get this error in PHP 5.3.2.

Fatal error: Can't inherit abstract function Aura\SqlQuery\Common\SubselectInterface::getBindValues()(previously declared abstract in Aura\SqlQuery\QueryInterface) in vendor/aura/sqlquery/src/Common/Select.php on line 22

Seems to working fine in 5.4.36. Downgrading to SqlQuery 2.4.2 solves the problem.

Can't JOIN two queries in a UNION to the same unaliased table

Using aura/sqlquery 2.4.2.

Test case:

echo (string) $select
    ->cols([
        '...'
    ])
    ->from('a')
    ->join('INNER', 'c', 'a_cid = c_id')
    ->union()
    ->cols([
        '...'
    ])
    ->from('b')
    ->join('INNER', 'c', 'b_cid = c_id');

Expected output:

SELECT ...
FROM a
INNER JOIN c ON (a_cid = c_id)
UNION
SELECT ...
FROM b
INNER JOIN c ON (b_cid = c_id)

Actual output:

Cannot reference 'INNER JOIN c' after 'INNER JOIN c' in vendor/aura/sqlquery/src/Common/Select.php(406): Aura\SqlQuery\Common\Select->addTableRef('INNER JOIN', 'c')

Because the expected SQL above is legal, it seems like this is a bug that should be fixed.

That said, there are two potential work-arounds:

1. Alias c with a different alias in both SELECT statements.
2. Alias the JOIN column from each of a and b to a common name, wrap the entire query in an additional SELECT, and move the offending JOIN to be part of the new outer query with the JOIN column using the added column alias.

PHP 5.3?

This library requires PHP 5.3 or later, but it uses Traits, which wasn't implemented in PHP until PHP 5.4.

development based on PRs even from core devs

can we propose so that everybody sends PRs, even core devs, so that we can comment and see better diffs, skimming over commits is not good even though github aggregates the stuff. Please. 👶 thanks!

REPLACE sql command

Hi

Would you accept a pull request that allowed the hard-coded "INSERT" statement in */Insert.php to be swapped for "REPLACE" when we need to do a REPLACE INTO query? (supported by sqlite, mysql)

Would probably implement it like this:

private $insertCommand = 'INSERT';

public function useReplace() 
{
    $this->insertCommand = 'REPLACE';
}

protected function build()
{
        return $this->insertCommand
            . $this->buildFlags()
            . $this->buildInto()
            . $this->buildValuesForInsert()
            . $this->buildValuesForUpdateOnDuplicateKey()
            . $this->buildReturning();
}

I prefer this approach over onDuplicateKeyUpdate() because it allows my tests to run unchanged on sqlite while using mysql on the live server. On Duplicate Key update is mysql only?

tables prefix

Hi,

First I'd like to thank all contributors to share this :)

It looks like there is no support for table prefix.

$query_factory = new QueryFactory('sqlite', null, 'ps_');

So that each table name is prefixed with 'ps_' (from and joins)
I think it could be great.

• Is there a plan to provide it ?
• can it be done ? without introducing a breaking change ?

Join and From conflict

Hello:
I'm building my sentence. I can't use "join" function before "from" function. When I tried, my app give me thist error: "Cannot join() without from() first." Line 453 of \src\Common\Select.php

Your description says I can use the functions with any order. I check your file and I guess you must use other array for "join" part.

I like this lib, is very usefull. Thanks.

(feature request) WHERE ... IN ... helper

It would be helpful to have a helper for IN statements. This Stack Overflow answer has an interesting workaround for lack of PDO helper for IN.

Or is there currently a different way I could use SqlQuery for WHERE ... IN statements?

Fatal error: Method Aura\SqlQuery\Mysql\Select::__toString() must not throw an exception

I think the behavior added in #13 (specifically by commit 7053f10) to cause an exception to be thrown when no columns are specified isn't having quite the intended effect.

05:39:34 $ php -v
PHP 5.5.23 (cli) (built: Mar 22 2015 18:55:51)
Copyright (c) 1997-2015 The PHP Group
Zend Engine v2.5.0, Copyright (c) 1998-2015 Zend Technologies
    with Xdebug v2.2.6, Copyright (c) 2002-2014, by Derick Rethans
05:40:11 $ php test.php
PHP Fatal error:  Method Aura\SqlQuery\Mysql\Select::__toString() must not throw an exception in /Users/matt/blopdev/blopboard-api/test.php on line 0
PHP Stack trace:
PHP   1. {main}() /Users/matt/blopdev/blopboard-api/test.php:0

Here's test.php from the above example:

<?php
require __DIR__ . '/vendor/autoload.php';
$factory = new \Aura\SqlQuery\QueryFactory('mysql');
$select = $factory->newSelect();
$select
    ->from('table');
var_dump((string) $select);

Per the PHP manual, an exception should not be thrown from __toString(), else it results in a (rather unintuitive) fatal error.

Here's the relevant trace:

• AbstractQuery->__toString()
• AbstractQuery->getStatement()
• Common\Select->build()
• Common\Select->buildCols()

I'm unsure how this should be fixed, but ideally the result would be a more intuitive error message, or at the very least documentation of this behavior in the README.

Where should i config the database parameters like dbhost, dbname, dbuser, dbpass?

I didn't find it in the documentation? So, how's the proper way to set them?

Option to hide columns when * is selected

Is it feasible to have an option to hide columns when * is selected?

$queryfactory->newSelect()->cols(['*']->hideCols(['col1', 'col2', 'col3', 'col4']);

This request may sound strange, but I have a table with 50 columns and I want 5 columns hidden. I can either provide a 45-column array, or select * and specify the 5 columns I want hidden from the results. Is this a feasible feature request?

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?

Wrong work with IN () syntax with PostgreSQL

Hello

I'm trying to use SQL syntax IN () or NOT IN() but it's not work correct for PostgreSQL

$orderStates = [1, 2];
...
$select->where('o.state in (:listStatement)')
                   ->bindValue('listStatement', $orderStates);

got error

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "Array"

And if i'm trying to prepare string

$orderStates = [1, 2];
...
$listStatement = implode(",", $orderStates);
$select->where('o.state in (:listStatement)')
                   ->bindValue('listStatement', $listStatement);

got error

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: "2,1"

and last error is what i'm writing about.
Bug there is not necessary " " around values. Builder generates

IN ("1,2")

but should be

IN (1, 2)

Please, fix it!

complex sql query with parentheses

Hello , I need generate sql query

SELECT RAYON.NAME
,Count(DOGOVOR.NDOG) AS Count_NDOG
,Sum(DAY.DA) AS Sum_DA
FROM (
(
RAYON INNER JOIN OSNADRES ON RAYON.KRN = OSNADRES.KRN
) INNER JOIN (
DOGOVOR LEFT JOIN DAY ON DOGOVOR.NDOG = DAY.NDOG
) ON OSNADRES.ARXNOM = DOGOVOR.ARXNOM
)
INNER JOIN DOLGIPODOG ON DOGOVOR.NDOG = DOLGIPODOG.NDOG
GROUP BY RAYON.NAME
ORDER BY RAYON.NAME;

you can not remove the brackets

i got
$select1->cols(array(
'RAYON.NAME',
'Count(DOGOVOR.NDOG) AS Count_NDOG',
'Sum(DAY.DA) AS Sum_DA'
))
->from('RAYON ')
->join( 'INNER','OSNADRES ','RAYON.KRN = OSNADRES.KRN')
->join ('INNER','DOGOVOR','DOGOVOR.NDOG = DAY .NDOG')
->join ('INNER','DOLGIPODOG','DOGOVOR.NDOG = DOLGIPODOG.NDOG')
->groupBy(array('RAYON. NAME'))
->orderBy(array('RAYON. NAME'));

what is wrong

please help correct

like not work

$select->where('field_name like ?',$field_var);
result
"WHERE field_name like ?" and mysql error

$select->where('field_name like :value');
$select->bindValue(array('value'=>$value));
result
"WHERE field_name like :value" and mysql error

Array support in conditions

I guess for all those:

->where('bar > :bar')

it would be better to add support for arrays:

->where(array($field,$condition,$value))

it would make easier to build dynamic queries based on various conditions than current version with strings.

Subselect generated by newSelect wrong quote

// Generating the select that later will be used as a subselect
$subSelect = $subQuery
->cols(['*'])
->from('table1 AS t1');

echo $subSelect->__toString();
// Below the result with all quotes correct
// SELECT * FROM table1 AS t1;

// Here the main select using the subselect generated above
$select = $query
->cols(['*'])
->from('table2 AS t2')
->where('field IN ('.$subSelect->__toString().')');

// Below the result with an unwanted extra quote
// SELECT * FROM table2 AS t2 WHERE field IN (SELECT * FROM table1 AS ``t1`); <- HERE

Issue: missing parentheses () support for WHERE conditions

examples:
where query1 AND (query2 OR query3) AND query4
where query1 OR (query2 AND query3) OR query4
where query1 OR (query2 AND (query3 XOR (query4 AND query5)))

Offset ignored when no limit is specified

Hello,

It looks like that the select query builder ignore the offset value if no limit is provided, which it shouldn't :

$queryBuilder
    ->cols('id')
    ->from('user')
    ->orderBy([ 'id' ])
    ->offset(5);

gives the following statement :

SELECT id FROM "user" ORDER BY id

According to SQL specification, it should work just fine (it does with postgreSQL) :

SELECT id FROM "user" ORDER BY id OFFSET 5

With mySQL, as explained in the documentation (http://dev.mysql.com/doc/refman/5.7/en/select.html), it should be handled this way :

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

Update composer package

Hi,

Could you please update composer package on packagist.org? It seems it is not providing new versions since v2.2.0.

Thanks!

@return self vs @return $this in docblocks

Having an issue with phpStorm autocompletion. For this code

        $query = $queryFactory->newSelect()
            ->from($this->getTableName())
            ->where(self::ID_FIELD . '= :recordID')
            ->bindValues(['recordID' => $recordID]);

phpStorm is unable to autocomplete bindValues() and any subsequent method from $query because all the fluent functions are all declared @return self. Changing them to @return $this fixes it, and is probably closer to what you mean?

From https://www.phpdoc.org/docs/latest/guides/types.html

self An object of the class where this type was used, if inherited it will still represent the class where it was originally defined.
$this This exact object instance, usually used to denote a fluent interface.

and from https://github.com/phpDocumentor/fig-standards/blob/master/proposed/phpdoc.md

13 self, the element to which this type applies is of the same class as which the documented element is originally contained.
...
15 $this, the element to which this type applies is the same exact instance as the current class in the given context. As such this type is a stricter version of static as, in addition, the returned instance must not only be of the same class but also the same instance.
This type is often used as return value for methods implementing the Fluent Interface design pattern.

So I think @return $this is probably what you mean? (because the classes implement a fluent interface). Will you accept a pull request to change these over?

Automatic table name prefix

Hi,

It's a common practice to have table prefix per project installation, where multiple projects can reside in a single database. Currently I have to build my queries like:

$select->from(DB_PREFIX . 'table1')

I would like to have an optional global prefix per QueryFactory to be automatically prepended in all statements that involve table names. Is there an easy way to implement this ?

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?

Autoquoting table names

Hi,

actually tables names are auto quoted. For example, calling

->from('production.some_table')

will result in

FROM "production"."some_table"

in the final query.

Unfortunately, sometimes (with postgreSQL at least) we may need to call a function. And function name must NOT be quoted, or it triggers an error.

->from('production.some_function()')

will result in

FROM "production"."some_function()"

but it should be

FROM "production".some_function()

I can humbly propose 2 solutions:

• autodetect a function call (by detecting the brackets at the end of the table name), but i might be not really reliable,
• adding another method/an additional parameter to stop autoquoting the table name in the from method.

Thanks a lot !

[NFR] Binds on joins

It's a recommended practice to move predicates to your joins so that your where clause is applied against a smaller subset. In the second example, the sql engine has to find all rows where id is less than the value, and then filter out type. The first example may do filtering first and then the id search is applied against a smaller subset.

Obviously the example "depends" and I may not have given a super great one below, but definitely a good feature request.

Example with:

SELECT id, name
FROM table1 AS t1
INNER JOIN table2 AS t2 ON (t2.table1_id = t1.id AND t2.type = ?)
WHERE t1.id < ?

Example without:

SELECT id, name
FROM table1 AS t1
INNER JOIN table2 AS t2 ON (t2.table1_id = t1.id)
WHERE t1.id < ? AND t2.type = ?

InsertInterface purpose?

Hi

I'm not understanding InsertInterface (and some of the other interfaces): they appear to be to be incomplete?

I'd like my code to depend on the interfaces rather than the concrete classes of Aura.SqlQuery but the interfaces are often missing useful methods. eg Common\Insert implements addRow() and addRows() but these are not in InsertInterface... is there a good reason for that?

Tag New Stable Release?

The behavior of SelectInterface::cols is broken in 2.0.0: it's possible to duplicate columns in SQL and cause all sorts of fun errors.

This fixes are already in develop-2.

insert->addRows() with only one array element generates incorrect result.

When you do the following:

$data = array(
    array('col1' => 'val1', 'col2' => 'val2')
);

$insert = $builder
    ->newInsert()
    ->into('tablename')
    ->addRows($data);

The query is generated without any values. It seems the cause is line 269 in Common/Insert

public function addRow(array $cols = array())
{
    if (! $this->col_values) {
        return $this->cols($cols);
    }
    ...

It seems that because we only have one row, that function returns instead of calling finishRow(). This causes the data to not correctly "flush" to the appropriate variables. Any particular reason why this function is returning at that point?

Limit, offset and paging

Hi,
I find bad that if we directly set offset and limit to a select query, that getPaging() doesn't give an updated value.

By the way, I think it could be really intresting to have a getLimit() and a getOffset() method on Select . Why ? Because it's easier to extends, easier to test, ...

INSERT FROM SELECT?

Is it possible to do something like this...

INSERT INTO table ("someColumn") SELECT "ABC" AS "someColumn" FROM DUAL WHERE NOT EXISTS (some condition)

I guess you could build an [INSERT INTO ...] query and then the SELECT and join them as a string, but I wasn't sure if that was the wrong approach.

Also, I don't see the "DUAL" keyword included... so I assume you'd have to do like fromRaw('DUAL') ?

Make addCol as public

Hi Paul,

I noticed we only have cols method. It will be nice to make the addColumn public for we can append to cols .

I can send a PR if you consider.

orderby

hello
how set order type DESC on MySql?

Binding multiple values from where() doesn't work?

This is a segment from the SELECT section of the README file:

N.b.: The *where(), *having(), and *join*() methods all take an optional final parameter, a sequential array of values to bind to sequential question-mark placeholders in the condition clause.

If I'm reading this right, this feature doesn't actually work correctly. Here's a test case:

require __DIR__ . '/vendor/autoload.php';
$factory = new \Aura\SqlQuery\QueryFactory('mysql');
$select = $factory->newSelect();
$select
    ->cols(['*'])
    ->from('table_name')
    ->where('id IN (?, ?)', [1, 2]);
var_dump((string) $select, $select->getBindValues());

Here's the output I get running this under PHP 5.5.23 and aura/sqlquery 2.4.2:

string(63) "SELECT
    *
FROM
    `table_name`
WHERE
    id IN (:_1_, :_2_)"

array(2) {
  ["_1_"]=>
  array(2) {
    [0]=>
    int(1)
    [1]=>
    int(2)
  }
  ["_2_"]=>
  NULL
}

The query is fine, but the bound values should look like this:

array(2) {
  ["_1_"]=> int(1)
  ["_2_"]=> int(2)
}

I can't speak to whether this applies to having() or join() because I haven't tested them. I originally found this while attempting to use this feature in a DELETE statement since Common\Select and Common\Delete both appear to call out to AbstractQuery->addWhere() to handle this.

page() depends on setPaging() call order

page method calcs limit and offset immediately, using current paging value

public function page($page)
    {
        // reset the count and offset
        $this->limit  = 0;
        $this->offset = 0;

        // determine the count and offset from the page number
        $page = (int) $page;
        if ($page > 0) {
            $this->limit  = $this->paging;
            $this->offset = $this->paging * ($page - 1);
        }

        // done
        return $this;
    }

In my opinion, one of the advantages of query builder is that method calls don't depend on their order. And in this case that rule is broken. You must call setPaging before page, otherwise limit and offset will be calced based on the default value.

So I think you should just save page and paging and calc its values only when building is invoked.

make method cols() optional

by default the value array('*')