DBAL-12: Add Doctrine\DBAL\SQLQuery package with simple Query Objects about dbal

[doctrinebot]

Comment created by @beberlei:

Discussions on the API:

First observations/requirements:

• Identifiers are NEVER quoted by the SQL Query Object
• A query object cannot ever be as efficient as our Persisters building SQL in a custom way
• A SQL Query instance is only an Object with an internal stack of SQL parts, it should not have any logic whatsoever
• A SQL Query instance is a stack for bound params and values, i.e. it should work towards prepared statements and not inline and quote values (like Zend_Db_Select)

Questions:

• Should we allow methods to accept either array or string? Or streamline API to allow only one? select(string) + selectAll(array)?

Binding values:

$sql->bindValue(1); // adds 1 to the bind stack and returns a ":doctrineValue$inc" string
$sql->bindParam($foo, PDO::PARAM_INT); // the same as before, with additional type binding

Building a Select clause A:

$sql->select(array('foo', 'bar', 'baz')); // SELECT foo, bar, baz
$sql->select(array('foo' => 'foo1', 'bar' => 'bar2', 'baz' => 'baz3')); // SELECT foo AS foo1, bar AS bar2, baz AS baz3

Building a Select clause B:

$sql->select('foo')->select('bar')->select('baz'); // SELECT foo, bar, baz
$sql->select('foo', 'f'); // SELECT foo AS f

Building the From clause A:

$sql->from('foo'); // FROM foo
$sql->from('foo', 'f'); // FROM foo f
$sql->from('foo', 'f', array('foo')); // SELECT foo FROM foo f

Building the From Clause B:

$sql->from('foo'); // FROM foo
$sql->from(array('foo')); // FROM foo
$sql->from(array('f' => 'foo')); // FROM foo f
$sql->from(array('f' => 'foo'), array('foo')); // SELECT foo FROM foo f

Building a Join clause (obviously depends on the From syntax also for a consistent approach):

public function join($table, $alias, $onClause, $cols);
public function join($table, $onClause, $cols);

Join Method Names: joinInner, joinLeft, joinRight (Zend) OR innerJoin, leftJoin, rightJoin (Zeta)?

Where Syntax:

This is pretty difficult and i prefer the approach from ORM\QueryBuilder:

$sql->where("foo = ?");
$sql->where("foo = ?", $foo); // allow this? its Zend*Db*Select syntax
$sql->where("foo = " . $sql->bindParam($foo));
$sql->where($sql->expr()->eq("foo", $sql->bindParam($foo));

For an OR on the main level:

$sql->where("foo = ?")->orWhere("bar = ?");

For a nested condition:

$sql->where("foo = ? OR bar = ? OR baz = ?");
$sql->where($sql->expr()->or(
    $sql->expr()->eq("foo", $sql->bindParam($foo)),
    $sql->expr()->eq("bar", $sql->bindParam($bar)),
    $sql->expr()->eq("baz", $sql->bindParam($baz)),
));

There is an Expression class that has control flow and sql functions, using a platform internally for vendor specific sql.

Execution or Preparing the SQL:

$stmt = $sql->prepare();
$sql->execute();

Comments?

[doctrinebot]

Comment created by romanb:

The usage of bindValue/bindParam is very confusing to me. It should be in-line with PDO where the only difference is whether the parameter is bound by value (the value of the variable gets bound at the point of the method call) or by reference (the value of the variable gets bound at the point the statement is executed).

[doctrinebot]

Comment created by @beberlei:

How would that look like in your opinion? This is what Zend_Db_Select gets wrong...

[doctrinebot]

Comment created by romanb:

Well I would say either having bindValue/bindParam but with the same semantics as PDO or trashing both and just having setParameter, like we do in the ORM, that binds the value at the time of the invocation, intuitively.
I would go with the latter as I have yet to find a real use-case for bindParam (binding "by reference").

The only "wrong" direction here IMHO is to reuse PDOs names but give them a completely different meaning.

I would just go with setParameter($key, $value, [$type = PDO::PARAM_STR]).

I might be missing something though because I dont really understand the bindValue/bindParam examples given in your comments, i.e. I dont understand why it "generates" a key. Can you show a "real" example usage in a query?

[doctrinebot]

Comment created by @beberlei:

But how would that bind stuff work with sub selects?

Say you do:

$sub = new SelectQuery();
$sub->select('a')->from('subtable')->where('b = ?')
$root = new SelectQuery();
$root->select('b')->from('root')->where('b IN (' . $sub . ' )');

[doctrinebot]

Comment created by @beberlei:

Assigned to Guilherme for 2.1

[doctrinebot]

Comment created by @beberlei:

Implemented

[doctrinebot]

Issue was closed with resolution "Fixed"

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.