Add set_expr() closing issues #43 & #59

3 files changed, 100 insertions, 8 deletions

diff --git a/README.markdown b/README.markdown
index d5a2933..51b5b74 100644
--- a/README.markdown
+++ b/README.markdown
@@ -36,6 +36,7 @@ Changelog
 * Add `select_many` and `select_many_expr` - closing issues #49 and #69
 * Add support for `MIN`, `AVG`, `MAX` and `SUM` - closes issue #16
 * Add `group_by_expr` - closes issue #24
+* Add `set_expr` to allow database expressions to be set as ORM properties - closes issues #59 and #43 [[brianherbert](https://github.com/brianherbert)]
 
 #### 1.1.1 - release 2011-01-30
 
@@ -414,6 +415,18 @@ To update the database, change one or more of the properties of the object, then
     // Syncronise the object with the database
     $person->save();
 
+#### Properties containing expressions ####
+
+It is possible to set properties on the model that contain database expressions using the `set_expr` method.
+
+    $person = ORM::for_table('person')->find_one(5);;
+    $person->set('name', 'Bob Smith');
+    $person->age = 20;
+    $person->set_expr('updated', 'NOW()');
+    $person->save();
+
+The `updated` column's value will be inserted into query in its raw form therefore allowing the database to execute any functions referenced - such as `NOW()` in this case.
+
 ### Creating new records ###
 
 To add a new record, you need to first create an "empty" object instance. You then set values on the object as normal, and save it.
@@ -427,6 +440,18 @@ To add a new record, you need to first create an "empty" object instance. You th
 
 After the object has been saved, you can call its `id()` method to find the autogenerated primary key value that the database assigned to it.
 
+#### Properties containing expressions ####
+
+It is possible to set properties on the model that contain database expressions using the `set_expr` method.
+
+    $person = ORM::for_table('person')->create();
+    $person->set('name', 'Bob Smith');
+    $person->age = 20;
+    $person->set_expr('added', 'NOW()');
+    $person->save();
+
+The `added` column's value will be inserted into query in its raw form therefore allowing the database to execute any functions referenced - such as `NOW()` in this case.
+
 ### Checking whether a property has been modified ###
 
 To check whether a property has been changed since the object was created (or last saved), call the `is_dirty` method:
diff --git a/idiorm.php b/idiorm.php
index 25ef3ff..c3bae4f 100644
--- a/idiorm.php
+++ b/idiorm.php
@@ -134,6 +134,9 @@
         // lifetime of the object
         protected $_dirty_fields = array();
 
+        // Fields that are to be inserted in the DB raw
+        protected $_expr_fields = array();
+
         // Is this a new object (has create() been called)?
         protected $_is_new = false;
 
@@ -734,9 +737,18 @@
          * Return a string containing the given number of question marks,
          * separated by commas. Eg "?, ?, ?"
          */
-        protected function _create_placeholders($number_of_placeholders) {
-            if($number_of_placeholders) {
-                return join(", ", array_fill(0, $number_of_placeholders, "?"));
+        protected function _create_placeholders($fields) {
+            if(!empty($fields)) {
+                $db_fields = array();
+                foreach($fields as $key => $value) {
+                    // Process expression fields directly into the query
+                    if(array_key_exists($key, $this->_expr_fields)) {
+                        $db_fields[] = $value;
+                    } else {
+                        $db_fields[] = '?';
+                    }
+                }
+                return implode(', ', $db_fields);
             }
         }
 
@@ -819,7 +831,7 @@
          */
         public function where_in($column_name, $values) {
             $column_name = $this->_quote_identifier($column_name);
-            $placeholders = $this->_create_placeholders(count($values));
+            $placeholders = $this->_create_placeholders($values);
             return $this->_add_where("{$column_name} IN ({$placeholders})", $values);
         }
 
@@ -828,7 +840,7 @@
          */
         public function where_not_in($column_name, $values) {
             $column_name = $this->_quote_identifier($column_name);
-            $placeholders = $this->_create_placeholders(count($values));
+            $placeholders = $this->_create_placeholders($values);
             return $this->_add_where("{$column_name} NOT IN ({$placeholders})", $values);
         }
 
@@ -1193,12 +1205,31 @@
          * database when save() is called.
          */
         public function set($key, $value = null) {
+            $this->_set_orm_property($key, $value);
+        }
+
+        public function set_expr($key, $value = null) {
+            $this->_set_orm_property($key, $value, true);
+        }
+
+        /**
+         * Set a property on the ORM object.
+         * @param string|array $key
+         * @param string|null $value
+         * @param bool $raw Whether this value should be treated as raw or not
+         */
+        protected function _set_orm_property($key, $value = null, $expr = false) {
             if (!is_array($key)) {
                 $key = array($key => $value);
             }
             foreach ($key as $field => $value) {
                 $this->_data[$field] = $value;
                 $this->_dirty_fields[$field] = $value;
+                if (false === $expr and isset($this->_expr_fields[$field])) {
+                    unset($this->_expr_fields[$field]);
+                } else if (true === $expr) {
+                    $this->_expr_fields[$field] = true;
+                }
             }
         }
 
@@ -1216,7 +1247,9 @@
          */
         public function save() {
             $query = array();
-            $values = array_values($this->_dirty_fields);
+
+            // remove any expression fields as they are already baked into the query
+            $values = array_diff_key($this->_dirty_fields, $this->_expr_fields);
 
             if (!$this->_is_new) { // UPDATE
                 // If there are no dirty values, do nothing
@@ -1254,7 +1287,10 @@
 
             $field_list = array();
             foreach ($this->_dirty_fields as $key => $value) {
-                $field_list[] = "{$this->_quote_identifier($key)} = ?";
+                if(!array_key_exists($key, $this->_expr_fields)) {
+                    $value = '?';
+                }
+                $field_list[] = "{$this->_quote_identifier($key)} = $value";
             }
             $query[] = join(", ", $field_list);
             $query[] = "WHERE";
@@ -1273,7 +1309,7 @@
             $query[] = "(" . join(", ", $field_list) . ")";
             $query[] = "VALUES";
 
-            $placeholders = $this->_create_placeholders(count($this->_dirty_fields));
+            $placeholders = $this->_create_placeholders($this->_dirty_fields);
             $query[] = "({$placeholders})";
             return join(" ", $query);
         }
diff --git a/test/test_queries.php b/test/test_queries.php
index 693b898..b0dc88b 100644
--- a/test/test_queries.php
+++ b/test/test_queries.php
@@ -238,6 +238,14 @@
     $expected = "INSERT INTO `widget` (`name`, `age`) VALUES ('Fred', '10')";
     Tester::check_equal("Insert data", $expected);
 
+    $widget = ORM::for_table('widget')->create();
+    $widget->name = "Fred";
+    $widget->age = 10;
+    $widget->set_expr('added', 'NOW()');
+    $widget->save();
+    $expected = "INSERT INTO `widget` (`name`, `age`, `added`) VALUES ('Fred', '10', NOW())";
+    Tester::check_equal("Insert data containing an expression", $expected);
+
     $widget = ORM::for_table('widget')->find_one(1);
     $widget->name = "Fred";
     $widget->age = 10;
@@ -246,12 +254,35 @@
     Tester::check_equal("Update data", $expected);
 
     $widget = ORM::for_table('widget')->find_one(1);
+    $widget->name = "Fred";
+    $widget->age = 10;
+    $widget->set_expr('added', 'NOW()');
+    $widget->save();
+    $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10', `added` = NOW() WHERE `id` = '1'";
+    Tester::check_equal("Update data containing an expression", $expected);
+
+    $widget = ORM::for_table('widget')->find_one(1);
     $widget->set(array("name" => "Fred", "age" => 10));
     $widget->save();
     $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10' WHERE `id` = '1'";
     Tester::check_equal("Update multiple fields", $expected);
 
     $widget = ORM::for_table('widget')->find_one(1);
+    $widget->set(array("name" => "Fred", "age" => 10));
+    $widget->set_expr(array("added" => "NOW()", "lat_long" => "GeomFromText('POINT(1.2347 2.3436)')"));
+    $widget->save();
+    $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10', `added` = NOW(), `lat_long` = GeomFromText('POINT(1.2347 2.3436)') WHERE `id` = '1'";
+    Tester::check_equal("Update multiple fields containing an expression", $expected);
+
+    $widget = ORM::for_table('widget')->find_one(1);
+    $widget->set(array("name" => "Fred", "age" => 10));
+    $widget->set_expr(array("added" => "NOW()", "lat_long" => "GeomFromText('POINT(1.2347 2.3436)')"));
+    $widget->lat_long = 'unknown';
+    $widget->save();
+    $expected = "UPDATE `widget` SET `name` = 'Fred', `age` = '10', `added` = NOW(), `lat_long` = 'unknown' WHERE `id` = '1'";
+    Tester::check_equal("Update multiple fields containing an expression (override previously set expression with plain value)", $expected);
+
+    $widget = ORM::for_table('widget')->find_one(1);
     $widget->delete();
     $expected = "DELETE FROM `widget` WHERE `id` = '1'";
     Tester::check_equal("Delete data", $expected);