Issues

ZF-2228: Improvement of where

Description

The where clause should be improved.

The actual implementation has some problems and is lacking simple but usefull features. This is a summary of all usefull changes which include:

  • Instant Binding of values
  • Combining of instant and late binding
  • Accepting array input
  • Accepting multiple placeholders
  • Creation of difficult clauses with array
  • Accept value types for values

Here are the usecases which solve old behaviour and show some new behaviour


 1) where("column = 1");         // -> "column = 1"
 4) where("column = :id");       // -> "column = '1'" depends on :id

 2) where("column = ?", 1);      // -> "column = '1'"
 3) where("column = ?", $value); // -> "column = '1'" depends on $value

 5) where("column = ?",      1, Zend_Db::INT_TYPE); // -> "column = 1" -> "column = 'stringvalue'"
 6) where("column = ?", $value, Zend_Db::INT_TYPE); // -> "column = 1" -> "column = 'stringvalue'" depends on $value

11) where("column = ? AND (column2 = ? OR column5 = ?)", 4, 'test', 5);        // -> "column = 4 AND (column2 = 'test' OR column5 = 5)"
12) where("column = ? AND (column2 = ? OR column5 = ?)", array(4, 'test', 5)); // -> "column = 4 AND (column2 = 'test' OR column5 = 5)"
16) where("price in (?)", 1, 2, 3, 4);                    // where price in (1, 2, 3, 4)

13) where("column = :first AND (column2 = :second OR column5 = :third)", array('first' => 4, 'second' => 'test', 'third' => 5)); // -> "column = 4 AND (column2 = 'test' OR column5 = 5)"
14) where("column = :first AND (column2 = :second OR column5 = :third)", array('first' => 4, 'second' => 'test'));               // -> "column = 4 AND (column2 = 'test' OR column5 = :third)" -> for later binding

 7) where(array("column" => 1, "other" => 140));       // -> "column = 1 AND other = 140"
 8) where(array("column" => 1, "other" => 140), true); // -> "column = '1' AND other = '140'"

 9) where(array(array("column = ?", 1), array("other = ?", 140)); // -> "column = '1' AND other = '140'
10)where(array(array("column = ?", 1, Zend_Db::INT_TYPE), array("other = ?", 140, Zend_Db::STRING_TYPE)); // -> "column = 1 AND other = '140'

15)where(array(array("column = ?", 1, Zend_Db::INT_TYPE), Zend_Db::OR, array("other = ?", 140, Zend_Db::STRING_TYPE)); // -> "(column = 1) OR (other = '140')

17) where("price in (?)", array(1,2,3,4));                    // where price in (1, 2, 3, 4)
18) where("price > ? and price in (?)", array (1,2,3,4));     // where price > 1 and price in (2, 3, 4)
19) where("price in (?) and price like '?'", array(1,2,3,4)); // where price in (1, 2, 3, 4) and price like '?'

There are some other issues which are related to single usecases and are set as dependend/relevant

Comments

I included the usecases 1-6 and 11-19 into the incubator.

The already implemented usecases have been integrated into core for 1.5RC

Class will be reworked by Simon... sorry if I confused anybody. He has a better overview over the complete Db design, not only Zend_Db_Select and will help better than me.

Numbers 17-19 are critical requirements, not enhancements imo. Are these features currently available under 1.7?

Will evaluate within 2 weeks

All sub-issues have been closed, except ZF-2142 which can stand alone.