bitemyapp / bitemyapp/esqueleto
Improve GROUP BY
- Dominant language
- Haskell
- Stars
- 399
- Forks
- 107
- Avg merge
- 1d 17h
- Merged PRs (30d)
- 1
Description
Now that @belevy has defeated the Join monster, we have another set of runtime issues that are becoming more prevalent:
`GROUP BY`
In SQL, if you `GROUP BY` a column, then you can't refer to any other columns in the `SELECT`, except by aggregations.
But `esqueleto` currently has absolutely no way of tracking this in the types. [`opaleye`](https://github.com/tomjaguarpaw/haskell-opaleye/blob/master/Doc/Tutorial/TutorialBasic.lhs#L559-L607) handles this with a function `aggregate`, but it's not obvious to me how you'd use this with a `JOIN`. [`beam`](https://hackage.haskell.org/package/beam-core-0.8.0.0/docs/Database-Beam-Query.html#t:QGroupable) has [an abstraction](https://tathougies.github.io/beam/user-guide/queries/aggregates/) that similarly accepts a query and provides a lambda. [`squeal`](https://hackage.haskell.org/package/squeal-postgresql-0.6.0.2/docs/Squeal-PostgreSQL-Query-Table.html#v:groupBy) tracks the aggregation in the type of `TableExpression`.
I think the least invasive way to go is probably to do something like:
```haskell
groupBy :: SqlQuery a -> (a -> GroupExpr) -> (GroupExpr -> SqlQuery b)
```
Usage would look like:
```haskell
groupBy
do
(f :& b) <- from $ Table @Foo
`InnerJoin`
Table @Bar
`on` do
\(f :& b) -> f ^. FooId ==. b ^. BarFooId
where_ $ f ^. FooName `in_` valList []
pure (f :& b)
do
\(f :& b) ->
groupCol (f ^. FooId) b
do
\(fooId, b) ->
pure (fooId, sum_ b)
```
But this structure really strongly reminds me of the [effectful property testing](https://www.parsonsmatt.org/2020/03/11/effectful_property_testing.html) pattern. Taking that as inspiration, maybe the right syntax is more like:
```haskell
select $ do
(a :& b) <- from fromClauses
groupBy (a ^. AGroupingColumn) $ \groupingColumn -> do
having_ $ sum_ (b ^. BSales) >=. val 123
pure (groupingColumn, sum_ (b ^. BSales), countRows)
```
In this case, our type is now:
```
groupBy
:: GroupingColumns
-> (GroupingColumns -> SqlQuery (Aggregating x))
-> SqlQuery x
```
Syntactically, it's nice, but it would need to be the last thing called in the `do` block. We can't require that without indexed monads.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.