Add select and select_expr methods to provide control over result columns

3 files changed, 124 insertions, 3 deletions

diff --git a/README.markdown b/README.markdown
index 80a6ada..4707aba 100644
--- a/README.markdown
+++ b/README.markdown
@@ -173,6 +173,49 @@ Two methods are provided to add `ORDER BY` clauses to your query. These are `ord
 
     $people = ORM::for_table('person')->order_by_asc('gender')->order_by_desc('name')->find_many();
 
+#### Result columns ####
+
+By default, all columns in the `SELECT` statement are returned from your query. That is, calling:
+
+    $people = ORM::for_table('person')->find_many();
+
+Will result in the query:
+
+    SELECT * FROM `person`;
+
+The `select` and method gives you control over which columns are returned. Call `select` multiple times to specify columns to return.
+
+    $people = ORM::for_table('person')->select('name')->select('age')->find_many();
+
+Will result in the query:
+
+    SELECT `name`, `age` FROM `person`;
+
+Optionally, you may also supply a second argument to `select` to specify an alias for the column:
+
+    $people = ORM::for_table('person')->select('name', 'person_name')->find_many();
+
+Will result in the query:
+
+    SELECT `name` AS `person_name` FROM `person`;
+
+Column names passed to `select` are quoted automatically, even if they contain `table.column`-style identifiers:
+
+    $people = ORM::for_table('person')->select('person.name', 'person_name')->find_many();
+
+Will result in the query:
+
+    SELECT `person`.`name` AS `person_name` FROM `person`;
+
+If you wish to override this behaviour (for example, to supply a database expression) you should instead use the `select_expr` method. Again, this takes the alias as an optional second argument.
+
+    // NOTE: For illustrative purposes only. To perform a count query, use the count() method.
+    $people_count = ORM::for_table('person')->select('COUNT(*)', 'count')->find_many();
+
+Will result in the query:
+
+    SELECT COUNT(*) AS `count` FROM `person`;
+
 #### Raw queries ####
 
 If you need to perform more complex queries, you can completely specify the query to execute by using the `raw_query` method. This method takes a string and an array of parameters. The string should contain placeholders, either in question mark or named placeholder syntax, which will be used to bind the parameters to the query.
diff --git a/idiorm.php b/idiorm.php
index 2749ca5..b630cc9 100644
--- a/idiorm.php
+++ b/idiorm.php
@@ -95,6 +95,12 @@
         // Values to be bound to the query
         protected $_values = array();
 
+        // Columns to select in the result
+        protected $_result_columns = array('*');
+
+        // Are we using the default result column or have these been manually changed?
+        protected $_using_default_result_columns = true;
+
         // Is this a raw query?
         protected $_is_raw_query = false;
 
@@ -372,6 +378,45 @@
         }
 
         /**
+         * Internal method to add an unquoted expression to the set
+         * of columns returned by the SELECT query. The second optional
+         * argument is the alias to return the expression as.
+         */
+        protected function _add_result_column($expr, $alias=null) {
+            if (!is_null($alias)) {
+                $expr .= " AS " . $this->_quote_identifier($alias);
+            }
+
+            if ($this->_using_default_result_columns) {
+                $this->_result_columns = array($expr);
+                $this->_using_default_result_columns = false;
+            } else {
+                $this->_result_columns[] = $expr;
+            }
+        }
+
+        /**
+         * Add a column to the list of columns returned by the SELECT
+         * query. This defaults to '*'. The second optional argument is
+         * the alias to return the column as.
+         */
+        public function select($column, $alias=null) {
+            $column = $this->_quote_identifier($column);
+            $this->_add_result_column($column, $alias);
+            return $this;
+        }
+
+        /**
+         * Add an unquoted expression to the list of columns returned
+         * by the SELECT query. The second optional argument is
+         * the alias to return the column as.
+         */
+        public function select_expr($expr, $alias=null) {
+            $this->_add_result_column($expr, $alias);
+            return $this;
+        }
+
+        /**
          * Internal method to add a WHERE condition to the query
          */
         protected function _add_where($fragment, $values) {
@@ -561,7 +606,8 @@
                 $count_column = $this->_quote_identifier('count');
                 return "SELECT COUNT(*) AS $count_column FROM " . $this->_quote_identifier($this->_table_name);
             } else {
-                return 'SELECT * FROM ' . $this->_quote_identifier($this->_table_name);
+                $result_columns = join(', ', $this->_result_columns);
+                return "SELECT {$result_columns} FROM " . $this->_quote_identifier($this->_table_name);
             }
         }
 
@@ -632,10 +678,22 @@
 
         /**
          * Quote a string that is used as an identifier
-         * (table names, column names etc).
+         * (table names, column names etc). This method can
+         * also deal with dot-separated identifiers eg table.column
          */
         protected function _quote_identifier($identifier) {
-            return "`$identifier`";
+            $parts = explode('.', $identifier);
+            $parts = array_map(array($this, '_quote_identifier_part'), $parts);
+            return join('.', $parts);
+        }
+
+        /**
+         * This method performs the actual quoting of a single
+         * part of an identifier. Currently uses backticks, which
+         * are compatible with (at least) MySQL and SQLite.
+         */
+        protected function _quote_identifier_part($part) {
+            return "`$part`";
         }
 
         /**
diff --git a/test/test_queries.php b/test/test_queries.php
index 5122402..c2247bf 100644
--- a/test/test_queries.php
+++ b/test/test_queries.php
@@ -100,6 +100,26 @@
     $expected = "SELECT `w`.* FROM `widget` w WHERE `name` = 'Fred' AND `age` = '5'";
     Tester::check_equal("Raw query", $expected);
 
+    ORM::for_table('widget')->select('name')->find_many();
+    $expected = "SELECT `name` FROM `widget`";
+    Tester::check_equal("Simple result column", $expected);
+
+    ORM::for_table('widget')->select('name')->select('age')->find_many();
+    $expected = "SELECT `name`, `age` FROM `widget`";
+    Tester::check_equal("Multiple simple result columns", $expected);
+
+    ORM::for_table('widget')->select('widget.name')->find_many();
+    $expected = "SELECT `widget`.`name` FROM `widget`";
+    Tester::check_equal("Specify table name and column in result columns", $expected);
+
+    ORM::for_table('widget')->select('widget.name', 'widget_name')->find_many();
+    $expected = "SELECT `widget`.`name` AS `widget_name` FROM `widget`";
+    Tester::check_equal("Aliases in result columns", $expected);
+
+    ORM::for_table('widget')->select_expr('COUNT(*)', 'count')->find_many();
+    $expected = "SELECT COUNT(*) AS `count` FROM `widget`";
+    Tester::check_equal("Literal expression in result columns", $expected);
+
     $widget = ORM::for_table('widget')->create();
     $widget->name = "Fred";
     $widget->age = 10;