[floe] r404 committed - adding support for groupBy and having clauses and an API for count/dis...

[fl...@googlecode.com]

Revision: 404
Author: coretxt
Date: Thu May 20 05:22:19 2010
Log: adding support for groupBy and having clauses and an API for
count/distinct/sum fields on select queries
http://code.google.com/p/floe/source/detail?r=404

Modified:
/trunk/dev/tests/query.test.php
/trunk/src/repository/Query.class.php
/trunk/src/repository/store/mysql/MysqlQuery.class.php

=======================================
--- /trunk/dev/tests/query.test.php Tue Mar 9 16:38:35 2010
+++ /trunk/dev/tests/query.test.php Thu May 20 05:22:19 2010
@@ -6,11 +6,17 @@

class MysqlQueryCriteriaTest extends UnitTestCase {

- function testStarSelect() {
+ function testWildcardFieldsFromEmptyParameter() {
$query = Query::instance();
$query->select()->from("articles");
$this->assertEqual($query->__toString(), "SELECT * FROM articles");
}
+
+ function testWildcardFieldsWithNoSelectGiven() {
+ $query = Query::instance();
+ $query->from("articles");
+ $this->assertEqual($query->__toString(), "SELECT * FROM articles");
+ }

function testMultipleColumnsSelectAsArray() {
$query = Query::instance();
@@ -20,11 +26,11 @@

function testMultipleColumnsSelectAsArgs() {
$query = Query::instance();
- $query->select("title","summary","updated")->from("articles");
+ $query->select("title", "summary", "updated")->from("articles");
$this->assertEqual($query->__toString(), "SELECT title,summary,updated
FROM articles");
}

- function testSingleColumnsSelect() {
+ function testChainedColumnsSelect() {
$query = Query::instance();
$query->select("title")->select("summary")->from("articles");
$this->assertEqual($query->__toString(), "SELECT title,summary FROM
articles");
@@ -107,9 +113,21 @@

function testSelectCountFunction() {
$query = Query::instance();
- $query->selectCount()->from("things");
+ $query->count()->from("things");
$this->assertEqual($query->__toString(), "SELECT COUNT(id) AS count FROM
things");
}
+
+ function testSelectCountFieldFunction() {
+ $query = Query::instance();
+ $query->count("name")->from("things");
+ $this->assertEqual($query->__toString(), "SELECT COUNT(name) AS name
FROM things");
+ }
+
+ function testSelectDistinctFieldFunction() {
+ $query = Query::instance();
+ $query->distinct("name")->from("things");
+ $this->assertEqual($query->__toString(), "SELECT DISTINCT(name) AS name
FROM things");
+ }

function testWhereJoinFunction() {
$query = Query::instance();
@@ -122,6 +140,12 @@
$query->select()->from('things')->where('key', '=', '1');
$this->assertEqual($query->__toString(),"SELECT * FROM things WHERE
key = 1");
}
+
+ function testGroupByClause() {
+ $query = Query::instance();
+ $query->from("things")->groupBy('name')->having('key', '=', '1');
+ $this->assertEqual($query->__toString(), "SELECT * FROM things GROUP BY
name HAVING key = 1");
+ }

}

=======================================
--- /trunk/src/repository/Query.class.php Tue Mar 9 16:53:10 2010
+++ /trunk/src/repository/Query.class.php Thu May 20 05:22:19 2010
@@ -24,12 +24,16 @@

protected $limitUpper;

+ protected $groupBy;
+
protected $orderBy;

protected $orderDir;

protected $whereClauses;

+ protected $havingClauses;
+
protected $selectFields;

protected $tableNames;
@@ -46,6 +50,7 @@
function __construct() {
$this->selectFields = array();
$this->whereClauses = array();
+ $this->havingClauses = array();
$this->tableNames = array();
}

@@ -83,15 +88,36 @@
}

/**
- * Select a count of the given column.
+ * Count from the given column.
*
* If no column given, defaults to <code>id</code>.
*
- * @todo move to MysqlSpecific query object
+ * @param string $column
* @return Query
*/
- function selectCount($column="id") {
- return $this->select("COUNT($column) AS count");
+ function count($column='id') {
+ $alias = ($column == 'id') ? 'count' : $column;
+ return $this->select("COUNT($column) AS $alias");
+ }
+
+ /**
+ * Select unique values from the given column.
+ *
+ * @param string $column
+ * @return Query
+ */
+ function distinct($column) {
+ return $this->select("DISTINCT($column) AS $column");
+ }
+
+ /**
+ * Sum of combined values from the given column.
+ *
+ * @param string $column
+ * @return Query
+ */
+ function sum($column) {
+ return $this->select("SUM($column) AS $column");
}

/**
@@ -253,6 +279,24 @@
$this->whereClauses[] = self::criteria($key, $operator, $lower);
return $this;
}
+
+ /**
+ * Group query results by the given column.
+ *
+ * @return Query
+ */
+ function groupBy($field) {
+ $this->groupBy = Inflect::underscore($field);
+ return $this;
+ }
+
+ /**
+ * Constrain grouped results with a where clause
+ */
+ function having($field, $operator, $value) {
+ $this->havingClauses[] = self::criteria($field, $operator, $value, true);
+ return $this;
+ }

/**
* Order the query results by the given column.
=======================================
--- /trunk/src/repository/store/mysql/MysqlQuery.class.php Tue Mar 9
16:38:35 2010
+++ /trunk/src/repository/store/mysql/MysqlQuery.class.php Thu May 20
05:22:19 2010
@@ -43,6 +43,13 @@
$sql .= "WHERE ";
$sql .= implode(' AND ', array_map(array($this, 'mergeClauses'),
$this->whereClauses));
}
+ if ($this->groupBy) {
+ $sql .= "GROUP BY {$this->groupBy}";
+ if (count($this->havingClauses) > 0) {
+ $sql .= " HAVING ";
+ $sql .= implode(' AND ', array_map(array($this, 'mergeClauses'),
$this->havingClauses));
+ }
+ }
if ($this->orderBy) {
$sql .= " ORDER BY {$this->orderBy} ";
$sql .= ($this->orderDir) ? $this->orderDir : 'DESC';
@@ -60,7 +67,7 @@
*/
function __toString() {
$sql = "SELECT ";
- $sql .= implode(',', $this->selectFields);
+ $sql .= ($this->selectFields) ? implode(',', $this->selectFields) : "*";
$sql .= " FROM ". implode(',', $this->tableNames) ." ";
$sql .= $this->toSql();
return trim($sql);

--
You received this message because you are subscribed to the Google Groups "Floe Commits" group.
To post to this group, send email to floe-c...@googlegroups.com.
To unsubscribe from this group, send email to floe-commits...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/floe-commits?hl=en.