Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-12

Add Doctrine\DBAL\SQLQuery package with simple Query Objects

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.1
    • Component/s: None
    • Labels:
      None

      Description

      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?

        Activity

        Hide
        Benjamin Eberlei added a comment -

        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?

        Show
        Benjamin Eberlei added a comment - 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?
        Hide
        Roman S. Borschel added a comment -

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

        Show
        Roman S. Borschel added a comment - 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).
        Hide
        Benjamin Eberlei added a comment -

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

        Show
        Benjamin Eberlei added a comment - How would that look like in your opinion? This is what Zend_Db_Select gets wrong...
        Hide
        Roman S. Borschel added a comment -

        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?

        Show
        Roman S. Borschel added a comment - 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?
        Hide
        Benjamin Eberlei added a comment -

        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 . ' )');
        
        Show
        Benjamin Eberlei added a comment - 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 . ' )');
        Hide
        Benjamin Eberlei added a comment -

        Assigned to Guilherme for 2.1

        Show
        Benjamin Eberlei added a comment - Assigned to Guilherme for 2.1
        Hide
        Benjamin Eberlei added a comment -

        Implemented

        Show
        Benjamin Eberlei added a comment - Implemented

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Benjamin Eberlei
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: