Skip to content

Loading…

DBAL-12: Add Doctrine\DBAL\SQLQuery package with simple Query Objects #1144

Closed
doctrinebot opened this Issue · 8 comments

2 participants

@doctrinebot

Jira issue originally created by user @beberlei:

We should add a Query Objects package to Doctrine\DBAL. This won't necessarily be used with Doctrine\ORM, however it could be really helpful to people that only plan to use Doctrine\DBAL and not the ORM.

Requirements:

  • Add Query Objects for all types of SELECT, INSERT, UPDATE, DELETE
  • Fluent Interface
  • Make use of Platform to abstract limit subquery and expressions
  • Add factory method on Doctrine\DBAL\Connection

API Ideas:

Are there query objects in other languages that could help here?

@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 ZendDbSelect)

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 ZendDbSelect 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"

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.1 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.