Repository: nuveo/prest
Pull request #177 (2017-06-30 21:39:50)

Add support to HAVING clause
refs #175 

missing docs on README

codecov[bot]: # [Codecov](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=h1) Report
> Merging [#177](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=desc) into [master](https://codecov.io/gh/nuveo/prest/commit/c1320b7ec2df12a17775cf5b1de669cc2faace0f?src=pr&el=desc) will **increase** coverage by `0.59%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/nuveo/prest/pull/177/graphs/tree.svg?width=650&height=150&src=pr&token=eVD9urwIEv)](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master     #177      +/-   ##
==========================================
+ Coverage   86.02%   86.61%   +0.59%     
==========================================
  Files          11       11              
  Lines         887      904      +17     
==========================================
+ Hits          763      783      +20     
+ Misses         87       84       -3     
  Partials       37       37
```


| [Impacted Files](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [adapters/postgres/postgres.go](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=tree#diff-YWRhcHRlcnMvcG9zdGdyZXMvcG9zdGdyZXMuZ28=) | `84.19% <100%> (+1.33%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=footer). Last update [c1320b7...21349bf](https://codecov.io/gh/nuveo/prest/pull/177?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

marioidival: @felipeweb done to merge

Patch:
diff --git a/README.md b/README.md
index 7d87c89..1f4bd17 100644
--- a/README.md
+++ b/README.md
@@ -302,6 +302,14 @@ We support this Group Functions:
 #### Using Group Functions
 	GET /DATABASE/SCHEMA/TABLE/?_select=fieldname00,sum:fieldname01&_groupby=fieldname01
 
+#### Having support
+To use Having clause with **Group By**, follow this syntax:
+	
+	_groupby=fieldname->>having:GROUPFUNC:FIELDNAME:CONDITION:VALUE-CONDITION
+	
+Example:
+	
+	GET /DATABASE/SCHEMA/TABLE/?_select=fieldname00,sum:fieldname01&_groupby=fieldname01-->having:sum:fieldname01:$gt:500
 
 
 ## Executing SQL scripts
diff --git a/adapters/postgres/postgres.go b/adapters/postgres/postgres.go
index 4e1acb9..000fd27 100644
--- a/adapters/postgres/postgres.go
+++ b/adapters/postgres/postgres.go
@@ -686,6 +686,31 @@ func GroupByClause(r *http.Request) (groupBySQL string) {
 		return
 	}
 
+	if strings.Contains(groupQuery, "->>having") {
+		params := strings.Split(groupQuery, ":")
+		groupFieldQuery := strings.Split(groupQuery, "->>having")
+		if len(params) != 5 {
+			groupBySQL = fmt.Sprintf(statements.GroupBy, groupFieldQuery[0])
+			return
+		}
+		// groupFunc, field, condition, conditionValue string
+		groupFunc, err := NormalizeGroupFunction(fmt.Sprintf("%s:%s", params[1], params[2]))
+		if err != nil {
+			groupBySQL = fmt.Sprintf(statements.GroupBy, groupFieldQuery[0])
+			return
+		}
+
+		operator, err := GetQueryOperator(params[3])
+		if err != nil {
+			groupBySQL = fmt.Sprintf(statements.GroupBy, groupFieldQuery[0])
+			return
+		}
+
+		havingQuery := fmt.Sprintf(statements.Having, groupFunc, operator, params[4])
+		groupBySQL = fmt.Sprintf("%s %s", fmt.Sprintf(statements.GroupBy, groupFieldQuery[0]), havingQuery)
+		return
+	}
+
 	groupBySQL = fmt.Sprintf(statements.GroupBy, groupQuery)
 	return
 }
diff --git a/statements/queries.go b/statements/queries.go
index a531695..d25a698 100644
--- a/statements/queries.go
+++ b/statements/queries.go
@@ -123,6 +123,9 @@ UPDATE %s.%s.%s SET %s`
 
 	// GroupBy query
 	GroupBy = `GROUP BY %s`
+
+	// Having query
+	Having = `HAVING %s %s %s`
 )
 
 var (


Test patch:
diff --git a/adapters/postgres/postgres_test.go b/adapters/postgres/postgres_test.go
index 942b98b..063569f 100644
--- a/adapters/postgres/postgres_test.go
+++ b/adapters/postgres/postgres_test.go
@@ -196,6 +196,14 @@ func TestGroupByClause(t *testing.T) {
 		{"Group by clause with one field", "/prest/public/test5?_groupby=celphone", "GROUP BY celphone", false},
 		{"Group by clause with two fields", "/prest/public/test5?_groupby=celphone,name", "GROUP BY celphone,name", false},
 		{"Group by clause without fields", "/prest/public/test5?_groupby=", "", true},
+
+		// having tests
+		{"Group by clause with having clause", "/prest/public/test5?_groupby=celphone->>having:sum:salary:$gt:500", "GROUP BY celphone HAVING SUM(salary) > 500", false},
+
+		// having errors, but continue with group by
+		{"Group by clause with wrong having clause (insufficient params)", "/prest/public/test5?_groupby=celphone->>having:sum:salary", "GROUP BY celphone", false},
+		{"Group by clause with wrong having clause (wrong query operator)", "/prest/public/test5?_groupby=celphone->>having:sum:salary:$at:500", "GROUP BY celphone", false},
+		{"Group by clause with wrong having clause (wrong group func)", "/prest/public/test5?_groupby=celphone->>having:sun:salary:$gt:500", "GROUP BY celphone", false},
 	}
 
 	for _, tc := range testCases {
diff --git a/controllers/tables_test.go b/controllers/tables_test.go
index 7dddaa5..05059ab 100644
--- a/controllers/tables_test.go
+++ b/controllers/tables_test.go
@@ -90,6 +90,7 @@ func TestSelectFromTables(t *testing.T) {
 		{"execute select in a table with select *", "/prest/public/test5?_select=*", "GET", http.StatusOK, ""},
 
 		{"execute select in a table with group by clause", "/prest/public/test_group_by_table?_select=age,sum:salary&_groupby=age", "GET", http.StatusOK, "[{\"age\":20,\"sum\":1350}, \n {\"age\":19,\"sum\":7997}]"},
+		{"Execute select in a table with group by and having clause", "/prest/public/test_group_by_table?_select=age,sum:salary&_groupby=age->>having:sum:salary:$gt:3000", "GET", http.StatusOK, "[{\"age\":19,\"sum\":7997}]"},
 
 		{"execute select in a view without custom where clause", "/prest/public/view_test", "GET", http.StatusOK, ""},
 		{"execute select in a view with count all fields *", "/prest/public/view_test?_count=*", "GET", http.StatusOK, ""},
@@ -109,6 +110,7 @@ func TestSelectFromTables(t *testing.T) {
 		{"execute select in a table with invalid count clause", "/prest/public/test?_count=0name", "GET", http.StatusBadRequest, ""},
 		{"execute select in a table with invalid order clause", "/prest/public/test?_order=0name", "GET", http.StatusBadRequest, ""},
 		{"execute select in a table with invalid fields using group by clause", "/prest/public/test_group_by_table?_select=pa,sum:pum&_groupby=pa", "GET", http.StatusBadRequest, ""},
+		{"execute select in a table with invalid fields using group by and having clause", "/prest/public/test_group_by_table?_select=pa,sum:pum&_groupby=pa->>having:sum:pmu:$eq:150", "GET", http.StatusBadRequest, ""},
 
 		{"execute select in a view with an other column", "/prest/public/view_test?_select=celphone", "GET", http.StatusBadRequest, ""},
 		{"execute select in a view with where and column invalid", "/prest/public/view_test?0celphone=$eq.888888", "GET", http.StatusBadRequest, ""},